Compare Values In Two Worksheets (May Include Extra Alpha Character)

[Wookiee]

Hello, all. I have tried a number of searches to help me figure out how to solve my problem, but either I'm not wording my queries correctly or no one has asked this question (I'm guessing it's the former).

Here is my situation. I have 2 worksheets which both contain a column listing member IDs (usually SSNs). On the first worksheet [SMR], only the 9-digit numerical SSN is listed. On the second worksheet [DR], the member ID could appear in the same 9-digit format or with an additional alpha character at the end. In both worksheets, the member IDs appear in Column B.

I am trying to figure out a formula to use in Column A of worksheet SMR which will identify whether the member ID in that sheet appears anywhere in Column B of the DR worksheet.

For example, if ID 122333444 is in SMR and either the same exact ID or a variation like 122333444D appears in DR, it would produce a result to indicate there was a match (it could be a count or TRUE/FALSE value; I'm not picky).

Can someone please provide me with some advice?

Thanks!

 

[SirJB7]

Hi, Wookie!
In the 2nd worksheet, DR, which data type has the ID when only digits? Is it General, number or text?
Regards!

 

[Sajan]

Hello wookie,
Check out the "FIND" function. That should give you what you need.

It is of the following format:
=FIND(SMRValue, DRValue)

You can use ISNUMBER to determine if you got a numeric result, indicating that there was a match.

Cheers,
Sajan.

 

[Haseeb A]

Hello Wookie,

Or to use COUNTIF with "*". COUNTIF will avoid the cell format.

=IF(SUM(COUNTIF(DR!B:B,SMR!A2&{"","*"})),"Yes","No")

 

[Wookiee]

Thank you all very much for the responses!

Haseeb, your formula fit my purposes quite nicely, although I shortened it as shown below to avoid the IF evaluation and hopefully save a little memory since there's the potential for several thousand records to appear in the SMR spreadsheet.

=SUM(COUNTIF(DR!B:B,B2&{"","*"}))

All I have to do then is filter to any record whose value in Column A is zero to remove the non-matches.

Have a great Friday, everyone. You've certainly started my day off well.

:D