Create number series based on another number series

[hbangalore]

Hi guys,

May be quite simple for some of you and could use some help.

I have one column with

234
234
234
235
235
235
235
236
236

I need to make it into a number series but keep the same format-as in 235 becomes 1, 235 becomes 2,etc so the column would look like

1
1
1
2
2
2
3
3

Sorry for the long post but I find it easiest to express my problem this way. Thanks.

[Paul]

Hi HBangalore,

I've adjusted your thread title to something that is descriptive of your actual problem. Please take a moment to read the forum rules (link in my signature) before posting new threads.

As for your problem, assuming your current numbers are in A1:A100, then in B1 put the formula:

=A1-$A$1+1

Fill that down and you will get a series based on the value in column A minus the value in A1 + 1 for each cell. If you require a different result, please provide a realistic set of data, preferably in a workbook attachment showing current and expected results (and why).

[haru]

I was thinking of something like this:

what you want to do seems a little confusing, but I think this is what you want to do assumme the one column you have is in column A.

Start on B2:

Please Login or Register  to view this content.

[hbangalore]

Hi Paul,

Thanks for the prompt reply and for modifying my inquiry. I kind of thought of changing it after I hit the send button! Anyway, the suggestion did not quite solve the problem entirely.

I have attached the file to explain better. I want to know if there is an easier way for column 2. I want to rename it as:

I have to analyse the results in a stats programme and the graphs are looking weird with long numbers in column 1.

Note that the cases do not repeat themselves on a regular basis-i.e some a repeats for 2 times, some for 3 etc.

[hbangalore]

Thanks Haru. It works! Please ignore my reply to Paul as I had not seen your post. Good day.

[hbangalore]

Hi,

This formula seemed to have worked before but when i tried this again, it is giving me a circular error message no matter what I try. Any thoughts or suggestions? I tried tackling the circular error as suggested by microsoft but I am stuck. appreciate help!

[Paul]

Hi HB,

Can you post a copy of the workbook in which you're seeing the circular references? As long as the formulas are in the proper cells I don't think that should happen. (Unless of course the cells being referenced can at some point be traced back to the cells with the formulas.)

[Haseeb Avarakkan]

Hello, try in B2, then copy down.

Please Login or Register  to view this content.

[hbangalore]

Hey,

Thanks a lot! Works well now.