Put values from column in one cell seperated by comma

[MrPowerQuery]

Hello there everyone,

i have a table that looks like this:

CUSTOMER	CUSTOMSUP	Art.No.	PAGE
Kunde A	Ansprechpartner A	1000001	1
Kunde A	Ansprechpartner A	1000001	2
Kunde A	Ansprechpartner A	1000001	3
Kunde B	Ansprechpartner B	1000002	4
Kunde B	Ansprechpartner B	1000002	5
Kunde B	Ansprechpartner B	1000002	6
Kunde B	Ansprechpartner B	1000002	7
Kunde B	Ansprechpartner B	1000002	8
Kunde B	Ansprechpartner B	1000002	9
Kunde c	Ansprechpartner c	1000003	10
Kunde c	Ansprechpartner c	1000003	11
Kunde c	Ansprechpartner c	1000003	12
Kunde D	Ansprechpartner D	1000004	13
Kunde D	Ansprechpartner D	1000004	14
Kunde D	Ansprechpartner D	1000004	15
Kunde D	Ansprechpartner D	1000004	16

<tbody>
</tbody>

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.