| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Compare the 1st 9 digits in two columns looking for duplicates
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Compare the 1st 9 digits in two columns looking for duplicates
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)&"*",B$1:B$100,0)) copy down to check A2, A3 etc. -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=525426 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Compare the 1st 9 digits in two columns looking for duplicates
Very nice, dll,.....very nice.
Vaya con Dios, Chuck, CABGx3 "daddylonglegs" wrote: 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)&"*",B$1:B$100,0)) copy down to check A2, A3 etc. -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=525426 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Compare the 1st 9 digits in two columns looking for duplicates
Try something like this:
For lists in A1:B50 D1: =COUNTIF($B$1:$B$50,LEFT(A1,9)&"*")0 copy that formula down as far as you need. If you don't want "hits" on blank cells, use this: D1: =COUNTIF($B$1:$B$50,LEFT(A1&" ",9)&"*")0 Does that help? *********** Regards, Ron XL2002, WinXP-Pro "Sweetetc" wrote: 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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Compare the 1st 9 digits in two columns looking for duplicates
Another way
=COUNTIF(B$1:B$100,LEFT(A1,9)&"*")0 -- Regards, Peo Sjoblom "Sweetetc" wrote in message ... 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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Compare the 1st 9 digits in two columns looking for duplicates
You folks are the greatest thanks
-- Thanks ETC "Sweetetc" wrote: 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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Compare the 1st 9 digits in two columns looking for duplicates
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)&"*")0 Ron #2 in D1: =COUNTIF($B$1:$B$50,LEFT(A1&" ",9)&"*")0 Peo #1 in D1: =COUNTIF(B$1:B$100,LEFT(A1,9)&"*")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-Pro "Sweetetc" wrote: Spoke too soon they do not seem to compare the way I need. 22345678922223200 12345678922223200 TRUE 32345678922223200 12345678922223200 TRUE 42345678922223200 22345678922223200 FALSE 12345678922223200 12345678922223200 FALSE 12345678922223200 12345678922223200 TRUE 24345678922223200 12345678922223200 TRUE 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 ETC "Ron Coderre" wrote: Try something like this: For lists in A1:B50 D1: =COUNTIF($B$1:$B$50,LEFT(A1,9)&"*")0 copy that formula down as far as you need. If you don't want "hits" on blank cells, use this: D1: =COUNTIF($B$1:$B$50,LEFT(A1&" ",9)&"*")0 Does that help? *********** Regards, Ron XL2002, WinXP-Pro "Sweetetc" wrote: 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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Compare the 1st 9 digits in two columns looking for duplicates
Spoke too soon they do not seem to compare the way I need.
22345678922223200 12345678922223200 TRUE 32345678922223200 12345678922223200 TRUE 42345678922223200 22345678922223200 FALSE 12345678922223200 12345678922223200 FALSE 12345678922223200 12345678922223200 TRUE 24345678922223200 12345678922223200 TRUE 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 ETC "Ron Coderre" wrote: Try something like this: For lists in A1:B50 D1: =COUNTIF($B$1:$B$50,LEFT(A1,9)&"*")0 copy that formula down as far as you need. If you don't want "hits" on blank cells, use this: D1: =COUNTIF($B$1:$B$50,LEFT(A1&" ",9)&"*")0 Does that help? *********** Regards, Ron XL2002, WinXP-Pro "Sweetetc" wrote: 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 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Compare the 1st 9 digits in two columns looking for duplicates
spoke too soon this does not seem to comapre what I need
22345678922223200 12345678922223200 TRUE 32345678922223200 12345678922223200 TRUE 42345678922223200 22345678922223200 FALSE 12345678922223200 12345678922223200 FALSE 12345678922223200 12345678922223200 TRUE 24345678922223200 12345678922223200 TRUE 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 ETC "Sweetetc" wrote: 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 |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Compare the 1st 9 digits in two columns looking for duplicates
Thanks for your response Ron DAH!!!! I was not it the absolute cell of B1
Works well when you do the correct things -- Thanks ETC "Ron Coderre" wrote: 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)&"*")0 Ron #2 in D1: =COUNTIF($B$1:$B$50,LEFT(A1&" ",9)&"*")0 Peo #1 in D1: =COUNTIF(B$1:B$100,LEFT(A1,9)&"*")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-Pro "Sweetetc" wrote: Spoke too soon they do not seem to compare the way I need. 22345678922223200 12345678922223200 TRUE 32345678922223200 12345678922223200 TRUE 42345678922223200 22345678922223200 FALSE 12345678922223200 12345678922223200 FALSE 12345678922223200 12345678922223200 TRUE 24345678922223200 12345678922223200 TRUE 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 ETC "Ron Coderre" wrote: Try something like this: For lists in A1:B50 D1: =COUNTIF($B$1:$B$50,LEFT(A1,9)&"*")0 copy that formula down as far as you need. If you don't want "hits" on blank cells, use this: D1: =COUNTIF($B$1:$B$50,LEFT(A1&" ",9)&"*")0 Does that help? *********** Regards, Ron XL2002, WinXP-Pro "Sweetetc" wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Compare multiple column of data and list out common and unique component in adj columns | Excel Worksheet Functions | |||
| how do I compare columns in two different workbooks? | Excel Discussion (Misc queries) | |||
| Compare Columns | Excel Discussion (Misc queries) | |||
| Compare two columns | Excel Discussion (Misc queries) | |||
| compare columns of different worksheets | Excel Discussion (Misc queries) | |||
Linear Mode
