Put values from column in one cell seperated by comma

[MrPowerQuery]

Hello there everyone,

i have a table that looks like this:

[TABLE="width: 457"]
<tbody>[TR]
[TD]CUSTOMER[/TD]
[TD]CUSTOMSUP[/TD]
[TD]Art.No.[/TD]
[TD]PAGE[/TD]
[/TR]
[TR]
[TD]Kunde A[/TD]
[TD]Ansprechpartner A[/TD]
[TD="align: right"]1000001[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Kunde A[/TD]
[TD]Ansprechpartner A[/TD]
[TD="align: right"]1000001[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Kunde A[/TD]
[TD]Ansprechpartner A[/TD]
[TD="align: right"]1000001[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]Kunde B[/TD]
[TD]Ansprechpartner B[/TD]
[TD="align: right"]1000002[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]Kunde B[/TD]
[TD]Ansprechpartner B[/TD]
[TD="align: right"]1000002[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]Kunde B[/TD]
[TD]Ansprechpartner B[/TD]
[TD="align: right"]1000002[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]Kunde B[/TD]
[TD]Ansprechpartner B[/TD]
[TD="align: right"]1000002[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]Kunde B[/TD]
[TD]Ansprechpartner B[/TD]
[TD="align: right"]1000002[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]Kunde B[/TD]
[TD]Ansprechpartner B[/TD]
[TD="align: right"]1000002[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD]Kunde c[/TD]
[TD]Ansprechpartner c[/TD]
[TD="align: right"]1000003[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]Kunde c[/TD]
[TD]Ansprechpartner c[/TD]
[TD="align: right"]1000003[/TD]
[TD="align: right"]11[/TD]
[/TR]
[TR]
[TD]Kunde c[/TD]
[TD]Ansprechpartner c[/TD]
[TD="align: right"]1000003[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD]Kunde D[/TD]
[TD]Ansprechpartner D[/TD]
[TD="align: right"]1000004[/TD]
[TD="align: right"]13[/TD]
[/TR]
[TR]
[TD]Kunde D[/TD]
[TD]Ansprechpartner D[/TD]
[TD="align: right"]1000004[/TD]
[TD="align: right"]14[/TD]
[/TR]
[TR]
[TD]Kunde D[/TD]
[TD]Ansprechpartner D[/TD]
[TD="align: right"]1000004[/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD]Kunde D[/TD]
[TD]Ansprechpartner D[/TD]
[TD="align: right"]1000004[/TD]
[TD="align: right"]16[/TD]
[/TR]
</tbody>[/TABLE]

The task is to write a formula or vba makro that does the following trick:
If the value in column "Art.No" changes put the value of the column "PAGE" into a other cell seperated by a comma.

Like in this case "3, 9, 12".

I tried this with the formula =IF(C2=C3;"";D2) but i dont know how to put these results in one cell seperated by commas....

Iam very sure some of u guys know how to solve my problem.

Thanks in Advance for helping me out here.

Ben

 

[louisH]

Hi. I'm not a formula expert so while waiting you can use this macro that will do the trick :

Sub test()
    
    Dim c As Range
    Dim str As String

    For Each c In Range("C2:C" & Range("C" & Rows.Count).End(xlUp).Row)
        
        If c.Offset(1, 0).Value <> c.Value And c.Offset(1, 0).Value <> "" Then str = str & ", " & c.Offset(0, 1).Value
        
    Next c
    
    str = Right(str, Len(str) - 2)
    Range("E1").Value = str

End Sub

Result will be dislayed in Cell E1.
I am also curious to discover the formula solution.

 

[MrPowerQuery]

Hi louisH,

this worked perfectly. Thanks a lot!

The Formula solution is a quite complex one and i still have not figured out what it really does:
F2: =IF(C2=C3;F3;D2& ", "&F3)
G2: =LEFT(F2;LEN(F2)-2) This deletes the last 2 figures to clean the string up, because there are not needed.

Maybe u get it.