lookup and Replace with value
 

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

lookup and Replace with value
 

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
"Nadeem Masood" wrote:

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