Ordering Question

**jason500** · 01-06-2010, 10:18 AM

Is it possible to give sets of numbers an order?

This is a bit difficult to explain so it's best to see the attachment (or code below) as an example.
(The order is based on the Date - But as long as everything is sorted acording to date in the first place, 'Date' may not even be needed in the final formula).

Please Login or Register  to view this content.

Does a formula for this even exist?

Thanks

**jason500** · 01-06-2010, 10:19 AM

Sorry, the code didn't display properly, see the attachment instead.

**sweep** · 01-06-2010, 10:24 AM

Hi,

If you just want the list of numbers as per your attachment,

D8=IF(B8<>B7,1,D7+1)

and copy down

**somesoldiers** · 01-06-2010, 10:26 AM

In 2007: select row 7 to end, sort by column B (smallest to largest), add level- sort by column A Oldest to newest

**jason500** · 01-06-2010, 10:28 AM

Legend!

That works great.
Thanks Sweep!

**sweep** · 01-06-2010, 10:32 AM

If your data isn't in ordered form, this

=SUMPRODUCT(($B$8:$B$27=B8)*(A8>$A$8:$A$27))+1

will do instead.

**jason500** · 01-06-2010, 10:37 AM

Wow, even better

**jason500** · 01-06-2010, 11:49 AM

Hi Sweep,
Thanks again for the help, the SUMPRODUCT formula works perfect except with the date formats I have (see attached sheet).
It considers November as a later month than December. Therefore, giving the wrong order.

It may a simple problem to do with formats but I can't seem to figure it out. No matter what format I change it to, it doesn't change.

Should hopefully be a quick solution, any ideas?

**jason500** · 01-06-2010, 12:20 PM

I have found the answer.
The file was exported from somewhere and made everything text format, to turn them back into number formats:
Select the data range
In Data tab > Text to Columns
Click Finish