Concatenate columns, variable number of rows

  • Thread starter Thread starter Dawn
  • Start date Start date
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!
 
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)
 
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...
 
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...
 
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...
 
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...
 
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...
 
Hello have a similar problem, I have two columns, A e B, I need to concatenate in C, one has value only every n cells, n that varies, and the other has a value in every cell.
I need a code that will start looking from the active cell in col A, if it is populated then count how many empty cell before the next no-empty cell and concatenate that first cell in A i times with B until non blank cell, at that point use this new cell to repeat the above process and so on with the next non-blank cell.

I attach example.

thank you
 

Attachments

Back
Top