Column Help

[RedKobra]

I have created a spread sheet in excel and over the years it's gotten pretty big. I have a column called ID that has numbers in it. Over the the years some of those numbers in the id column have been removed.

For example, the current spread sheet looks like something like this

1
1
2
5
16
16
16
16
28
28
28
28
40
40
40
45
50
60
60
60

What I want to do is to make the column where numbers are not missing between other numbers including keeping the duplicate numbers.

For example

1
1
2
5
16
16
16

I like for those numbers to now look like

1
1
2
3 ( is 5)
4 (is 16)
4 (is 16)
4 (is 16)

Is this possible to get this done in excel? Hopefully I am not confusing anyone on what I am trying to accomplish here.

Thanks,
RedKobra

[FDibbins]

Hi and welcome to the forum

Assuming you want to do this with a formula, use a helper (I usefd B, as shown below), but you can use whatever you want.

	A	B
1	1	1
2	1	1
3	2	2
4	5	3
5	16	4
6	16	4
7	16	4

B1=1
B2=IF(A2=A1,B1,B1+1)
copied down

You can then either just keep the helper, or copy/paste values from the helper over the original data and the delete the helper

[RedKobra]

FDibbins, thanks for responding to my post. I am not sure what helper is. Based on the diagram you have posted, it looks like I need to create two more column to achieve what I am asking. Is that correct?

Thanks

[SCLai]

Hi RedKobra,
A helper Column/Row (usually a column, and in FDibbin's solution too) is one that was added to assist in arriving to the desired (final) display.

Hi FDibbin,
Sorry if I am mistaken, but I think what RedKobra wants as the final output would be your column B, and not A, so the helper column should be A instead of B? Or does RedKobra wants both? Or was I mistaken somewhere?


Regards
SCLai

[RedKobra]

SCLai, thanks for replying. I am wanting the final output to look like column B. To be honest, I have never created a helper column before, so I am confused on how to create one. I have about 1,000 rows that I wanted to organize in the correct number order. I could do it manually, but that I will take a bit of time. I was just hoping to find a quicker way to save some time.

Thanks again.

[oeldere]

FDibbins has already given you the answer in #2.

Type 1 in cell B1

Type =IF(A2=A1,B1,B1+1) in cell B2

And drag the formula down.

[RedKobra]

FDibbins, SCLai, and oeldere a BIG THANKS for helping me. You save me a lot of time. FDibbins thanks for the formula, I just didn't understand the helper column.

Again thank you very much

[FDibbins]

@ RedKobra, a helper column is just that (run calcs, simplify formulas, pretty much anything) - a colu8mn that helps you do stuff. Then, depending on the situation, you either keep the helper (and maybe use it for other calc's) or delete it once it has served its purpose.

In your case, you could do either. If you keep it, you could then use that for further cals (if any) or you could copy/paste values over your original data (this copies the answer to the formula, not the formula) and then delete the helper. It all depends on what you need to do, and to some extent, how often you need to do it.

Thanks for the feedback, glad it worked for you