Concatenate columns, variable number of rows

D

Dawn

I have a report that returns multiple rows per ID number. The columns always
stay the same. The number of rows per ID number is variable. I would like
to group the info per ID number into one cell. If the number of rows was
constant I could concatenate easily. But each ID number could have one row
or a hundred rows, it is completely variable. Can VBA be used to
concatenate?

The way the report currently looks, it gives the ID number in the first row,
then each subsequent row has the ID number blank until there is a new ID
number to show. This report comes out of a database.

Sample data:

ID Country Number
1234-5678 US 123456
[blank] GB 789123
[blank] IT 456789
4567-8912 US 23456
8912-3456 EP 1234567
[blank] US 8912345
[blank] JP 456789
[blank] CN 123456789
2345-6789 [blank]

So in this example what I want to end up with is two columns, on row per ID
number:

ID Related Matters (one cell)
1234-5678 US 123456; GB 789123; IT 456789
4567-8912 US 23456
8912-3456 EP 1234567; US 8912345; JP 456789; CN 123456789
2345-6789 [blank]

Thanks in advance for any help/ideas anyone can give!
 
D

Dawn

The report is coming from a database, going into excel format. I'm saving
into .csv. If I save into .xls it creates it so the the ID number cell spans
the multiple rows, instead of returning blank rows, if that makes any
difference.

I didn't create the report, however, it's just one I use. I'm not sure I
can get it to alter in format at all. Perhaps could get it altered to fill
in the blank ID numbers, but I'm not sure (just guessing that's what you're
wondering about)
 
R

Rick Rothstein \(MVP - VB\)

I hope my terminology doesn't confuse you (it made it easier for me to
visualize), but I refer to your existing data (the data you get from your
"report" CSV file consisting of the 3 column ID, Country and Number) as
"Data" and the resulting output as "Report". In the code below, simply set
the appropriate values for your worksheets in the section I have marked off
and then run the macro...

Sub ConsolidateData()
Dim CurrentID As String
Dim LastCellInRow As Long
Dim DataHeaderRow As Long
Dim ReportHeaderRow As Long

' *********************************************
' * Set your worksheet values in this section *
' *********************************************
Const DataIDCol = "A"
Const DataCountryCol = "B"
Const DataNumberCol = "C"
DataHeaderRow = 1 '0 if no header
Const ReportIDCol = "A"
Const ReportRelatedMattersCol = "B"
ReportHeaderRow = 1 '0 if no header
' *********************************************

With Worksheets("Data")
CurrentID = .Cells(2, DataIDCol).Value
LastCellInRow = .Cells(.Rows.Count, DataIDCol).End(xlUp).Row
Do While DataHeaderRow <= LastCellInRow
DataHeaderRow = DataHeaderRow + 1
If .Cells(DataHeaderRow, DataIDCol).Value <> "" Then
ReportHeaderRow = ReportHeaderRow + 1
CurrentID = .Cells(DataHeaderRow, DataIDCol).Value
Worksheets("Report").Cells(ReportHeaderRow, _
ReportIDCol).Value = CurrentID
Worksheets("Report").Cells(ReportHeaderRow, _
ReportRelatedMattersCol).Value = _
.Cells(DataHeaderRow, DataCountryCol).Value & " " & _
.Cells(DataHeaderRow, DataNumberCol).Value
Else
Worksheets("Report").Cells(ReportHeaderRow, _
ReportRelatedMattersCol).Value = _
Worksheets("Report").Cells(ReportHeaderRow, _
ReportRelatedMattersCol).Value & "; " & _
.Cells(DataHeaderRow, DataCountryCol).Value & " " & _
.Cells(DataHeaderRow, DataNumberCol).Value
End If
If DataHeaderRow = LastCellInRow Then Exit Do
Loop
End With
End Sub


Rick



