combining duplicates

[Novman]

Combining Dupalitcks.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
2		size	Colour	PO	Pack	mm	code	Amount			Colour	PO	Pack	mm	code	Amount
3		h	yellow	12458	yes		abc	10		h		51476				42
4		h	blue	12541	no			2		f		95441				111
5		h	blue	12541	no			5
6		f	yellow green	12455	yes			4			Colour	PO	Pack	mm	code	Amount
7		f	black	45551	yes		abc	87		h		51476				42
8		f	yellow	12490	yes		abc	6		f		95441				111
9		h	black	1478	no			5		yellow		49896				42
10		h	yellow	12458	yes		abc	20		blue		25082				7
11		f	yellow green	12455	yes			8		yellow green		24910				12
12		f	yellow	12490	yes		abc	6		black		47029				92
13
14		What I am looking to do is combined my duplicates by looking through
15		3 columns hi-lighted above and adding them together if all 3 criteria's match
16		and adding the values in last column combining occurs
17
18		This is what I would like it to loo like, the shading is only for demonstration.
19		size	Colour	PO	Pack	mm	code	Amount
20		h	yellow	12458	yes		abc	30
21		h	blue	12541	no			7
22		f	yellow green	12455	yes			12
23		f	black	45551	yes		abc	87
24		f	yellow	12490	yes		abc	6
25		h	black	1478	no			5
26		f	yellow	12490	yes		abc	6
27
Sheet2

 

[ISY]

Hi

=LET(X,UNIQUE(B2:G11,FALSE),HSTACK(X,BYROW(X,LAMBDA(W,SUM(FILTER(H2:H11,(INDEX(W,1,1)=B2:B11)*(INDEX(W,1,2)=C2:C11)*(INDEX(W,1,3)=D2:D11)))))))

 

[Novman]

Hi

=LET(X,UNIQUE(B2:G11,FALSE),HSTACK(X,BYROW(X,LAMBDA(W,SUM(FILTER(H2:H11,(INDEX(W,1,1)=B2:B11)*(INDEX(W,1,2)=C2:C11)*(INDEX(W,1,3)=D2:D11)))))))

Hi, Thank you for your reply, Done a quick check to try out your formula and it gives back what I'm looking for, before I go ahead and try it on my bigger sheet I just want to ask, iv used (Data) Filter then (Data Tools) then Consolidate, can I get same results using that method or is it best to use the formula.

 

[Novman]

Sorry for the wait, the fourmla you gave me does work on the table i sent, my mistake as i thought all i had to do wes eidit the fourmla but been trying but cant get it, ill past a new table if you could eidet your fourmla to give me the same results as the one ou first posted.

 

[Novman]

MrExccel Help.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
2		size	Colour	PO	Pack	mm	code	Amount		size	Colour	PO	Pack	mm	code	0
3		h	yellow	12458	yes		abc	10		h	yellow	12458	yes	0	abc	30
4		h	blue	12541	no			2		h	blue	12541	no	0	0	7
5		h	blue	12541	no			5		f	yellow green	12455	yes	0	0	12
6		f	yellow green	12455	yes			4		f	black	45551	yes	0	abc	87
7		f	black	45551	yes		abc	87		f	yellow	12490	yes	0	abc	12
8		f	yellow	12490	yes		abc	6		h	black	1478	no	0	0	5
9		h	black	1478	no			5
10		h	yellow	12458	yes		abc	20
11		f	yellow green	12455	yes			8
12		f	yellow	12490	yes		abc	6
13
14				use tabe below to return the table above with blue tick
15		Order	In Stock	H/F	Colour	PO	Pack	Value	day 1	day 2	day 3	day 4	day 5	day 6	day 7	stock	needed for order	oreder sent	Final Count
16
17		1	YES	h	yellow	12458	no	100	1	3	4	6	7	2	9				32
18		2	NO	h	blue	12541	yes	90	4	25	6	8	45	4	2				94
19		3	NO	h	blue	12541	no	50	2	0	0	0	0	0	0				2
20		4	NO	f	yellow green	12455	no	70	5	2	48	6	7	2	9				79
21		5	YES	f	black	45551	yes	22	89	112	9	8	45	4	2				269
22		6	YES	f	yellow	12490	yes	37	3	2	6	0	0	0	0				11
23		7	YES	h	black	1478	yes	12	2	2	4	0	0	0	0				8
24		8	YES	h	yellow	12458	no	17	1	2	55	1	1	1	1				62
25		9	NO	f	yellow green	12455	yes	8	45	3	78	1	3	4	100				234
26		10	NO	f	yellow	12490	yes	10	2	85	15	4	25	6	15				152
27		11	YES	f	yellow	12490	yes	55	2	2	10	2	0	0	55				71
28		12	YES					96	7	2	15	5	45	25	65				164
29																			1178
Sheet2

