Countif/Concatenate

  • Thread starter Thread starter Cathy Landry
  • Start date Start date
C

Cathy Landry

Hello,

Is there a way to concatenate all descriptions in Column B for each unique
invoice id?

ColA ColB ColC
Inv Id Description Concat Descr
7700490 descr100 descr100;descr200;descr300;descr400
7700490 descr200
7700490 descr300
7700490 descr400
7723309 descr10 descr10
7723310 descr20 descr20
7814245 descr30 descr30;descr40;descr50
7814245 descr40
7814245 descr50

Thank you!
Cathy
 
Hi Cathy,

I presume the example you gave is how you wish the result to look, and that
you just want simple cell formulae to produce this in column C?

If so then:

I would firstly sort the data by invoice ID to ensure that all similar IDs
are grouped together. Then, in the first row of data, C1 say, write:

=B1

In subsequent rows use, so C2 is:

=IF(A2=A1,C1 & "; " & B2, B2)

If I misunderstood then let me know and I'll try again :)

Cheers,

Sean.
 
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 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.
 
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 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.
 
Hi Sean,

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

Thank you sooooo much.
Cathy












- Mostra testo citato -
Click to expand...

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
 
Back
Top