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
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

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Blad1​

Worksheet Formulas Cell Formula B135 =LEFT(A135,LEN(A135)-3) C135 =B135&TEXT(COUNTIF($B$135:$B135,$B135),"000") <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

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
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

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Blad1​

Worksheet Formulas Cell Formula B135 =LEFT(A135,LEN(A135)-3) C135 =B135&TEXT(COUNTIF($B$135:$B135,$B135),"000") <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

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")