Compare Two Columns (Exact and Wildcard)

[Xavier]

I'm having trouble applying the right formula to my spreadsheet. What
I'm trying to do is compare two columns and determine which numbers in
column A appear in column B. The problem is the way my data is stored:

A B
12345 12345r1
12345 23456
12345 67890r2
23456
34567

As you can see, I'll either have an exact match (23456) or a partial
match (12345 & 12345r1). So I would need, say column C, to display
True, True, True, True, False. I tried the formula
"=ISNUMBER(MATCH(LEFT(A1,5)&"*",List2!A:A,0))+0" but that returns True
only for partial matches, not exact. Does anyone know which formula I
should use for my case? Thank you!

Xavier

[Bernie Deitrick]

Xavier,

For your example, enter this array formula (enter using Ctrl-Shift-Enter) into cell C1, then copy
down to C2:C5

=SUM((TEXT(A1,"0")=LEFT($B$1:$B$3,LEN(A1)))*1)>=1

HTH,
Bernie
MS Excel MVP


"Xavier" <[email protected]> wrote in message
news:[email protected]...
> I'm having trouble applying the right formula to my spreadsheet. What
> I'm trying to do is compare two columns and determine which numbers in
> column A appear in column B. The problem is the way my data is stored:
>
> A B
> 12345 12345r1
> 12345 23456
> 12345 67890r2
> 23456
> 34567
>
> As you can see, I'll either have an exact match (23456) or a partial
> match (12345 & 12345r1). So I would need, say column C, to display
> True, True, True, True, False. I tried the formula
> "=ISNUMBER(MATCH(LEFT(A1,5)&"*",List2!A:A,0))+0" but that returns True
> only for partial matches, not exact. Does anyone know which formula I
> should use for my case? Thank you!
>
> Xavier
>

[Ron Coderre]

If the Col_B items are ALL text entries...maybe something like this?:

With your sample data beginning in A1

This formula checks if the A1 value is in the B1:B3 list
C1: =COUNTIF($B$1:$B$3,A1&"*")>0

Copy that formula down

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Bernie Deitrick" wrote:

> Xavier,
>
> For your example, enter this array formula (enter using Ctrl-Shift-Enter) into cell C1, then copy
> down to C2:C5
>
> =SUM((TEXT(A1,"0")=LEFT($B$1:$B$3,LEN(A1)))*1)>=1
>
> HTH,
> Bernie
> MS Excel MVP
>
>
> "Xavier" <[email protected]> wrote in message
> news:[email protected]...
> > I'm having trouble applying the right formula to my spreadsheet. What
> > I'm trying to do is compare two columns and determine which numbers in
> > column A appear in column B. The problem is the way my data is stored:
> >
> > A B
> > 12345 12345r1
> > 12345 23456
> > 12345 67890r2
> > 23456
> > 34567
> >
> > As you can see, I'll either have an exact match (23456) or a partial
> > match (12345 & 12345r1). So I would need, say column C, to display
> > True, True, True, True, False. I tried the formula
> > "=ISNUMBER(MATCH(LEFT(A1,5)&"*",List2!A:A,0))+0" but that returns True
> > only for partial matches, not exact. Does anyone know which formula I
> > should use for my case? Thank you!
> >
> > Xavier
> >
>
>
>

[Ron Coderre]

Apologies, Bernie....Evidently, I selected the wrong posting in this topic. :\
(I intended to reply to the OP)

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

XL2002, WinXP


"Ron Coderre" wrote:

> If the Col_B items are ALL text entries...maybe something like this?:
>
> With your sample data beginning in A1
>
> This formula checks if the A1 value is in the B1:B3 list
> C1: =COUNTIF($B$1:$B$3,A1&"*")>0
>
> Copy that formula down
>
> Is that something you can work with?
> ***********
> Regards,
> Ron
>
> XL2002, WinXP
>
>
> "Bernie Deitrick" wrote:
>
> > Xavier,
> >
> > For your example, enter this array formula (enter using Ctrl-Shift-Enter) into cell C1, then copy
> > down to C2:C5
> >
> > =SUM((TEXT(A1,"0")=LEFT($B$1:$B$3,LEN(A1)))*1)>=1
> >
> > HTH,
> > Bernie
> > MS Excel MVP
> >
> >
> > "Xavier" <[email protected]> wrote in message
> > news:[email protected]...
> > > I'm having trouble applying the right formula to my spreadsheet. What
> > > I'm trying to do is compare two columns and determine which numbers in
> > > column A appear in column B. The problem is the way my data is stored:
> > >
> > > A B
> > > 12345 12345r1
> > > 12345 23456
> > > 12345 67890r2
> > > 23456
> > > 34567
> > >
> > > As you can see, I'll either have an exact match (23456) or a partial
> > > match (12345 & 12345r1). So I would need, say column C, to display
> > > True, True, True, True, False. I tried the formula
> > > "=ISNUMBER(MATCH(LEFT(A1,5)&"*",List2!A:A,0))+0" but that returns True
> > > only for partial matches, not exact. Does anyone know which formula I
> > > should use for my case? Thank you!
> > >
> > > Xavier
> > >
> >
> >
> >

[Xavier]

Thanks guys, got it working now!

Xavier

Bernie Deitrick wrote:
> Xavier,
>
> For your example, enter this array formula (enter using Ctrl-Shift-Enter) into cell C1, then copy
> down to C2:C5
>
> =SUM((TEXT(A1,"0")=LEFT($B$1:$B$3,LEN(A1)))*1)>=1
>
> HTH,
> Bernie
> MS Excel MVP
>
>
> "Xavier" <[email protected]> wrote in message
> news:[email protected]...
> > I'm having trouble applying the right formula to my spreadsheet. What
> > I'm trying to do is compare two columns and determine which numbers in
> > column A appear in column B. The problem is the way my data is stored:
> >
> > A B
> > 12345 12345r1
> > 12345 23456
> > 12345 67890r2
> > 23456
> > 34567
> >
> > As you can see, I'll either have an exact match (23456) or a partial
> > match (12345 & 12345r1). So I would need, say column C, to display
> > True, True, True, True, False. I tried the formula
> > "=ISNUMBER(MATCH(LEFT(A1,5)&"*",List2!A:A,0))+0" but that returns True
> > only for partial matches, not exact. Does anyone know which formula I
> > should use for my case? Thank you!
> >
> > Xavier
> >