Get data in different row and summarize in a single cell with comma

[kelvin0403]

Hi everyone,

I would like to have a formula to summarize up all the customer name in a single cell which the shipment date falls into the same date.
Table 2 is the result that I wish to get. There is a comma "," in between each customer name.

Kindly advise how should I set the formula. Pls help. Thank you

Table 1

SHIPMENT DATE	CUSTOMER	PRODUCTS	Quantity	Invoice No
01-07-2020	B H Polymers	1T	60	ZT-PFM20050038
01-07-2020	Blend Colours Pvt Ltd	2T	300	ZT-PFM20050052
01-07-2020	Blend Colours (Telangana)	2T	150	ZT-PFM20050058
02-07-2020	Blend Colours Kattedan	1T	52	ZT-PFM20060073
02-07-2020	Blend Colours Burgul	2TS	156	ZT-PFM20060073
03-07-2020	Dolphin Polyfill	2T	50	ZT-PFM20060020
04-07-2020	Jai Corp	2T	156	ZT-PFM20060007
04-07-2020	Pr Polymer	2T	150	ZT-PFM20060082
05-07-2020	Ras Polytex	2T	100	ZT-PFM20020155
05-07-2020	Jai Corp	2T	250	ZT-PFM20060089
05-07-2020	B H Polymers	1T	260	ZT-PFM20060052
05-07-2020	Blend Colours Pvt Ltd	2TS	390	ZT-PFM20060038

Table 2

Date	Customer
01-07-2020	B H Polymers, Blend Colours Pvt Ltd, Blend Colours (Telangana)
02-07-2020​	Blend Colours Kattedan, Blend Colours Burgul
03-07-2020​	Dolphin Polyfill
04-07-2020​	Jai Corp, Pr Polymer
05-07-2020​	Ras Polytex, Jai Corp, B H Polymers, Blend Colours Pvt Ltd

Kelvin

 

[alansidman]

Follow this tutorial

IMA Site Maintenance

Book6.xlsm
	A	B	C	D	E	F
1	SHIPMENT DATE	CUSTOMER	PRODUCTS	Quantity	Invoice No
2	1/7/2020	B H Polymers	1T	60	ZT-PFM20050038
3	1/7/2020	Blend Colours Pvt Ltd	2T	300	ZT-PFM20050052
4	1/7/2020	Blend Colours (Telangana)	2T	150	ZT-PFM20050058
5	2/7/2020	Blend Colours Kattedan	1T	52	ZT-PFM20060073
6	2/7/2020	Blend Colours Burgul	2TS	156	ZT-PFM20060073
7	3/7/2020	Dolphin Polyfill	2T	50	ZT-PFM20060020
8	4/7/2020	Jai Corp	2T	156	ZT-PFM20060007
9	4/7/2020	Pr Polymer	2T	150	ZT-PFM20060082
10	5/7/2020	Ras Polytex	2T	100	ZT-PFM20020155
11	5/7/2020	Jai Corp	2T	250	ZT-PFM20060089
12	5/7/2020	B H Polymers	1T	260	ZT-PFM20060052
13	5/7/2020	Blend Colours Pvt Ltd	2TS	390	ZT-PFM20060038
14
15	SHIPMENT DATE	CustomerList
16	1/7/2020	B H Polymers,Blend Colours Pvt Ltd,Blend Colours (Telangana)
17	2/7/2020	Blend Colours Kattedan,Blend Colours Burgul
18	3/7/2020	Dolphin Polyfill
19	4/7/2020	Jai Corp,Pr Polymer
20	5/7/2020	Ras Polytex,Jai Corp,B H Polymers,Blend Colours Pvt Ltd
21
Sheet3

 

[kelvin0403]

Thanks Alansidman. Pivot table is not suitable in my case. Is there any other way to get the same result?

 

[alansidman]

Possibly with Some VBA. Don't have time to do it now. Maybe someone else will come by before I have a chance.

 

[JoeMo]

If you have a version of Excel that supports TEXTJOIN and FILTER worksheet functions you can use the formula below. Otherwise, I'd be inclined to write a UDF.

Book1.xlsm
	A	B	C	D	E
1	SHIPMENT DATE	CUSTOMER	PRODUCTS	Quantity	Invoice No
2	1/7/2020	B H Polymers	1T	60	ZT-PFM20050038
3	1/7/2020	Blend Colours Pvt Ltd	2T	300	ZT-PFM20050052
4	1/7/2020	Blend Colours (Telangana)	2T	150	ZT-PFM20050058
5	2/7/2020	Blend Colours Kattedan	1T	52	ZT-PFM20060073
6	2/7/2020	Blend Colours Burgul	2TS	156	ZT-PFM20060073
7	3/7/2020	Dolphin Polyfill	2T	50	ZT-PFM20060020
8	4/7/2020	Jai Corp	2T	156	ZT-PFM20060007
9	4/7/2020	Pr Polymer	2T	150	ZT-PFM20060082
10	5/7/2020	Ras Polytex	2T	100	ZT-PFM20020155
11	5/7/2020	Jai Corp	2T	250	ZT-PFM20060089
12	5/7/2020	B H Polymers	1T	260	ZT-PFM20060052
13	5/7/2020	Blend Colours Pvt Ltd	2TS	390	ZT-PFM20060038
14
15
16	Date	Customer
17	1/7/2020	B H Polymers, Blend Colours Pvt Ltd, Blend Colours (Telangana)
18	2/7/2020	Blend Colours Kattedan, Blend Colours Burgul
19	3/7/2020	Dolphin Polyfill
20	4/7/2020	Jai Corp, Pr Polymer
21	5/7/2020	Ras Polytex, Jai Corp, B H Polymers, Blend Colours Pvt Ltd
Sheet7

Cell Formulas
Range	Formula
B17:B21	B17	=TEXTJOIN(", ",TRUE,FILTER(B2:B13,A2:A13=A17))

 

[alansidman]

Option Explicit

Sub NonPT()
    Dim s1 As Worksheet, s3 As Worksheet
    Set s1 = Sheets("Sheet1")
    Set s3 = Sheets("Sheet3")
    'Change the sheet names to your tabs
    Dim i As Long, lr As Long, last As Long, j As Long
    lr = s3.Range("A" & Rows.Count).End(xlUp).Row

    s3.Range("A2:B2").Copy
    s1.Range("A1").PasteSpecial xlPasteValuesAndNumberFormats
    For i = 3 To lr
        If s3.Range("A" & i) = s3.Range("A" & i - 1) Then
            last = s1.Range("A" & Rows.Count).End(xlUp).Row
            s1.Range("B" & last) = s1.Range("B" & last) & ", " & s3.Range("B" & i)
        Else: s3.Range("A" & i & ":B" & i).Copy
            s1.Range("A" & last + 1).PasteSpecial xlPasteValuesAndNumberFormats
        End If
    Next i
    MsgBox "complete"
End Sub

Book6.xlsm
	A	B
1	1/7/2020	B H Polymers, Blend Colours Pvt Ltd, Blend Colours (Telangana)
2	2/7/2020	Blend Colours Kattedan, Blend Colours Burgul
3	4/7/2020	Jai Corp, Pr Polymer
4	5/7/2020	Ras Polytex, Jai Corp, B H Polymers, Blend Colours Pvt Ltd
Sheet1