Cell Formulas
Range	Formula
J2:P8	J2	=LET(X,UNIQUE(B2:G12,FALSE),HSTACK(X,BYROW(X,LAMBDA(W,SUM(FILTER(H2:H12,(INDEX(W,1,1)=B2:B12)*(INDEX(W,1,2)=C2:C12)*(INDEX(W,1,3)=D2:D12)))))))
S17:S28	S17	=SUM(I17:O17)
S29	S29	=SUM(S17:S28)
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
_FilterDatabase	=Sheet2!$B$2:$H$12	J2

 

[Novman]

MrExccel Help.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
2	size	Colour	PO	Pack	mm	code	Amount		Table on the right is the result of the combine table from the left using a formula			size	Colour	PO	Pack	mm	code	0
3	h	yellow	12458	yes		ABC	10					h	yellow	12458	yes	0	ABC	30
4	h	blue	12541	no			2					h	blue	12541	no	0	0	7
5	h	blue	12541	no			5					f	green	12455	yes	0	0	12
6	f	green	12455	yes			4					f	black	45551	yes	0	ABC	87
7	f	black	45551	yes		ABC	87					f	yellow	12490	yes	0	ABC	12
8	f	yellow	12490	yes		ABC	6					h	black	1478	no	0	0	5
9	h	black	1478	no			5
10	h	yellow	12458	yes		ABC	20		"=LET(X,UNIQUE(B2:G12,FALSE),HSTACK(X,BYROW(X,LAMBDA(W,SUM(FILTER(H2:H12,(INDEX(W,1,1)=B2:B12)*(INDEX(W,1,2)=C2:C12)*(INDEX(W,1,3)=D2:D12)))))))
11	f	green	12455	yes			8
12	f	yellow	12490	yes		ABC	6
13
14
15	I'm looking for a formula to combined the debitages in table below and add its counts, what I would like it to return is below this table
16	Order	In Stock	H/F	Colour	PO	Pack	Value	day 1	day 2	day 3	day 4	day 5	day 6	day 7	stock	needed for order	order sent	Final Count
17
18	1	YES	h	yellow	12458	no	100	1	3	4	6	7	2	9				12
19	2	NO	h	blue	12541	yes	90	4	25	6	8	45	4	2				10
20	3	NO	h	blue	12541	no	50	2	0	0	0	0	0	0				55
21	4	NO	f	green	12455	no	70	5	2	48	6	7	2	9				12
22	5	YES	f	black	45551	yes	22	89	112	9	8	45	4	2				78
23	6	YES	f	yellow	12490	yes	37	3	2	6	0	0	0	0				8
24	7	YES	h	black	1478	yes	12	2	2	4	0	0	0	0				52
25	8	YES	h	yellow	12458	no	17	1	2	55	1	1	1	1				30
26	9	NO	f	green	12455	yes	8	45	3	78	1	3	4	100				241
27	10	NO	f	yellow	12490	yes	10	2	85	15	4	25	6	15				115
28	11	YES	f	yellow	12490	yes	55	2	2	10	2	0	0	55				55
29	12	YES					96	7	2	15	5	45	25	65				11
30	the green shaded columns are the columns id like returned and combined if they are duplicates, wile adding the blue shaded columns figures when combining
31	the table at top of page has a formula doing as I need it to but is a smaller table with less columns																	679
32	H/F	Colour	PO	Final Count
33
34	h	yellow	12458	42	this is what I want to have returned if posable
35	h	blue	12541	65
36	f	green	12455	253
37	f	black	45551	78
38	f	yellow	12490	178
39	h	black	1478	52
Sheet2