Dawn said:
I have a report that returns multiple rows per ID number. The columns
always
stay the same. The number of rows per ID number is variable. I would
like
to group the info per ID number into one cell. If the number of rows was
constant I could concatenate easily. But each ID number could have one
row
or a hundred rows, it is completely variable. Can VBA be used to
concatenate?

The way the report currently looks, it gives the ID number in the first
row,
then each subsequent row has the ID number blank until there is a new ID
number to show. This report comes out of a database.

Sample data:

ID Country Number
1234-5678 US 123456
[blank] GB 789123
[blank] IT 456789
4567-8912 US 23456
8912-3456 EP 1234567
[blank] US 8912345
[blank] JP 456789
[blank] CN 123456789
2345-6789 [blank]

So in this example what I want to end up with is two columns, on row per
ID
number:

ID Related Matters (one cell)
1234-5678 US 123456; GB 789123; IT 456789
4567-8912 US 23456
8912-3456 EP 1234567; US 8912345; JP 456789; CN 123456789
2345-6789 [blank]

Thanks in advance for any help/ideas anyone can give!
Click to expand...
 
D

Dawn

Oh, that is a thing of great beauty Rick, thank you very much, works perfectly!

Rick Rothstein (MVP - VB) said:
I hope my terminology doesn't confuse you (it made it easier for me to
visualize), but I refer to your existing data (the data you get from your
"report" CSV file consisting of the 3 column ID, Country and Number) as
"Data" and the resulting output as "Report". In the code below, simply set
the appropriate values for your worksheets in the section I have marked off
and then run the macro...

Sub ConsolidateData()
Dim CurrentID As String
Dim LastCellInRow As Long
Dim DataHeaderRow As Long
Dim ReportHeaderRow As Long

' *********************************************
' * Set your worksheet values in this section *
' *********************************************
Const DataIDCol = "A"
Const DataCountryCol = "B"
Const DataNumberCol = "C"
DataHeaderRow = 1 '0 if no header
Const ReportIDCol = "A"
Const ReportRelatedMattersCol = "B"
ReportHeaderRow = 1 '0 if no header
' *********************************************

With Worksheets("Data")
CurrentID = .Cells(2, DataIDCol).Value
LastCellInRow = .Cells(.Rows.Count, DataIDCol).End(xlUp).Row
Do While DataHeaderRow <= LastCellInRow
DataHeaderRow = DataHeaderRow + 1
If .Cells(DataHeaderRow, DataIDCol).Value <> "" Then
ReportHeaderRow = ReportHeaderRow + 1
CurrentID = .Cells(DataHeaderRow, DataIDCol).Value
Worksheets("Report").Cells(ReportHeaderRow, _
ReportIDCol).Value = CurrentID
Worksheets("Report").Cells(ReportHeaderRow, _
ReportRelatedMattersCol).Value = _
.Cells(DataHeaderRow, DataCountryCol).Value & " " & _
.Cells(DataHeaderRow, DataNumberCol).Value
Else
Worksheets("Report").Cells(ReportHeaderRow, _
ReportRelatedMattersCol).Value = _
Worksheets("Report").Cells(ReportHeaderRow, _
ReportRelatedMattersCol).Value & "; " & _
.Cells(DataHeaderRow, DataCountryCol).Value & " " & _
.Cells(DataHeaderRow, DataNumberCol).Value
End If
If DataHeaderRow = LastCellInRow Then Exit Do
Loop
End With
End Sub


Rick



Dawn said:
I have a report that returns multiple rows per ID number. The columns
always
stay the same. The number of rows per ID number is variable. I would
like
to group the info per ID number into one cell. If the number of rows was
constant I could concatenate easily. But each ID number could have one
row
or a hundred rows, it is completely variable. Can VBA be used to
concatenate?

The way the report currently looks, it gives the ID number in the first
row,
then each subsequent row has the ID number blank until there is a new ID
number to show. This report comes out of a database.

Sample data:

