Lookup using a single cell with concatenated values and return values to a single cell as concatenated values

[norwoodkd2001]

I have a situation where we are asking users to provide the name or names of groups that can be assigned to an underwriting condition. They are to enter all the names that apply separated by a comma into one cell. I have a another tab that has the names and the associated ID's. I need to provide the ID's that relate to all the names entered in the group name cell to a single field separated by commas. Also not every condition will have multiple group names. Below is an example of the source sheet where the users are populating column B and column C being the expected outcome of the lookup and the sheet that contains the the Group Names and associated Group ID's.

Any help would be greatly appreciated!

 

[Special-K99]

Not very user friendly and prone to human error. You want them to enter "Employment / Income, Purchase, Comments, DU, General" and expect them to type it 100% correctly?
What happens if they spell a Group name incorrectly?

Why not just provide them with a list where they can put a number 1 2 or 3 (Condition) against each list item?

 

[norwoodkd2001]

Not very user friendly and prone to human error. You want them to enter "Employment / Income, Purchase, Comments, DU, General" and expect them to type it 100% correctly?
What happens if they spell a Group name incorrectly?

Why not just provide them with a list where they can put a number 1 2 or 3 (Condition) against each list item?

You are correct it is not very user friendly but I am trying to make it a little easier for the user. The problem is the amount of data to be collected and limiting the amount of time to update the spreadsheet.

There will be over 200 rows of data (conditions) and the list of group names is actually over 35 not just the subset I presented in my screenshot.. This is for an upload to a 3rd party system and the vendor only wants the numeric values but it would be very difficult for a user to always refer to a chart on each condition to make sure they entered the correct numeric value and I need to make it easy for them to copy/paste so I didn't go with a drop down list.

I felt it was easier for them to type in the name because I can always correct for spelling errors but I wouldn't be able to identify if they keyed in a wrong number. I'm sure I will get some errors on the lookup until I correct the spelling errors but I'll be scrubbing the data before it goes the vendor anyway.

So based upon this....any solutions on how to do the lookup?

 

[Fluff]

What version of Xl will this be run on?

 

[norwoodkd2001]

What version of Xl will this be run on?

2016

 

[Fluff]

That's a shame, it can be done very easily with TEXTJOIN, but you don't have that.
Would a VBA solution be ok, or does it need to be a formula?

 

[norwoodkd2001]

Yes, VBA would work great!

 

[Fluff]

With data like

Book1
	A	B				F	G	H
1		County
2	15	Bedfordshire					Surrey, Bristol	24, 43
3	38	Berkshire					Devon, Essex, Kent	5, 6, 18
4	43	Bristol					Kent, Wiltshire, Cumbria	18, 17, 44
5	20	Buckinghamshire					West Yorkshire	14
6	40	Cambridgeshire					Dorset, Cornwall	13, 46
7	39	Cheshire
8	48	City of London
9	46	Cornwall
10	35	County Durham
11	44	Cumbria
12	32	Derbyshire
13	5	Devon
14	13	Dorset
15	37	East Riding of Yorkshire
16	41	East Sussex
17	6	Essex
18	21	Gloucestershire
19	26	Greater London
20	22	Greater Manchester
21	7	Hampshire
22	45	Herefordshire
23	25	Hertfordshire
24	28	Isle of Wight
25	18	Kent
26	10	Lancashire
27	11	Leicestershire
28	8	Lincolnshire
29	29	Merseyside
Data

Sub norwoodkd()
    Dim Sp As Variant
    Dim i As Long
    Dim Cl As Range
    Dim txt As String
    
    With CreateObject("scripting.dictionary")
        For Each Cl In Range("B2", Range("B" & Rows.Count).End(xlUp))
            .Item(Cl.Value) = Cl.Offset(, -1).Value
        Next Cl
        For Each Cl In Range("G2", Range("G" & Rows.Count).End(xlUp))
            Sp = Split(Cl.Value, ", ")
            For i = 0 To UBound(Sp)
                If .exists(Sp(i)) Then txt = txt & .Item(Sp(i)) & ", "
            Next i
            Cl.Offset(, 1).Value = Left(txt, Len(txt) - 2)
            txt = ""
        Next Cl
    End With
End Sub

 

[norwoodkd2001]

Awesome! I will try it out. Thanks so much!

 

[Fluff]

You're welcome & thanks for the feedback