Well, just my personal preference, plus the fact that your data can have a
variable number of keys/commas, I'd go with VBA.

Here's some code that should do the trick for you. I've annotated what you
may need to change in it based on the layout of Sheet1 (Report).

To insert this code, open the workbook, press [Alt]+[F11] to enter the VBA
editor. Then choose Insert | Module and copy, paste and modify the code
below in the blank module presented to you. Close the VBA editor. Choose
Sheet1 and use Tools | Macro | Macros to select and [Run] the code.

Sub BreakOutKeyFields()
'assumes combined, comma separated entries
'are in column A
'and that column B is available to
'put the individual entries into
'adjust these constants as needed
Const sourceCol = "A"
Const destCol = "B"
'also assumes that first
'entry to be broken apart is in row 2
Const firstDataRow = 2 ' change if needed
Dim lastRow As Long
Dim tmpString As String
Dim sourceRange As Range
Dim anySourceEntry As Range

lastRow = Range(sourceCol & Rows.Count).End(xlUp).Row
Set sourceRange = Range(sourceCol & firstDataRow & ":" & _
sourceCol & lastRow)
For Each anySourceEntry In sourceRange
If Not IsEmpty(anySourceEntry) Then
tmpString = anySourceEntry.Value
'need a separator at the very end
If Right(tmpString, 1) < "," Then
tmpString = Trim(tmpString) & ","
End If
Do While InStr(tmpString, ",")
'display the key
Range(destCol & Rows.Count).End(xlUp).Offset(1, 0) = _
Left(tmpString, InStr(tmpString, ",") - 1)
'remove last key from working string
tmpString = Right(tmpString, Len(tmpString) - _
InStr(tmpString, ","))
Loop
End If
Next
End Sub

Hope this helps.

"Nadeem Masood" wrote:

Hi,

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