ID Country Number
1234-5678 US 123456
[blank] GB 789123
[blank] IT 456789
4567-8912 US 23456
8912-3456 EP 1234567
[blank] US 8912345
[blank] JP 456789
[blank] CN 123456789
2345-6789 [blank]

So in this example what I want to end up with is two columns, on row per
ID
number:

ID Related Matters (one cell)
1234-5678 US 123456; GB 789123; IT 456789
4567-8912 US 23456
8912-3456 EP 1234567; US 8912345; JP 456789; CN 123456789
2345-6789 [blank]

Thanks in advance for any help/ideas anyone can give!
Click to expand...
Click to expand...
 
R

Rick Rothstein \(MVP - VB\)

You are welcome. I have to apologize to you though... I meant to put the
worksheet names in section you fill in so you wouldn't have had to search
for them in the actual code. In case you (or other readers of this thread)
might find it useful, here is that code (with it, you only have to change
values in the marked-off section and nowhere else) ...

Sub ConsolidateData()
Dim CurrentID As String
Dim LastCellInRow As Long
Dim DataHeaderRow As Long
Dim ReportHeaderRow As Long

' *********************************************
' * Set your worksheet values in this section *
' *********************************************
Const DataSheetName = "Data"
Const DataIDCol = "A"
Const DataCountryCol = "B"
Const DataNumberCol = "C"
DataHeaderRow = 1 '0 if no header
Const ReportSheetName = "Report"
Const ReportIDCol = "A"
Const ReportRelatedMattersCol = "B"
ReportHeaderRow = 1 '0 if no header
' *********************************************

With Worksheets(DataSheetName)
CurrentID = .Cells(2, DataIDCol).Value
LastCellInRow = .Cells(.Rows.Count, DataIDCol).End(xlUp).Row
Do While DataHeaderRow <= LastCellInRow
DataHeaderRow = DataHeaderRow + 1
If .Cells(DataHeaderRow, DataIDCol).Value <> "" Then
ReportHeaderRow = ReportHeaderRow + 1
CurrentID = .Cells(DataHeaderRow, DataIDCol).Value
Worksheets(ReportSheetName).Cells(ReportHeaderRow, _
ReportIDCol).Value = CurrentID
Worksheets(ReportSheetName).Cells(ReportHeaderRow, _
ReportRelatedMattersCol).Value = _
.Cells(DataHeaderRow, DataCountryCol).Value & " " & _
.Cells(DataHeaderRow, DataNumberCol).Value
Else
Worksheets(ReportSheetName).Cells(ReportHeaderRow, _
ReportRelatedMattersCol).Value = _
Worksheets(ReportSheetName).Cells(ReportHeaderRow, _
ReportRelatedMattersCol).Value & "; " & _
.Cells(DataHeaderRow, DataCountryCol).Value & " " & _
.Cells(DataHeaderRow, DataNumberCol).Value
End If
If DataHeaderRow = LastCellInRow Then Exit Do
Loop
End With
End Sub


Rick




Dawn said:
Oh, that is a thing of great beauty Rick, thank you very much, works
perfectly!

Rick Rothstein (MVP - VB) said:
I hope my terminology doesn't confuse you (it made it easier for me to
visualize), but I refer to your existing data (the data you get from your
"report" CSV file consisting of the 3 column ID, Country and Number) as
"Data" and the resulting output as "Report". In the code below, simply
set
the appropriate values for your worksheets in the section I have marked
off
and then run the macro...

Sub ConsolidateData()
Dim CurrentID As String
Dim LastCellInRow As Long
Dim DataHeaderRow As Long
Dim ReportHeaderRow As Long

' *********************************************
' * Set your worksheet values in this section *
' *********************************************
Const DataIDCol = "A"
Const DataCountryCol = "B"
Const DataNumberCol = "C"
DataHeaderRow = 1 '0 if no header
Const ReportIDCol = "A"
Const ReportRelatedMattersCol = "B"
ReportHeaderRow = 1 '0 if no header
' *********************************************

