lookup and Replace with value

[Nadeem Masood]

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

 

[JLatham]

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]

This will split the multi-key-string into individual keys.
Next step, I have to use these keys and do a lookup in sheet2 to get another
field from a corresponding records. Can you please give me an idea of what
method(s) to use to accomplish this part --- just method and an usage
example would suffice.

Thanks for taking time to write complete sub. I appreciate it.

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.

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

 

[JLatham]

From your first posting, it would appear that a VLOOKUP() at this point would
be the best way to go.

Let us assume that you used the code I wrote pretty much as is, and that the
individual key fields are now on sheet [Report] in column B beginning in row
2; that is, Report!B2 now has an entry like ABC123

In another column on row 2, you can use a formula like this to get data from
the [Database] sheet:
=VLOOKUP($B2,Database!$A$2:$C$20,2,FALSE)

Things to change in the formula: First, the range on the Database sheet -
$A$2:$C$20 The $A should be the same column that the key (as ABC123) is in,
and the row, $2, should be the first one that the table uses. The $C should
refer to the last column on the Database sheet that has fields for the
records in it, and the $20 should be the last row number that the table uses.
So if your table went from column A to column F and from row 2 down to row
399, it would be $A$2:$F$399

Second, the ",2," portion tells which column of the table on the Database
sheet to return data from when there is a match. A ,1, would return the
information from the first column (A), while 2 returns information from the
2nd column. In the example where it went from A through F, then ,6, would
return information from column F.

The $ symbols in that table reference are to keep things from changing as
you fill the formula down/over to the right on the [Report] sheet. The $B in
the first parameter, "($B2,", keeps the column reference for the key field on
that sheet from changing but lets the row reference change as you fill it
down the sheet.

Hope this helps.

This will split the multi-key-string into individual keys.
Next step, I have to use these keys and do a lookup in sheet2 to get another
field from a corresponding records. Can you please give me an idea of what
method(s) to use to accomplish this part --- just method and an usage
example would suffice.

Thanks for taking time to write complete sub. I appreciate it.

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.

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

 

[Nadeem Masood]

It works for me. Thank you very much !

From your first posting, it would appear that a VLOOKUP() at this point
would
be the best way to go.

Let us assume that you used the code I wrote pretty much as is, and that
the
individual key fields are now on sheet [Report] in column B beginning in
row
2; that is, Report!B2 now has an entry like ABC123

In another column on row 2, you can use a formula like this to get data
from
the [Database] sheet:
=VLOOKUP($B2,Database!$A$2:$C$20,2,FALSE)

Things to change in the formula: First, the range on the Database sheet -
$A$2:$C$20 The $A should be the same column that the key (as ABC123) is
in,
and the row, $2, should be the first one that the table uses. The $C
should
refer to the last column on the Database sheet that has fields for the
records in it, and the $20 should be the last row number that the table
uses.
So if your table went from column A to column F and from row 2 down to row
399, it would be $A$2:$F$399

Second, the ",2," portion tells which column of the table on the Database
sheet to return data from when there is a match. A ,1, would return the
information from the first column (A), while 2 returns information from
the
2nd column. In the example where it went from A through F, then ,6, would
return information from column F.

The $ symbols in that table reference are to keep things from changing as
you fill the formula down/over to the right on the [Report] sheet. The $B
in
the first parameter, "($B2,", keeps the column reference for the key field
on
that sheet from changing but lets the row reference change as you fill it
down the sheet.

Hope this helps.

This will split the multi-key-string into individual keys.
Next step, I have to use these keys and do a lookup in sheet2 to get
another
field from a corresponding records. Can you please give me an idea of
what
method(s) to use to accomplish this part --- just method and an usage
example would suffice.

Thanks for taking time to write complete sub. I appreciate it.

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.

:

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