macro for 4000 rows (merge & count duplicates items )

[KalilMe]

hi experts

I need macro run for about 4000 rows . so this is the simple data to understand me . it should merge duplicate item based on COL D and with merge duplicate items in COL B,C should contain comma and count the duplicate item based on COL D with summing QTY so the result should show from COL J: O with the same formatting and borders

CONC.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	DATE	INVOICE	ORDER	CODE	BRAND	TYPE	ORIGIN	QTY		ITEM	INVOICE	ORDER	CODE	COUNT	QTY
2	1/1/2021	FRT-100/G-1	OR-FR/1000	FR-00	BANAN	SO	TR	12		1	FRT-100/G-1,2	OR-FR/1000,1	FR-00	6	70
3	1/2/2021	FRT-100/G-1	OR-FR/1000	FR-00	BANAN	MA	TR	13		2	FRT-100/G-7,9	OR-FR/1002,4	FR-001	2	10
4	1/3/2021	FRT-100/G-2	OR-FR/1001	FR-00	BANAN	SS	TUT	10		3	FRT-100/G-8,10	OR-FR/1003,5	FR-002	2	40
5	1/4/2021	FRT-100/G-2	OR-FR/1001	FR-00	BANAN	SO	TR	12
6	1/5/2021	FRT-100/G-2	OR-FR/1001	FR-00	BANAN	MA	TR	13
7	1/6/2021	FRT-100/G-2	OR-FR/1001	FR-00	BANAN	SS	TUT	10
8	1/7/2021	FRT-100/G-7	OR-FR/1002	FR-01	ORANG	MM	NNT	5
9	1/8/2021	FRT-100/G-8	OR-FR/1003	FR-02	ORANG	ASD	KI	20
10	1/9/2021	FRT-100/G-9	OR-FR/1004	FR-01	ORANG	MM	NNT	5
11	1/10/2021	FRT-100/G-10	OR-FR/1005	FR-02	ORANG	ASD	KI	20
12
13
sss

 

[alansidman]

With Power Pivot, create these two measures shown in the attachments

Book2
	A	B	C	D	E
3	CODE	Invoices	Orders	Count of INVOICE	Sum of QTY
4	FR-00	FRT-100/G-1, FRT-100/G-1, FRT-100/G-2, FRT-100/G-2, FRT-100/G-2, FRT-100/G-2	OR-FR/1000, OR-FR/1000, OR-FR/1001, OR-FR/1001, OR-FR/1001, OR-FR/1001	6	70
5	FR-01	FRT-100/G-7, FRT-100/G-9	OR-FR/1002, OR-FR/1004	2	10
6	FR-02	FRT-100/G-8, FRT-100/G-10	OR-FR/1003, OR-FR/1005	2	40
Sheet2

to see a tutorial on how I achieved this is at: Excel: Reporting Text in a Pivot Table - Strategic Finance

 

[Sanjeev1976]

Formula based

 

[Sanjeev1976]

Formula based

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	DATE	INVOICE	ORDER	CODE	BRAND	TYPE	ORIGIN	QTY		ITEM	INVOICE	ORDER	CODE	COUNT	QTY
2	01-01-21	FRT-100/G-1	OR-FR/1000	FR-00	BANAN	SO	TR	12		1	FRT-100/G-1, FRT-100/G-2	OR-FR/1000, OR-FR/1001	FR-00	6	70
3	01-02-21	FRT-100/G-1	OR-FR/1000	FR-00	BANAN	MA	TR	13		2	FRT-100/G-7, FRT-100/G-9	OR-FR/1002, OR-FR/1004	FR-01	2	10
4	01-03-21	FRT-100/G-2	OR-FR/1001	FR-00	BANAN	SS	TUT	10		3	FRT-100/G-8, FRT-100/G-10	OR-FR/1003, OR-FR/1005	FR-02	2	40
5	01-04-21	FRT-100/G-2	OR-FR/1001	FR-00	BANAN	SO	TR	12
6	01-05-21	FRT-100/G-2	OR-FR/1001	FR-00	BANAN	MA	TR	13
7	01-06-21	FRT-100/G-2	OR-FR/1001	FR-00	BANAN	SS	TUT	10
8	01-07-21	FRT-100/G-7	OR-FR/1002	FR-01	ORANG	MM	NNT	5
9	01-08-21	FRT-100/G-8	OR-FR/1003	FR-02	ORANG	ASD	KI	20
10	01-09-21	FRT-100/G-9	OR-FR/1004	FR-01	ORANG	MM	NNT	5
11	01-10-21	FRT-100/G-10	OR-FR/1005	FR-02	ORANG	ASD	KI	20
Sheet1

Cell Formulas
Range	Formula
K2:K4	K2	=TEXTJOIN(", ",,UNIQUE(FILTER($B$2:$B$4001,$D$2:$D$4001=M2)))
L2:L4	L2	=TEXTJOIN(", ",,UNIQUE(FILTER($C$2:$C$4001,$D$2:$D$4001=M2)))
M2:M4	M2	=SORT(UNIQUE(FILTER($D$2:$D$4001,($D$2:$D$4001<>""))))
N2:N4	N2	=COUNTIF($D$2:$D$4001,M2)
O2:O4	O2	=SUMIF($D$2:$D$4001,M2,$H$2:$H$4001)
J3:J4	J3	=J2+1
Dynamic array formulas.

 

[Fluff]

@Sanjeev1976 maybe you didn't check the OP's profile, but it shows 2016, so cannot use the formulae in cols K, L & M

 

[Sanjeev1976]

Hi Fluff,
Yes, missed checking the profile, will check the same in future before responding

 

[KalilMe]

thanks guys , if that's possible by vba will be great