With Worksheets("Data")
CurrentID = .Cells(2, DataIDCol).Value
LastCellInRow = .Cells(.Rows.Count, DataIDCol).End(xlUp).Row
Do While DataHeaderRow <= LastCellInRow
DataHeaderRow = DataHeaderRow + 1
If .Cells(DataHeaderRow, DataIDCol).Value <> "" Then
ReportHeaderRow = ReportHeaderRow + 1
CurrentID = .Cells(DataHeaderRow, DataIDCol).Value
Worksheets("Report").Cells(ReportHeaderRow, _
ReportIDCol).Value = CurrentID
Worksheets("Report").Cells(ReportHeaderRow, _
ReportRelatedMattersCol).Value = _
.Cells(DataHeaderRow, DataCountryCol).Value & " " & _
.Cells(DataHeaderRow, DataNumberCol).Value
Else
Worksheets("Report").Cells(ReportHeaderRow, _
ReportRelatedMattersCol).Value = _
Worksheets("Report").Cells(ReportHeaderRow, _
ReportRelatedMattersCol).Value & "; " & _
.Cells(DataHeaderRow, DataCountryCol).Value & " " & _
.Cells(DataHeaderRow, DataNumberCol).Value
End If
If DataHeaderRow = LastCellInRow Then Exit Do
Loop
End With
End Sub


Rick



Dawn said:
I have a report that returns multiple rows per ID number. The columns
always
stay the same. The number of rows per ID number is variable. I would
like
to group the info per ID number into one cell. If the number of rows
was
constant I could concatenate easily. But each ID number could have one
row
or a hundred rows, it is completely variable. Can VBA be used to
concatenate?

The way the report currently looks, it gives the ID number in the first
row,
then each subsequent row has the ID number blank until there is a new
ID
number to show. This report comes out of a database.

Sample data:

ID Country Number
1234-5678 US 123456
[blank] GB 789123
[blank] IT 456789
4567-8912 US 23456
8912-3456 EP 1234567
[blank] US 8912345
[blank] JP 456789
[blank] CN 123456789
2345-6789 [blank]

So in this example what I want to end up with is two columns, on row
per
ID
number:

ID Related Matters (one cell)
1234-5678 US 123456; GB 789123; IT 456789
4567-8912 US 23456
8912-3456 EP 1234567; US 8912345; JP 456789; CN 123456789
2345-6789 [blank]

Thanks in advance for any help/ideas anyone can give!
Click to expand...
Click to expand...
Click to expand...
 
D

Dawn

Cool thanks -- I just named my tabs "Data" and "Report" like you did, which
works fine for my purposes, but this is a nice improvement. Thanks again!

Rick Rothstein (MVP - VB) said:
You are welcome. I have to apologize to you though... I meant to put the
worksheet names in section you fill in so you wouldn't have had to search
for them in the actual code. In case you (or other readers of this thread)
might find it useful, here is that code (with it, you only have to change
values in the marked-off section and nowhere else) ...

Sub ConsolidateData()
Dim CurrentID As String
Dim LastCellInRow As Long
Dim DataHeaderRow As Long
Dim ReportHeaderRow As Long

' *********************************************
' * Set your worksheet values in this section *
' *********************************************
Const DataSheetName = "Data"
Const DataIDCol = "A"
Const DataCountryCol = "B"
Const DataNumberCol = "C"
DataHeaderRow = 1 '0 if no header
Const ReportSheetName = "Report"
Const ReportIDCol = "A"
Const ReportRelatedMattersCol = "B"
ReportHeaderRow = 1 '0 if no header
' *********************************************

