How Do I change repeating numbers in a column

[Joel]

Hi,

I'm creating a huge spreadtsheet for a large music collection. One of the
columns is the track number. Unfortunately some of the discs have the same
track number. I would like to figure out a way to index these numbers so
instead of track 3 reapting 4-5 times it will index to 3.2, 3.2., 3.3,
....etc.

Is there a way to do this without changing each cell individually? It would
be a huge job.

Thanks in advance for your help!!

 

[Max]

One idea is to produce it as text ..
Assuming track numbers in A1 down
In B1:
=IF(A1="","",IF(COUNTIF(A$1:A1,A1)>1,(A1&"."&COUNTIF(A$1:A1,A1)-1),A1&""))
Copy B1 down as far as required

 

[Joel]

Thanks for your response Max.

Unfortunately this data is going to be imported into another database and
they need it in a specific format.

So the track numer column (in This case Column D) needs to be a standard
numerical vaule. Any repeated numbers from the same disc must have an
indexed numer (3.2, 3.3, 3.4, etc)

It is a big problem because there are many track #3's (random track number
for expample purposes) in the column. But it may not be repeating because it
is the only track #3 that correlates with with that specific disc (another
comlumn).

The ones that need indexing are the repeated numbers that are stacked on top
of each other in this column. So if thare five number 3's consectutively
(scrolling down) in the column ...they need to be indexed.

 

[Joel]

Thanks edvwvw

each index cell.

Yup, that definitely works. Still a pretty huge job though because finding
all of the cells that repeat in this manner and then applying this drag down
techniqiue will take some time.I'm dealing with over 30,000 cells in this
column. Most of the repeating numbers only repeat 4-5 times.

So I was hoping to to identify these repeating cells faster than scrolling
down this entire sheet.

In additional to what seems like an impossible wishlist ...to
automatically change those numbers in the indexed format.

But thank you for your help! IF all else fails you've defintiely shaved some
time off my task! Thanks!

Joel

 

[Max]

I'm not sure that I'm following you ..

Assuming source data in A1:A5 is
1
2
3
3
3

then the earlier suggestion returns it as:
1
2
3
3.1
3.2

If you want it returned as:
1
2
3
3.2
3.3

just change the formula in B1 to:
=IF(A1="","",IF(COUNTIF(A$1:A1,A1)>1,(A1&"."&COUNTIF(A$1:A1,A1)),A1&""))
and copy down

Doesn't the above give you the desired indexing? - which will distinguish
the duplicate source numbers? Given the assumed source data in A1:A5, what
returns do you expect in B1:B5?

 

[dhstein]

I'm not sure exactly what you need, but here's an idea. You're looking to
modify the track number if the track number repeats. So in a separate column
create a formula. I assume the track number is in Column E and the formula
is in column X. Use this in Cell X2 --- =IF(E2=E1,X1+.01,E2) Now
propagate that formula all the way down. You should get unique numbers in
column "X". You can then copy - paste special value from column X to column
E.