Automatically generate/separate duplicate value in cell.

rudzkien

New Member
Joined
Feb 21, 2020
Messages
5
Office Version
2010
Platform
Windows, Mobile
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.
Duplicate - Excel.jpg
 

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
11,830
Office Version
2007
Platform
Windows
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
ABCDEF
1No1stQtyDuplicateQty
21Monitor
1
Phone
3
32Key
2
CPU
2
43Mouse
1
Key
2
54System
1
 
 
65Motherboard
1
 
 
76CPU
2
 
 
87Phone
3
 
 
98Router
1
 
 
109Lan cable
1
 
 
1110Key
1
 
 
1211Pen
1
 
 
1312Paper
1
 
 
1413Coffe
1
 
 
1514CPU
1
 
 
1615Cable
1
 
 
1716Printer
1
 
 
1817Phone
2
 
 
1918Mug
1
 
 
2019Phone
1
 
 
21
22
Hoja1
Cell Formulas
RangeFormula
E2:E20E2=IFERROR(INDEX($B$2:$B$200,LARGE(IF($C$2:$C$200=2,ROW($C$2:$C$200)-1),ROWS($E$1:E1))),"")
F2:F20F2=IFERROR(VLOOKUP(E2,$B$2:$C$200,2,0),"")
C2:C20C2=COUNTIF(B2:B$200,B2)
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.
 

rudzkien

New Member
Joined
Feb 21, 2020
Messages
5
Office Version
2010
Platform
Windows, Mobile
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!!!.:geek:

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

New Member
Joined
Feb 21, 2020
Messages
5
Office Version
2010
Platform
Windows, Mobile
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?
Duplicate List w Batch 2 & 3.PNG
 

Watch MrExcel Video

Forum statistics

Threads
1,099,684
Messages
5,470,116
Members
406,681
Latest member
sachinmasurkar

This Week's Hot Topics

Top