With Worksheets(DataSheetName)
CurrentID = .Cells(2, DataIDCol).Value
LastCellInRow = .Cells(.Rows.Count, DataIDCol).End(xlUp).Row
Do While DataHeaderRow <= LastCellInRow
DataHeaderRow = DataHeaderRow + 1
If .Cells(DataHeaderRow, DataIDCol).Value <> "" Then
ReportHeaderRow = ReportHeaderRow + 1
CurrentID = .Cells(DataHeaderRow, DataIDCol).Value
Worksheets(ReportSheetName).Cells(ReportHeaderRow, _
ReportIDCol).Value = CurrentID
Worksheets(ReportSheetName).Cells(ReportHeaderRow, _
ReportRelatedMattersCol).Value = _
.Cells(DataHeaderRow, DataCountryCol).Value & " " & _
.Cells(DataHeaderRow, DataNumberCol).Value
Else
Worksheets(ReportSheetName).Cells(ReportHeaderRow, _
ReportRelatedMattersCol).Value = _
Worksheets(ReportSheetName).Cells(ReportHeaderRow, _
ReportRelatedMattersCol).Value & "; " & _
.Cells(DataHeaderRow, DataCountryCol).Value & " " & _
.Cells(DataHeaderRow, DataNumberCol).Value
End If
If DataHeaderRow = LastCellInRow Then Exit Do
Loop
End With
End Sub


Rick




Dawn said:
Oh, that is a thing of great beauty Rick, thank you very much, works
perfectly!

Rick Rothstein (MVP - VB) said:
I hope my terminology doesn't confuse you (it made it easier for me to
visualize), but I refer to your existing data (the data you get from your
"report" CSV file consisting of the 3 column ID, Country and Number) as
"Data" and the resulting output as "Report". In the code below, simply
set
the appropriate values for your worksheets in the section I have marked
off
and then run the macro...

Sub ConsolidateData()
Dim CurrentID As String
Dim LastCellInRow As Long
Dim DataHeaderRow As Long
Dim ReportHeaderRow As Long

' *********************************************
' * Set your worksheet values in this section *
' *********************************************
Const DataIDCol = "A"
Const DataCountryCol = "B"
Const DataNumberCol = "C"
DataHeaderRow = 1 '0 if no header
Const ReportIDCol = "A"
Const ReportRelatedMattersCol = "B"
ReportHeaderRow = 1 '0 if no header
' *********************************************

With Worksheets("Data")
CurrentID = .Cells(2, DataIDCol).Value
LastCellInRow = .Cells(.Rows.Count, DataIDCol).End(xlUp).Row
Do While DataHeaderRow <= LastCellInRow
DataHeaderRow = DataHeaderRow + 1
If .Cells(DataHeaderRow, DataIDCol).Value <> "" Then
ReportHeaderRow = ReportHeaderRow + 1
CurrentID = .Cells(DataHeaderRow, DataIDCol).Value
Worksheets("Report").Cells(ReportHeaderRow, _
ReportIDCol).Value = CurrentID
Worksheets("Report").Cells(ReportHeaderRow, _
ReportRelatedMattersCol).Value = _
.Cells(DataHeaderRow, DataCountryCol).Value & " " & _
.Cells(DataHeaderRow, DataNumberCol).Value
Else
Worksheets("Report").Cells(ReportHeaderRow, _
ReportRelatedMattersCol).Value = _
Worksheets("Report").Cells(ReportHeaderRow, _
ReportRelatedMattersCol).Value & "; " & _
.Cells(DataHeaderRow, DataCountryCol).Value & " " & _
.Cells(DataHeaderRow, DataNumberCol).Value
End If
If DataHeaderRow = LastCellInRow Then Exit Do
Loop
End With
End Sub


Rick



I have a report that returns multiple rows per ID number. The columns
always
stay the same. The number of rows per ID number is variable. I would
like
to group the info per ID number into one cell. If the number of rows
was
constant I could concatenate easily. But each ID number could have one
row
or a hundred rows, it is completely variable. Can VBA be used to
concatenate?

The way the report currently looks, it gives the ID number in the first
row,
then each subsequent row has the ID number blank until there is a new
ID
number to show. This report comes out of a database.

Sample data:

