Automatically generate/separate duplicate value in cell.

[rudzkien]

Hi guys,

I need your help. I want to separate automatically the duplicate names in my list while typing them. I want to include also the quantity of duplicate names. The image "2" is only a sample which I would like to happen.

 

[DanteAmor]

Hi and welcome to MrExcel.

If you change the formula in column C, you can use the following:

Note: The formula in column E is an Array formula.

Dante Amor
	A	B	C	D	E	F
1	No	1st	Qty		Duplicate	Qty
2	1	Monitor	1		Phone	3
3	2	Key	2		CPU	2
4	3	Mouse	1		Key	2
5	4	System	1
6	5	Motherboard	1
7	6	CPU	2
8	7	Phone	3
9	8	Router	1
10	9	Lan cable	1
11	10	Key	1
12	11	Pen	1
13	12	Paper	1
14	13	Coffe	1
15	14	CPU	1
16	15	Cable	1
17	16	Printer	1
18	17	Phone	2
19	18	Mug	1
20	19	Phone	1
21
22
Hoja1

Cell Formulas
Range	Formula
E2:E20	E2	=IFERROR(INDEX($B$2:$B$200,LARGE(IF($C$2:$C$200=2,ROW($C$2:$C$200)-1),ROWS($E$1:E1))),"")
F2:F20	F2	=IFERROR(VLOOKUP(E2,$B$2:$C$200,2,0),"")
C2:C20	C2	=COUNTIF(B2:B$200,B2)
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

 

[rudzkien]

Wow! Thank you so much. There's some small changes like the quantity(QTY) of the list but I guessed that's the only way to do this thing. This is very helpful. THANK YOU!!!.

P.S. I would like to add additional question: What if I need to add 2 to 3 columns on the list, like I have Batch 1 and I want to add second and third column on the list for Batch 2 and 3. (I'm not sure if I need to make another topic for this)

 

[rudzkien]

Hi @DanteAmor ,

This was the one I was referring (please see attached photo). I want to add 2nd Batch and 3 Batch list. But the Mouse and Monitor(Batch 2) are not included in Duplicate List.

How can we include Batch 2 and Batch 3 List in Duplicate List?