Hi Sean,

I apologize for my late response! I think your code will work much better
than a formula(s), but I do have a question and again apologize for my
limited knowledge of vba, but I'm not sure where I would input the column
references in your code. Currently the invoice id is in col a and the descr
is in col g.

Thank you so much for your assistance.
Cathy

"SeanC UK" wrote:

Hi again Cathy,

You could do this in the cells a few different ways, the easiest being
having Col D using a formula like (say in D2, assuming row 1 has headings):

=IF(A2=A1,0,1)

This would give you a 1 for the first row of each set, 0s elsewhere. Then
you could hide this column and use colum E to say:

=IF(D2=1, C2, "")

And hiding Col C would give you Col E showing just the first row strings. To
make the concatenated string appear in the first row of each set you would
need to amend the formula I gave for Col C to read:

=IF(A2=A3,B2 & ";" & C3, B2)

There are other ways so as not to have hidden columns, using Conditional
Formatting so that the font colour in cells that are not the first row of the
group become invisible, plus many other methods I expect, but they could
become more complicated.

Personally, if you are happy with writing a little VBA code then you could
use the following, amending any column/row references to suit:

Public Sub Concat()
Const intIDCol As Integer = 1
Const intItemCodeCol As Integer = 2
Const intConcatCol As Integer = 3
Const lngFirstRow As Long = 27
Dim lngRowCounter As Long
Dim strConcatResult As String
Dim lngResultRow As Long
strConcatResult = Cells(lngFirstRow, intItemCodeCol)
lngResultRow = lngFirstRow
For lngRowCounter = lngFirstRow To Cells(lngFirstRow, _
intIDCol).CurrentRegion.Rows.Count + lngFirstRow - 1
If Cells(lngRowCounter, intIDCol) = Cells(lngRowCounter + 1,
intIDCol) _ Then
strConcatResult = strConcatResult & "; " & Cells(lngRowCounter +
1, _ intItemCodeCol)
Else
Cells(lngResultRow, intConcatCol) = strConcatResult
lngResultRow = lngRowCounter + 1
strConcatResult = Cells(lngRowCounter + 1, intItemCodeCol)
End If
Next
End Sub

Three lines of the code above have wrapped over, so I've place the _
character at the end, in case you wish to copy this.

Hope this helps (more than last time),

Sean.
--
(please remember to click yes if replies you receive are helpful to you)


"Cathy Landry" wrote:

Hi Sean,

Thank you for your quick response!

That's sort of working, but ideally C2 would have all the descriptions and
then the subsequent rows for that invoice id would be null.