ID Country Number
1234-5678 US 123456
[blank] GB 789123
[blank] IT 456789
4567-8912 US 23456
8912-3456 EP 1234567
[blank] US 8912345
[blank] JP 456789
[blank] CN 123456789
2345-6789 [blank]

So in this example what I want to end up with is two columns, on row
per
ID
number:

ID Related Matters (one cell)
1234-5678 US 123456; GB 789123; IT 456789
4567-8912 US 23456
8912-3456 EP 1234567; US 8912345; JP 456789; CN 123456789
2345-6789 [blank]

Thanks in advance for any help/ideas anyone can give!
Click to expand...
Click to expand...
Click to expand...
 
K

Kerry

Rick...I like this concept. I have similar data that has Firm_Name in Column
A, ContactEmail in Column N, ExecutiveEmail in Column S. Each Firm has
multiple office locations, therefore can have multiple rows of information.
In to create a column of email address that I can use to automatically email
files to these addresses. One thing I need to do is check to make sure emails
are not the same.

My Date

Firm_Name ContactEmail ExecutiveEmail
ABC (e-mail address removed) (e-mail address removed)
ABC (e-mail address removed) (e-mail address removed)
XYZ (e-mail address removed) (e-mail address removed)
AXX (e-mail address removed)

ContactMail needs to be who the email is sent to, and ExecutiveEmail needes
to be cc. If ContactEmail = ExecutiveEmail then only use ContactEmail in To
and cc ExecutiveEmail. If ContactEmail <> ExecutiveEmail then ContactEmail To
and ExectutiveEmail cc. Sometimes Contact email is blank and there are
multiple ExecutiveMail rows, I would like to use an additional comumn where
ExecutivePostition is used to select who email is addressed to and cc to. Can
this be done?

Thanks,
Kerry

Rick Rothstein (MVP - VB) said:
You are welcome. I have to apologize to you though... I meant to put the
worksheet names in section you fill in so you wouldn't have had to search
for them in the actual code. In case you (or other readers of this thread)
might find it useful, here is that code (with it, you only have to change
values in the marked-off section and nowhere else) ...

Sub ConsolidateData()
Dim CurrentID As String
Dim LastCellInRow As Long
Dim DataHeaderRow As Long
Dim ReportHeaderRow As Long

' *********************************************
' * Set your worksheet values in this section *
' *********************************************
Const DataSheetName = "Data"
Const DataIDCol = "A"
Const DataCountryCol = "B"
Const DataNumberCol = "C"
DataHeaderRow = 1 '0 if no header
Const ReportSheetName = "Report"
Const ReportIDCol = "A"
Const ReportRelatedMattersCol = "B"
ReportHeaderRow = 1 '0 if no header
' *********************************************

With Worksheets(DataSheetName)
CurrentID = .Cells(2, DataIDCol).Value
LastCellInRow = .Cells(.Rows.Count, DataIDCol).End(xlUp).Row
Do While DataHeaderRow <= LastCellInRow
DataHeaderRow = DataHeaderRow + 1
If .Cells(DataHeaderRow, DataIDCol).Value <> "" Then
ReportHeaderRow = ReportHeaderRow + 1
CurrentID = .Cells(DataHeaderRow, DataIDCol).Value
Worksheets(ReportSheetName).Cells(ReportHeaderRow, _
ReportIDCol).Value = CurrentID
Worksheets(ReportSheetName).Cells(ReportHeaderRow, _
ReportRelatedMattersCol).Value = _
.Cells(DataHeaderRow, DataCountryCol).Value & " " & _
.Cells(DataHeaderRow, DataNumberCol).Value
Else
Worksheets(ReportSheetName).Cells(ReportHeaderRow, _
ReportRelatedMattersCol).Value = _
Worksheets(ReportSheetName).Cells(ReportHeaderRow, _
ReportRelatedMattersCol).Value & "; " & _
.Cells(DataHeaderRow, DataCountryCol).Value & " " & _
.Cells(DataHeaderRow, DataNumberCol).Value
End If
If DataHeaderRow = LastCellInRow Then Exit Do
Loop
End With
End Sub


