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
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)&"*",B$1:B$100,0))
copy down to check A2, A3 etc.
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
>
>
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
Another way
=COUNTIF(B$1:B$100,LEFT(A1,9)&"*")>0
--
Regards,
Peo Sjoblom
"Sweetetc" <Sweetetc@discussions.microsoft.com> wrote in message
news:39A39331-CAB4-44C1-BA01-2FE14CEAC6E6@microsoft.com...
>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
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
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
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
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
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
these 3 formulas are not working for me
22345678922223200 12345678922223200 false false false
32345678922223200 12345678922223200 false false false
42345678922223200 22345678922223200 false false false
12345678922223200 12345678922223200 false false false
12345678922223200 12345678922223200 false false false
24345678922223200 12345678922223200 false false false
22345678922223200 12345678922223200 =countif($b$1:$b$50,left(a1,9)&"*")>0 =countif($b$1:$b$50,left(a1&" ",9)&"*")>0 =countif(b$1:b$100,left(a1,9)&"*")>0
32345678922223200 12345678922223200 =countif($b$1:$b$50,left(a2,9)&"*")>0 =countif($b$1:$b$50,left(a2&" ",9)&"*")>0 =countif(b$1:b$100,left(a2,9)&"*")>0
42345678922223200 22345678922223200 =countif($b$1:$b$50,left(a3,9)&"*")>0 =countif($b$1:$b$50,left(a3&" ",9)&"*")>0 =countif(b$1:b$100,left(a3,9)&"*")>0
12345678922223200 12345678922223200 =countif($b$1:$b$50,left(a4,9)&"*")>0 =countif($b$1:$b$50,left(a4&" ",9)&"*")>0 =countif(b$1:b$100,left(a4,9)&"*")>0
12345678922223200 12345678922223200 =countif($b$1:$b$50,left(a5,9)&"*")>0 =countif($b$1:$b$50,left(a5&" ",9)&"*")>0 =countif(b$1:b$100,left(a5,9)&"*")>0
24345678922223200 12345678922223200 =countif($b$1:$b$50,left(a6,9)&"*")>0 =countif($b$1:$b$50,left(a6&" ",9)&"*")>0 =countif(b$1:b$100,left(a6,9)&"*")>0
ive used show formulas in column d1 e1 f1
Post your own thread. It is against forum rules to add a "new" post to an existing one.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks