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

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&"."&COUNT IF(A$1:A1,A1)-1),A1&""))
Copy B1 down as far as required
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Joel" wrote:
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!!

Joel wrote:
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!!


In the first cell format the figure as a number with one decimal place - this
will give you 3.0, then in the second cell 3.1. Highlight the two cells and
then drag down you will find that the last number will increment by one.
When you reach 3.9 it will want to go to 4 unless you then format with 2
decimal places to give 3.10, 3.11 etc

edvwvw

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200805/1

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.

"Max" wrote:

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&"."&COUNT IF(A$1:A1,A1)-1),A1&""))
Copy B1 down as far as required
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Joel" wrote:
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!!

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

"edvwvw via OfficeKB.com" wrote:

Joel wrote:
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!!


In the first cell format the figure as a number with one decimal place - this
will give you 3.0, then in the second cell 3.1. Highlight the two cells and
then drag down you will find that the last number will increment by one.
When you reach 3.9 it will want to go to 4 unless you then format with 2
decimal places to give 3.10, 3.11 etc

edvwvw

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200805/1

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&"."&COUNT IF(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?
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Joel" wrote:
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.

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.

"Joel" wrote:

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

"edvwvw via OfficeKB.com" wrote:

Joel wrote:
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!!


In the first cell format the figure as a number with one decimal place - this
will give you 3.0, then in the second cell 3.1. Highlight the two cells and
then drag down you will find that the last number will increment by one.
When you reach 3.9 it will want to go to 4 unless you then format with 2
decimal places to give 3.10, 3.11 etc

edvwvw

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200805/1

dhstein wrote:
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.

Thanks edvwvw

[quoted text clipped - 36 lines]

edvwvw

Joel

I have written a small macro to achieve the result that you want - try it on
a copy not the "live" data. There is one drawback at the moment - because I
have used the Integer value it will only go up to .9

The macro assumes that the values are in column A change as appropriate:

Sub test()
Dim LR As Long, i As Long
LR = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To LR Step 1

If Int(Range("A" & (i + 1)).Value) = Int(Range("A" & i).Value) Then
Range("A" & (i + 1)).Value = Range("A" & i).Value + 0.1

End If
Next i


End Sub

edvwvw

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200805/1