lookup and Replace with value

[Nadeem Masood]

BlankHi,

I have a huge report which has one of the columns containing a variable number of comma separated text (key of a record in database) -- like below.

Sheet1 (Report) - Column A"

ABC123,ABC456,ABC222
ABC234,ABC685
.............
.............

Each of the values above (ABC123 for example) is a key field for a record (database) and individual records are contained in another sheet - as below:

Sheet2 (database)
Column A: Column B Column C
ABC123 field_1 ..... field2
ABC456 field_1 ..... field2
ABC222 field_1 ..... field2

What I need to do is to insert another column in Sheet1 (Report). This column should contain the respective field1 values (in the same order as keys themselves).

What is the easiest/efficient way to accomplish this with formula and/or vba?

Thanks for your help.

Nadeem

 

[Sheeloo]

Use VBA
loop through row
Split into Keys
loop through keys
Lookup the value for each key
Concatenate
Next Key
Next Row

If is a one time job
then split using Text to Columns...
Use VLOOKUP() to find the values for each key
Concatenate