software

I have two columns of data. Each row cell is 27 characters long. I want to
find any duplicate matches between the two columns on just the 1st 9
characters of each cell. Is there a function that can do this?

--
Thanks
ETC


If you want to see whether the first 9 characters of A1 matches the
first 9 characters of any entry in B1:B100 then in C1

=ISNUMBER(MATCH(LEFT(A1,9)amp;quot;*quot;,B$1:B$100,0))

copy down to check A2, A3 etc.--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: www.excelforum.com/member.php...oamp;userid=30486
View this thread: www.excelforum.com/showthread...hreadid=525426Very nice, dll,.....very nice.

Vaya con Dios,
Chuck, CABGx3
quot;daddylonglegsquot; wrote:

gt;
gt; If you want to see whether the first 9 characters of A1 matches the
gt; first 9 characters of any entry in B1:B100 then in C1
gt;
gt; =ISNUMBER(MATCH(LEFT(A1,9)amp;quot;*quot;,B$1:B$100,0))
gt;
gt; copy down to check A2, A3 etc.
gt;
gt;
gt; --
gt; daddylonglegs
gt; ------------------------------------------------------------------------
gt; daddylonglegs's Profile: www.excelforum.com/member.php...oamp;userid=30486
gt; View this thread: www.excelforum.com/showthread...hreadid=525426
gt;
gt;

Try something like this:

For lists in A1:B50

D1: =COUNTIF($B$1:$B$50,LEFT(A1,9)amp;quot;*quot;)gt;0

copy that formula down as far as you need.

If you don't want quot;hitsquot; on blank cells, use this:
D1: =COUNTIF($B$1:$B$50,LEFT(A1amp;quot; quot;,9)amp;quot;*quot;)gt;0Does that help?

***********
Regards,
Ron

XL2002, WinXP-Proquot;Sweetetcquot; wrote:

gt; I have two columns of data. Each row cell is 27 characters long. I want to
gt; find any duplicate matches between the two columns on just the 1st 9
gt; characters of each cell. Is there a function that can do this?
gt;
gt; --
gt; Thanks
gt; ETC

Another way
=COUNTIF(B$1:B$100,LEFT(A1,9)amp;quot;*quot;)gt;0
--

Regards,

Peo Sjoblomquot;Sweetetcquot; gt; wrote in message
...
gt;I have two columns of data. Each row cell is 27 characters long. I want
gt;to
gt; find any duplicate matches between the two columns on just the 1st 9
gt; characters of each cell. Is there a function that can do this?
gt;
gt; --
gt; Thanks
gt; ETC
You folks are the greatest thanks
--
Thanks
ETCquot;Sweetetcquot; wrote:

gt; I have two columns of data. Each row cell is 27 characters long. I want to
gt; find any duplicate matches between the two columns on just the 1st 9
gt; characters of each cell. Is there a function that can do this?
gt;
gt; --
gt; Thanks
gt; ETC

I must be missing something!?!

Using your posted data, I tried both of the formulas I posted AND Peo's
formula.
Ron #1 in D1: =COUNTIF($B$1:$B$50,LEFT(A1,9)amp;quot;*quot;)gt;0
Ron #2 in D1: =COUNTIF($B$1:$B$50,LEFT(A1amp;quot; quot;,9)amp;quot;*quot;)gt;0
Peo #1 in D1: =COUNTIF(B$1:B$100,LEFT(A1,9)amp;quot;*quot;)gt;0

All three formulas, when copied down, returned TRUE, FALSE, FALSE, TRUE,
TRUE, FALSE

Perhaps if you post the exact formula you're using we might spot a difference.

***********
Regards,
Ron

XL2002, WinXP-Proquot;Sweetetcquot; wrote:

gt; Spoke too soon they do not seem to compare the way I need.
gt;
gt; 2234567892222320012345678922223200TRUE
gt; 3234567892222320012345678922223200TRUE
gt; 4234567892222320022345678922223200FALSE
gt; 1234567892222320012345678922223200FALSE
gt; 1234567892222320012345678922223200TRUE
gt; 2434567892222320012345678922223200TRUE
gt;
gt; These two columns should return True, False, False, True. True. False,
gt; I am aking if for the 1st 9 digits and A1 are there any matches for the 1st
gt; 9 digits in column B Then A2 first 9 digits, etc.
gt; --
gt; Thanks
gt; ETC
gt;
gt;
gt; quot;Ron Coderrequot; wrote:
gt;
gt; gt; Try something like this:
gt; gt;
gt; gt; For lists in A1:B50
gt; gt;
gt; gt; D1: =COUNTIF($B$1:$B$50,LEFT(A1,9)amp;quot;*quot;)gt;0
gt; gt;
gt; gt; copy that formula down as far as you need.
gt; gt;
gt; gt; If you don't want quot;hitsquot; on blank cells, use this:
gt; gt; D1: =COUNTIF($B$1:$B$50,LEFT(A1amp;quot; quot;,9)amp;quot;*quot;)gt;0
gt; gt;
gt; gt;
gt; gt; Does that help?
gt; gt;
gt; gt; ***********
gt; gt; Regards,
gt; gt; Ron
gt; gt;
gt; gt; XL2002, WinXP-Pro
gt; gt;
gt; gt;
gt; gt; quot;Sweetetcquot; wrote:
gt; gt;
gt; gt; gt; I have two columns of data. Each row cell is 27 characters long. I want to
gt; gt; gt; find any duplicate matches between the two columns on just the 1st 9
gt; gt; gt; characters of each cell. Is there a function that can do this?
gt; gt; gt;
gt; gt; gt; --
gt; gt; gt; Thanks
gt; gt; gt; ETC

