Excel number ordering

[deansonline]

Hi Everyone,

My name is Dean, I've spent the last few weeks struggling to learn excel and now i need a little help if possible...

Does anyone know of way to quickly number each name as I've done above and then to start again back to the number 1 after you hit the date in the blue line?... I have 1000"s of rows to do this to.

been doing this for hours and have no idea if you can?..

Thanks

[#2]

@deansonline yeah you can do it.  put this formula below as I am considering that your data in image starts from 1

In Cell A4,

=If(isnumber(left(B4,1)*1),"",B3+1)

and drag this formula till last Row. Hope this works.

[#3]

@jukapil 

Thanks for getting back to me so quickly, unfortunately ive copied and pasted it and its still counts without resetting.

I don't know if this will help....

i have attached another picture, I have a column that i don't need and that i am happy to copy and paste over it. as soon as there is a gap i need to it reset back to 1 and count again.

Thank you very much for your time

[#4]

@deansonline seems your dates are different format. Use the same formula and drag it till end. Then Please activate the filter in Column B and do filtering by when contains = 2020. This will filter the date rows then select Column A and press delete. Then it will reset the numbers.

Hope your name does not contain 2020

[#5]

@jukapil 

Really do appreciate your help, 

I don't know what i am doing wrong here, Can you take a look at see if its just me?.. I can't seem to crack it.

Best

Dean

[#6]

@deansonline 

It looks like you are working with Pivot Table. I'm not sure if data model is available on Mac, if so you may add calculated column with ID into the model and use it. For such sample

such column could be calculated as

=
CALCULATE (
    COUNTA ( Table1[N] ),
    FILTER ( Table1, Table1[D] = EARLIER ( Table1[D] ) ),
    Table1[N] <= EARLIER ( Table1[N] )
)

If not, perhaps you may add helper column to the source data calculating unique names ID for each datetime.

[#7]

@Sergei Baklan 

Thank you for your reply, 

Sad to say i have no idea what im doing, I thought this would be a simple process but how wrong am i :)

I may have to just carry on doing all this manually.

[#8]

@deansonline Sorry got asleep. Please see attached file.

You can keep dragging the formulas and it will work for more data. Meanwhile I have done it for most of them. Just One minute job

Thanks

Kapil

[#9]

As a variant:

Since your dates are all properly formatted, consider this formula in A3 and fill it all the way down.

=IF(ISNUMBER(B3),"",N(A2)+1)

The N(..) part sees to it that you don't get an error when you add 1 to a cell with "". You will loose the formatting in the first column (blue color and borders), but that can easily be fixed by "painting" the format of any of the other columns on to A and change back the alignment and number formatting to your liking. Your revised file is attached.

[#10]

You guys are absolute legends and i can't thank you enough,

This is absolutely super, 

Thank you so so much :)

Dean