Counting Duplicates

[bhieatza]

Okay this should be an easy one. I need to make a function or macro that counts duplicates. The last three characters are numbers. So if it comes across:
Milk_001
Milk_001
It would be changed to
Milk_001
Milk_002
The problem i am running into is when I get to the 10th duplicate it reads:
Milk_0010 instead of Milk_010.
Thank you for the help!

 

[Kenneth Hobson]

What are you changing, cells, arrays, etc.?

In VBA I would probably use Split() and Format(x, "00#") along with this sort of thing for cells. http://www.mrexcel.com/forum/excel-...st-duplicate-value-rename-new-text-range.html

 

[Oeldere]

Book1
	A	B	C
135	Milk_001	Milk_	Milk_001
136	Milk_001	Milk_	Milk_002
137	Milk_001	Milk_	Milk_003
138	Milk_001	Milk_	Milk_004
139	Milk_001	Milk_	Milk_005
140	Milk_001	Milk_	Milk_006
141	Milk_001	Milk_	Milk_007
142	Milk_001	Milk_	Milk_008
143	Milk_001	Milk_	Milk_009
144	Milk_001	Milk_	Milk_010
145	Milk_001	Milk_	Milk_011
Blad1

Cell Formulas
Range	Formula
B135	=LEFT(A135,LEN(A135)-3)
C135	=B135&TEXT(COUNTIF($B$135:$B135,$B135),"000")

 

[bhieatza]

	A	B	C
Milk_001
Milk_001
Milk_001
Milk_001
Milk_001
Milk_001
Milk_001
Milk_001
Milk_001
Milk_001
Milk_001

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]135[/TD]

[TD="bgcolor: #FFFF00"]Milk_[/TD]
[TD="bgcolor: #92D050"]Milk_001[/TD]

[TD="align: center"]136[/TD]

[TD="bgcolor: #FFFF00"]Milk_[/TD]
[TD="bgcolor: #92D050"]Milk_002[/TD]

[TD="align: center"]137[/TD]

[TD="bgcolor: #FFFF00"]Milk_[/TD]
[TD="bgcolor: #92D050"]Milk_003[/TD]

[TD="align: center"]138[/TD]

[TD="bgcolor: #FFFF00"]Milk_[/TD]
[TD="bgcolor: #92D050"]Milk_004[/TD]

[TD="align: center"]139[/TD]

[TD="bgcolor: #FFFF00"]Milk_[/TD]
[TD="bgcolor: #92D050"]Milk_005[/TD]

[TD="align: center"]140[/TD]

[TD="bgcolor: #FFFF00"]Milk_[/TD]
[TD="bgcolor: #92D050"]Milk_006[/TD]

[TD="align: center"]141[/TD]

[TD="bgcolor: #FFFF00"]Milk_[/TD]
[TD="bgcolor: #92D050"]Milk_007[/TD]

[TD="align: center"]142[/TD]

[TD="bgcolor: #FFFF00"]Milk_[/TD]
[TD="bgcolor: #92D050"]Milk_008[/TD]

[TD="align: center"]143[/TD]

[TD="bgcolor: #FFFF00"]Milk_[/TD]
[TD="bgcolor: #92D050"]Milk_009[/TD]

[TD="align: center"]144[/TD]

[TD="bgcolor: #FFFF00"]Milk_[/TD]
[TD="bgcolor: #92D050"]Milk_010[/TD]

[TD="align: center"]145[/TD]

[TD="bgcolor: #FFFF00"]Milk_[/TD]
[TD="bgcolor: #92D050"]Milk_011[/TD]

</tbody>

Blad1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B135[/TH]
[TD="align: left"]=LEFT(A135,LEN(A135)-3)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C135[/TH]
[TD="align: left"]=B135&TEXT(COUNTIF($B$135:$B135,$B135),"000")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Wow. I am upset I did not think of that haha. Thanks for your help.

 

[Oeldere]

You're welcome. We appreciate the feedback!

 

[bhieatza]

	A	B	C
Milk_001
Milk_001
Milk_001
Milk_001
Milk_001
Milk_001
Milk_001
Milk_001
Milk_001
Milk_001
Milk_001

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]135[/TD]

[TD="bgcolor: #FFFF00"]Milk_[/TD]
[TD="bgcolor: #92D050"]Milk_001[/TD]

[TD="align: center"]136[/TD]

[TD="bgcolor: #FFFF00"]Milk_[/TD]
[TD="bgcolor: #92D050"]Milk_002[/TD]

[TD="align: center"]137[/TD]

[TD="bgcolor: #FFFF00"]Milk_[/TD]
[TD="bgcolor: #92D050"]Milk_003[/TD]

[TD="align: center"]138[/TD]

[TD="bgcolor: #FFFF00"]Milk_[/TD]
[TD="bgcolor: #92D050"]Milk_004[/TD]

[TD="align: center"]139[/TD]

[TD="bgcolor: #FFFF00"]Milk_[/TD]
[TD="bgcolor: #92D050"]Milk_005[/TD]

[TD="align: center"]140[/TD]

[TD="bgcolor: #FFFF00"]Milk_[/TD]
[TD="bgcolor: #92D050"]Milk_006[/TD]

[TD="align: center"]141[/TD]

[TD="bgcolor: #FFFF00"]Milk_[/TD]
[TD="bgcolor: #92D050"]Milk_007[/TD]

[TD="align: center"]142[/TD]

[TD="bgcolor: #FFFF00"]Milk_[/TD]
[TD="bgcolor: #92D050"]Milk_008[/TD]

[TD="align: center"]143[/TD]

[TD="bgcolor: #FFFF00"]Milk_[/TD]
[TD="bgcolor: #92D050"]Milk_009[/TD]

[TD="align: center"]144[/TD]

[TD="bgcolor: #FFFF00"]Milk_[/TD]
[TD="bgcolor: #92D050"]Milk_010[/TD]

[TD="align: center"]145[/TD]

[TD="bgcolor: #FFFF00"]Milk_[/TD]
[TD="bgcolor: #92D050"]Milk_011[/TD]

</tbody>

Blad1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B135[/TH]
[TD="align: left"]=LEFT(A135,LEN(A135)-3)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C135[/TH]
[TD="align: left"]=B135&TEXT(COUNTIF($B$135:$B135,$B135),"000")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Is this possible to do for multiple things such as:
Milk_001
Grapes_001
Milk_001
Grapes_001
Milk_001
Corn_001
Grapes_001

Goes to:
Milk_001
Grapes_001
Milk_002
Grapes_002
Milk_003
Corn_001
Grapes_003
???

 

[Oeldere]

Yes off course, same concept.

Book1
	A	B	C
155	Milk_001	Milk_	Milk_001
156	Grapes_001	Grapes_	Grapes_001
157	Milk_001	Milk_	Milk_002
158	Grapes_001	Grapes_	Grapes_002
159	Milk_001	Milk_	Milk_003
160	Corn_001	Corn_	Corn_001
161	Grapes_001	Grapes_	Grapes_003
Blad1

Cell Formulas
Range	Formula
B155	=LEFT(A155,LEN(A155)-3)
C155	=B155&TEXT(COUNTIF($B$155:$B155,$B155),"000")