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.

Hi Cathy,

No problem.

The column references are the four lines where I've defined variables using
Const. This makes the variable a constant value (although they are still
referred to as variables!) As column numbers in Excel are within the
limitations of an integer data type, I have declared them as integers, and
prefixed their names with int (and lng for Long data type for the row
variable for the first row of data).

Therefore, the invoice ID column I have is intIDCol, which I gave the value
of 1, which is obviously fine for you, and the description column, whish I
named intItemCodeCol, should be set to 7 for you. I have referred to the
concatenated string column as intConcatCol, and set it to 3, which I expect
you will wish to change!

If you have any problems let me know, especially if copying the code, I know
that the line spacing does not always show well, so if necessary I can space
out individual lines with an empty line between. I generally use Firefox and
I know that the line spacing doesn't appear correctly to me!

Good luck,

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


"Cathy Landry" wrote:

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

Hi Sean,

This works beautifully......you Rock!!

Thank you sooooo much.
Cathy

"SeanC UK" wrote:

Hi Cathy,

No problem.

The column references are the four lines where I've defined variables using
Const. This makes the variable a constant value (although they are still
referred to as variables!) As column numbers in Excel are within the
limitations of an integer data type, I have declared them as integers, and
prefixed their names with int (and lng for Long data type for the row
variable for the first row of data).

Therefore, the invoice ID column I have is intIDCol, which I gave the value
of 1, which is obviously fine for you, and the description column, whish I
named intItemCodeCol, should be set to 7 for you. I have referred to the
concatenated string column as intConcatCol, and set it to 3, which I expect
you will wish to change!

If you have any problems let me know, especially if copying the code, I know
that the line spacing does not always show well, so if necessary I can space
out individual lines with an empty line between. I generally use Firefox and
I know that the line spacing doesn't appear correctly to me!

Good luck,

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


"Cathy Landry" wrote:

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

most welcome :)
--
(please remember to click yes if replies you receive are helpful to you)


"Cathy Landry" wrote:

Hi Sean,

This works beautifully......you Rock!!

Thank you sooooo much.
Cathy

"SeanC UK" wrote:

Hi Cathy,

No problem.

The column references are the four lines where I've defined variables using
Const. This makes the variable a constant value (although they are still
referred to as variables!) As column numbers in Excel are within the
limitations of an integer data type, I have declared them as integers, and
prefixed their names with int (and lng for Long data type for the row
variable for the first row of data).

Therefore, the invoice ID column I have is intIDCol, which I gave the value
of 1, which is obviously fine for you, and the description column, whish I
named intItemCodeCol, should be set to 7 for you. I have referred to the
concatenated string column as intConcatCol, and set it to 3, which I expect
you will wish to change!

If you have any problems let me know, especially if copying the code, I know
that the line spacing does not always show well, so if necessary I can space
out individual lines with an empty line between. I generally use Firefox and
I know that the line spacing doesn't appear correctly to me!

Good luck,

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


"Cathy Landry" wrote:

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

On 4 Feb, 17:02, Cathy Landry
wrote:
Hi Sean,

This works beautifully......you Rock!!

Thank you sooooo much.
Cathy



"SeanC UK" wrote:
Hi Cathy,

No problem.

The column references are the four lines where I've defined variables using
Const. This makes the variable a constant value (although they are still
referred to as variables!) As column numbers in Excel are within the
limitations of an integer data type, I have declared them as integers, and
prefixed their names with int (and lng for Long data type for the row
variable for the first row of data).

Therefore, the invoice ID column I have is intIDCol, which I gave the value
of 1, which is obviously fine for you, and the description column, whish I
named intItemCodeCol, should be set to 7 for you. I have referred to the
concatenated string column as intConcatCol, and set it to 3, which I expect
you will wish to change!

If you have any problems let me know, especially if copying the code, I know
that the line spacing does not always show well, so if necessary I can space
out individual lines with an empty line between. I generally use Firefox and
I know that the line spacing doesn't appear correctly to me!

Good luck,

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

"Cathy Landry" wrote:

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- Nascondi testo citato

- Mostra testo citato -

Hi Cathy.
After the good Sean solution, try also this UDF:

Function FindNth(Valore As Variant, tabella As Range, _
col As Integer)
Dim i As Long
Dim trova As String
For i = 1 To tabella.Rows.Count
If tabella.Cells(i, 1) = Valore Then
trova = trova & " " & tabella.Cells(i, col)
End If
Next i
FindNth = trova
End Function

''According to your example:
'In C2: =IF(A2=A1;"";FindNth(A2,A2:B10,2))
' and copy down up to C10

Regards
Eliano