Cell Formulas
Range	Formula
M2:S8	M2	=LET(X,UNIQUE(B2:G12,FALSE),HSTACK(X,BYROW(X,LAMBDA(W,SUM(FILTER(H2:H12,(INDEX(W,1,1)=B2:B12)*(INDEX(W,1,2)=C2:C12)*(INDEX(W,1,3)=D2:D12)))))))
S31	S31	=SUM(S18:S29)
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
_FilterDatabase	=Sheet2!$B$2:$H$12	M2

 

[Novman]

MrExccel Help.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
2	size	Colour	PO	Pack	mm	code	Amount		Table on the right is the result of the combine table from the left using a formula			size	Colour	PO	Pack	mm	code	0
3	h	yellow	12458	yes		ABC	10					h	yellow	12458	yes	0	ABC	30
4	h	blue	12541	no			2					h	blue	12541	no	0	0	7
5	h	blue	12541	no			5					f	green	12455	yes	0	0	12
6	f	green	12455	yes			4					f	black	45551	yes	0	ABC	87
7	f	black	45551	yes		ABC	87					f	yellow	12490	yes	0	ABC	12
8	f	yellow	12490	yes		ABC	6					h	black	1478	no	0	0	5
9	h	black	1478	no			5
10	h	yellow	12458	yes		ABC	20		"=LET(X,UNIQUE(B2:G12,FALSE),HSTACK(X,BYROW(X,LAMBDA(W,SUM(FILTER(H2:H12,(INDEX(W,1,1)=B2:B12)*(INDEX(W,1,2)=C2:C12)*(INDEX(W,1,3)=D2:D12)))))))
11	f	green	12455	yes			8
12	f	yellow	12490	yes		ABC	6
13
14
15	I'm looking for a formula to combined the debitages in table below and add its counts, what I would like it to return is below this table
16	Order	In Stock	H/F	Colour	PO	Pack	Value	day 1	day 2	day 3	day 4	day 5	day 6	day 7	stock	needed for order	order sent	Final Count
17
18	1	YES	h	yellow	12458	no	100	1	3	4	6	7	2	9				12
19	2	NO	h	blue	12541	yes	90	4	25	6	8	45	4	2				10
20	3	NO	h	blue	12541	no	50	2	0	0	0	0	0	0				55
21	4	NO	f	green	12455	no	70	5	2	48	6	7	2	9				12
22	5	YES	f	black	45551	yes	22	89	112	9	8	45	4	2				78
23	6	YES	f	yellow	12490	yes	37	3	2	6	0	0	0	0				8
24	7	YES	h	black	1478	yes	12	2	2	4	0	0	0	0				52
25	8	YES	h	yellow	12458	no	17	1	2	55	1	1	1	1				30
26	9	NO	f	green	12455	yes	8	45	3	78	1	3	4	100				241
27	10	NO	f	yellow	12490	yes	10	2	85	15	4	25	6	15				115
28	11	YES	f	yellow	12490	yes	55	2	2	10	2	0	0	55				55
29	12	YES					96	7	2	15	5	45	25	65				11
30	the green shaded columns are the columns id like returned and combined if they are duplicates, wile adding the blue shaded columns figures when combining
31	the table at top of page has a formula doing as I need it to but is a smaller table with less columns																	679
32	H/F	Colour	PO	Final Count
33
34	h	yellow	12458	42	this is what I want to have returned if posable
35	h	blue	12541	65
36	f	green	12455	253
37	f	black	45551	78
38	f	yellow	12490	178
39	h	black	1478	52
Sheet2

Cell Formulas
Range	Formula
M2:S8	M2	=LET(X,UNIQUE(B2:G12,FALSE),HSTACK(X,BYROW(X,LAMBDA(W,SUM(FILTER(H2:H12,(INDEX(W,1,1)=B2:B12)*(INDEX(W,1,2)=C2:C12)*(INDEX(W,1,3)=D2:D12)))))))
S31	S31	=SUM(S18:S29)
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
_FilterDatabase	=Sheet2!$B$2:$H$12	M2

I hope this will help a bit more