How to convert a list into list of consecutive numbers

Hello!

Let me see if I can explain my question in an understandable fashion....

I have a table containing data for about 2000 ID numbers. Some of these numbers are unique and some are duplicates. I would like to convert the ID numbers into a consecutive list of integers while preserving the unique numbers. For example, if the first column of my table is currently:

ID#
18578
19644
19644
20247
20974
21361
21361
21419

I would like to change that to something like:

ID#
1
2
2
3
4
5
5
6

I need to know which records (i.e. which rows of data) are from the same ID# but want to remove the actual ID#.

Can anyone help with this?

Thanks!

Not really understood.

This is my proposal: Leave Column a alone
In column b produce a list of the unique numbers from Column A and sort them numerically.
In column C create a new series of ID Numbers to match the numbers in column B
In Column D Map the New Numbers to your existing data.

BRB

Originally Posted by tigeravatar

mr. grieves,

Welcome to the forum!
If your list starts in A1, then in B1 type 1 and in B2 and copied down use this formula:

Please Login or Register  to view this content.

I don't know how, but that worked exactly as I needed it to! Thanks!!

Ok here is my code for the above

Please Login or Register  to view this content.

Nice Code Tigeravtar.

But it assumes that all the id numbers are sorted sequentially.

But I assume that can easily be sorted.

So the code isn't nice, its excellent.

The following is based on Tigeravtars excellent code.

Please Login or Register  to view this content.