Find the location of matching cell value (single number) in another column that contains multiple numbers separated by commas

[FishSea]

Hi,

I've been stuck on this one for a bit now. I have two sheets with a table in each, examples below:

Sheet name "tiers", table name "orig":

cid	tier
92944741	15
40695415	15
94022261	14
72356125	14
99855973	14
7216489	14
30203042	14
63537001	14
6606033	14
18674819	14
16886282	14
54573501	14
80292130	14
24793180	14
33398063	14
85914584	14

<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>
</tbody>

Sheet name "customers", table name "contacts":

Email Address	cid list
OOEK@fake.com	17644399,40695415,0
XMIQ@fake.com	94022261,41276315,0
RMEH@fake.com	99855973,69744518,0
JCGZ@fake.com	30203042,27393148,0
VBPH@fake.com	54573501,347474742,81366419,0
XQHK@fake.com	50811900,16886282,0
AUKJ@fake.com	85914584,20671229,0
PBWS@fake.com	92944741,31197542,0
NBFI@fake.com	22992367,6606033,0
UKWC@fake.com	33398063,62629360,9999999,0
NTUE@fake.com	72356125,223344,60758952,0
ITTE@fake.com	18674819,24194445,0
EYMG@fake.com	7216489,52159608,3848484,0
NXJB@fake.com	24793180,25590276,0
IVDR@fake.com	63537001,74712916,0
SYTZ@fake.com	30346954,80292130,0

<colgroup><col><col></colgroup><tbody>
</tbody>

What I'm trying to do is, in a 3rd column in the "orig" table, show the email address for the corresponding cid, by finding the matching cid value in the "contacts" table 'cid list'. I'm open to both formulas and/or VBA, however I'm less keen on , because there's not a set amount of comma separated numbers that will appear in the cid list. It could be 2, 3, or more, the numbers could have different amounts of digits, and this will be different for different contacts/customers. Also, I'd prefer to do this in the 'orig' table, as it is already organized by 'tier', and I'm trying to make this an automated process as there is new data for this every day.

I've been able to find a formula that finds if the cid is located in the cid list, but not where the cid is located in that list (and by extension, haven't been able to return the corresponding email). When testing this formula, I was using it in the customers sheet/contacts table (reverse of where I want, as described above, but it helped with testing the formula) - 'A' refers to the cid column in the orig table, and 'B' to the cid list column in the contacts table:

=IF(SUM(IFERROR(SEARCH(cid!A:A,B1),0))>0, "FOUND", "NOT FOUND")

Any help would be appreciated. Please let me know if any clarification would help - this is my first time posting a question here.

 

[mumps]

Try:

Sub Test()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Sheets("tiers").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim cid As Range
    Dim foundcid As Range
    For Each cid In Sheets("tiers").Range("A2:A" & LastRow)
        Set foundcid = Sheets("customers").Range("B:B").Find(cid, LookIn:=xlValues, lookat:=xlPart)
        If Not foundcid Is Nothing Then
            cid.Offset(0, 2) = foundcid.Offset(, -1)
        End If
    Next cid
    Application.ScreenUpdating = True
End Sub

 

[FishSea]

Try:

Sub Test()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Sheets("tiers").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim cid As Range
    Dim foundcid As Range
    For Each cid In Sheets("tiers").Range("A2:A" & LastRow)
        Set foundcid = Sheets("customers").Range("B:B").Find(cid, LookIn:=xlValues, lookat:=xlPart)
        If Not foundcid Is Nothing Then
            cid.Offset(0, 2) = foundcid.Offset(, -1)
        End If
    Next cid
    Application.ScreenUpdating = True
End Sub

That is perfect, it does exactly what I wanted and was easy to implement. Thank you very much!

 

[mumps]

You are very welcome.

 

[Eric W]

Here's a formula you can put in column 3 of your orig table:

=IFERROR(INDEX(Customers!$A$2:$A$17,1/(1/MIN(IF(ISNUMBER(FIND(","&$I2&",",","&Customers!$B$2:$B$17&",")),ROW($B$2:$B$17)-ROW($B$2)+1)))),"No match")
confirmed with Control+Shift+Enter.

However, if you're happy with mumps macro, you'd probably want to stick with that since it's more efficient.

 

[Aladin Akyurek]

In C2 of tiers enter and copy down:

=LOOKUP(9.99999999999999E+307,FIND(","&A2&",",","&customers!$B$2:$B$17&","),customers!$A$2:$A$17)

 

[mumps]

Hi Aladin. Could you be so kind as to explain how each part of your formula works? Thank you.

 

[Aladin Akyurek]

Hi Aladin. Could you be so kind as to explain how each part of your formula works? Thank you.

See: http://www.mrexcel.com/forum/excel-questions/724843-there-case-sensitive-vlookup.html#post3568323

 

[mumps]

Much appreciated.

 

[FishSea]

Thanks Aladin! I tried that Lookup formula, it also works great for my example chart. In my actual data, the number of contacts changes each day, and while I could most likely change the range in the lookup formula with a little work, I've already implemented the macro mumps provided. However, I'm sure that formula could/will come in handy for me in the future if a situation arises where the macro is less efficient. Either way, good to know this can also be done without macro use!