Automatic separate duplicate with two or three columns.

[rudzkien]

Hi Guys,

Can you please help me I would like to know how to separate automatically the same names with two or three columns. I manage to separate automatically the first column with the help of Mr. @DanteAmor (Thank you!). But how can I include Batch 2 and Batch 3 List in Duplicate List?

As you can see in the photo below. All same name in 1st Batch are in Duplicate list with quantity. But the 2nd batch was not included in Duplicate List.

Please help!

 

[DanteAmor]

For that example, with the following formulas:

varios 21feb2020.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	No	1st	Qty	No	2nd	Qty	No	3rd	Qty		Duplicate	Qty
2	1	Monitor	1	26	Laptop	1	51	PC	2		PC	2
3	2	Key	2	27	Charger	1	52	Cable	2		Key	2
4	3	Mouse	1	28	Mouse	2	53	Mouse	3		Cable	2
5	4	System	1	29	PC	1	54	CPU	4		Mouse	3
6	5	Motherboard	1	30	Monitor	2	55		0		Monitor	2
7	6	CPU	2	31	Flashdrive	2	56		0		CPU	4
8	7	Phone	3	32	CPU	3	57		0		Flashdrive	2
9	8	Router	1	33		0	58		0		Phone	3
10	9	Lan cable	1	34		0	59		0			0
11	10	Key	1	35		0	60		0			0
12	11	Pen	1	36		0	61		0			0
13	12	Paper	1	37		0	62		0			0
14	13	Coffe	1	38		0	63		0			0
15	14	CPU	1	39		0	64		0			0
16	15	Cable	1	40		0	65		0			0
17	16	Printer	1	41		0	66		0			0
18	17	Phone	2	42		0	67		0			0
19	18	Mug	1	43		0	68		0			0
20	19	Phone	1	44		0	69		0			0
21	20	HDD	1	45		0	70		0			0
22	21	Flashdrive	1	46		0	71		0			0
23	22		0	47		0	72		0			0
24	23		0	48		0	73		0			0
25	24		0	49		0	74		0			0
26	25		0	50		0	75		0			0
Hoja1

Cell Formulas
Range	Formula
K2:K26	K2	=IFERROR(INDIRECT(TEXT(SMALL(IF($B$2:$H$26<>"",IF($C$2:$I$26=2,ROW($B$2:$H$26)*1000+COLUMN($B$2:$H$26))),ROWS($A$2:$A2)),"R000C000"),0),"")
L2:L26	L2	=MAX(($B$2:$H$26=K2)*(IF(ISNUMBER($C$2:$I$26),$C$2:$I$26)))
C2:C26	C2	=COUNTIF(B2:B$26,B2)
F2:F26	F2	=COUNTIF($B$2:B$26,E2)+COUNTIF(E2:E$26,E2)
I2:I26	I2	=COUNTIF($B$2:B$26,H2)+COUNTIF($E$2:E$26,H2)+COUNTIF(H2:H$26,H2)
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

 

[rudzkien]

Great!!!.. Thank you so much!!! @DanteAmor

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.