Spoke too soon they do not seem to compare the way I need.

2234567892222320012345678922223200TRUE
3234567892222320012345678922223200TRUE
4234567892222320022345678922223200FALSE
1234567892222320012345678922223200FALSE
1234567892222320012345678922223200TRUE
2434567892222320012345678922223200TRUE

These two columns should return True, False, False, True. True. False,
I am aking if for the 1st 9 digits and A1 are there any matches for the 1st
9 digits in column B Then A2 first 9 digits, etc.
--
Thanks
ETCquot;Ron Coderrequot; wrote:

gt; Try something like this:
gt;
gt; For lists in A1:B50
gt;
gt; D1: =COUNTIF($B$1:$B$50,LEFT(A1,9)amp;quot;*quot;)gt;0
gt;
gt; copy that formula down as far as you need.
gt;
gt; If you don't want quot;hitsquot; on blank cells, use this:
gt; D1: =COUNTIF($B$1:$B$50,LEFT(A1amp;quot; quot;,9)amp;quot;*quot;)gt;0
gt;
gt;
gt; Does that help?
gt;
gt; ***********
gt; Regards,
gt; Ron
gt;
gt; XL2002, WinXP-Pro
gt;
gt;
gt; quot;Sweetetcquot; wrote:
gt;
gt; gt; I have two columns of data. Each row cell is 27 characters long. I want to
gt; gt; find any duplicate matches between the two columns on just the 1st 9
gt; gt; characters of each cell. Is there a function that can do this?
gt; gt;
gt; gt; --
gt; gt; Thanks
gt; gt; ETC

spoke too soon this does not seem to comapre what I need

2234567892222320012345678922223200TRUE
3234567892222320012345678922223200TRUE
4234567892222320022345678922223200FALSE
1234567892222320012345678922223200FALSE
1234567892222320012345678922223200TRUE
2434567892222320012345678922223200TRUE

These two columns should return True, False, False, True. True. False,
I am aking if for the 1st 9 digits and A1 are there any matches for the 1st
9 digits in column B Then A2 first 9 digits, etc.--
Thanks
ETCquot;Sweetetcquot; wrote:

gt; I have two columns of data. Each row cell is 27 characters long. I want to
gt; find any duplicate matches between the two columns on just the 1st 9
gt; characters of each cell. Is there a function that can do this?
gt;
gt; --
gt; Thanks
gt; ETC

Thanks for your response Ron DAH!!!! I was not it the absolute cell of B1

Works well when you do the correct things
--
Thanks
ETCquot;Ron Coderrequot; wrote:

gt; I must be missing something!?!
gt;
gt; Using your posted data, I tried both of the formulas I posted AND Peo's
gt; formula.
gt; Ron #1 in D1: =COUNTIF($B$1:$B$50,LEFT(A1,9)amp;quot;*quot;)gt;0
gt; Ron #2 in D1: =COUNTIF($B$1:$B$50,LEFT(A1amp;quot; quot;,9)amp;quot;*quot;)gt;0
gt; Peo #1 in D1: =COUNTIF(B$1:B$100,LEFT(A1,9)amp;quot;*quot;)gt;0
gt;
gt; All three formulas, when copied down, returned TRUE, FALSE, FALSE, TRUE,
gt; TRUE, FALSE
gt;
gt; Perhaps if you post the exact formula you're using we might spot a difference.
gt;
gt; ***********
gt; Regards,
gt; Ron
gt;
gt; XL2002, WinXP-Pro
gt;
gt;
gt; quot;Sweetetcquot; wrote:
gt;
gt; gt; Spoke too soon they do not seem to compare the way I need.
gt; gt;
gt; gt; 2234567892222320012345678922223200TRUE
gt; gt; 3234567892222320012345678922223200TRUE
gt; gt; 4234567892222320022345678922223200FALSE
gt; gt; 1234567892222320012345678922223200FALSE
gt; gt; 1234567892222320012345678922223200TRUE
gt; gt; 2434567892222320012345678922223200TRUE
gt; gt;
gt; gt; These two columns should return True, False, False, True. True. False,
gt; gt; I am aking if for the 1st 9 digits and A1 are there any matches for the 1st
gt; gt; 9 digits in column B Then A2 first 9 digits, etc.
gt; gt; --
gt; gt; Thanks
gt; gt; ETC
gt; gt;
gt; gt;
gt; gt; quot;Ron Coderrequot; wrote:
gt; gt;
gt; gt; gt; Try something like this:
gt; gt; gt;
gt; gt; gt; For lists in A1:B50
gt; gt; gt;
gt; gt; gt; D1: =COUNTIF($B$1:$B$50,LEFT(A1,9)amp;quot;*quot;)gt;0
gt; gt; gt;
gt; gt; gt; copy that formula down as far as you need.
gt; gt; gt;
gt; gt; gt; If you don't want quot;hitsquot; on blank cells, use this:
gt; gt; gt; D1: =COUNTIF($B$1:$B$50,LEFT(A1amp;quot; quot;,9)amp;quot;*quot;)gt;0
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; Does that help?
gt; gt; gt;
gt; gt; gt; ***********
gt; gt; gt; Regards,
gt; gt; gt; Ron
gt; gt; gt;
gt; gt; gt; XL2002, WinXP-Pro
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; quot;Sweetetcquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; I have two columns of data. Each row cell is 27 characters long. I want to
gt; gt; gt; gt; find any duplicate matches between the two columns on just the 1st 9
gt; gt; gt; gt; characters of each cell. Is there a function that can do this?
gt; gt; gt; gt;
gt; gt; gt; gt; --
gt; gt; gt; gt; Thanks
gt; gt; gt; gt; ETC