Rick




Dawn said:
Oh, that is a thing of great beauty Rick, thank you very much, works
perfectly!

Rick Rothstein (MVP - VB) said:
I hope my terminology doesn't confuse you (it made it easier for me to
visualize), but I refer to your existing data (the data you get from your
"report" CSV file consisting of the 3 column ID, Country and Number) as
"Data" and the resulting output as "Report". In the code below, simply
set
the appropriate values for your worksheets in the section I have marked
off
and then run the macro...

Sub ConsolidateData()
Dim CurrentID As String
Dim LastCellInRow As Long
Dim DataHeaderRow As Long
Dim ReportHeaderRow As Long

' *********************************************
' * Set your worksheet values in this section *
' *********************************************
Const DataIDCol = "A"
Const DataCountryCol = "B"
Const DataNumberCol = "C"
DataHeaderRow = 1 '0 if no header
Const ReportIDCol = "A"
Const ReportRelatedMattersCol = "B"
ReportHeaderRow = 1 '0 if no header
' *********************************************

With Worksheets("Data")
CurrentID = .Cells(2, DataIDCol).Value
LastCellInRow = .Cells(.Rows.Count, DataIDCol).End(xlUp).Row
Do While DataHeaderRow <= LastCellInRow
DataHeaderRow = DataHeaderRow + 1
If .Cells(DataHeaderRow, DataIDCol).Value <> "" Then
ReportHeaderRow = ReportHeaderRow + 1
CurrentID = .Cells(DataHeaderRow, DataIDCol).Value
Worksheets("Report").Cells(ReportHeaderRow, _
ReportIDCol).Value = CurrentID
Worksheets("Report").Cells(ReportHeaderRow, _
ReportRelatedMattersCol).Value = _
.Cells(DataHeaderRow, DataCountryCol).Value & " " & _
.Cells(DataHeaderRow, DataNumberCol).Value
Else
Worksheets("Report").Cells(ReportHeaderRow, _
ReportRelatedMattersCol).Value = _
Worksheets("Report").Cells(ReportHeaderRow, _
ReportRelatedMattersCol).Value & "; " & _
.Cells(DataHeaderRow, DataCountryCol).Value & " " & _
.Cells(DataHeaderRow, DataNumberCol).Value
End If
If DataHeaderRow = LastCellInRow Then Exit Do
Loop
End With
End Sub


Rick



I have a report that returns multiple rows per ID number. The columns
always
stay the same. The number of rows per ID number is variable. I would
like
to group the info per ID number into one cell. If the number of rows
was
constant I could concatenate easily. But each ID number could have one
row
or a hundred rows, it is completely variable. Can VBA be used to
concatenate?

The way the report currently looks, it gives the ID number in the first
row,
then each subsequent row has the ID number blank until there is a new
ID
number to show. This report comes out of a database.

Sample data:

ID Country Number
1234-5678 US 123456
[blank] GB 789123
[blank] IT 456789
4567-8912 US 23456
8912-3456 EP 1234567
[blank] US 8912345
[blank] JP 456789
[blank] CN 123456789
2345-6789 [blank]

So in this example what I want to end up with is two columns, on row
per
ID
number:

ID Related Matters (one cell)
1234-5678 US 123456; GB 789123; IT 456789
4567-8912 US 23456
8912-3456 EP 1234567; US 8912345; JP 456789; CN 123456789
2345-6789 [blank]

Thanks in advance for any help/ideas anyone can give!
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Conditional format based on 2 records 0
Borders on Fixed # of Columns, Variable # of Rows 3
sorting data based on variable number of rows 1
count number of rows (variable range) 3
Inserting Variable Number of Rows 2
Copying range to variable number of rows 2
Help Counting 10 Columns * X Number of Rows 5
Transpose variable columns of data into addtional rows 1

Top