# Generate "ascending order no" for columns of data that are not in order

1. ## Generate "ascending order no" for columns of data that are not in order

Hello,

I wish to generate an "ascending order no" for (2) columns of data that are not in ascending order based on those columns (the tables are in ascending order based on a different column, "Data Sequence"). Currently I am doing this by temporarily sorting the column in question in ascending order, manually generating the "ascending order no", then returning the tables back to their original order state, which based on the ascending order of a different column, "Data Sequence".

Attached is the file, "List Order", where on Sheet1, I need the "TimeStamp order" and "Distance Order". I have manually inserted the desired results.

Please note that one of the columns in question ("Distance Order") has "999", and this always needs to be "999", and the remaining non-999 values need an "ascending order no".

I desire a formula solution, but a non-control-shift-enter solution, as my co-workers tend to invalidate these array formulas too easily.

Helper columns are OK.

2. ## Re: Generate "ascending order no" for columns of data that are not in order

Im not sure how you got the values in D, and im not sure if the formulas below will give you what you want...

=INDEX(\$A\$2:\$C\$15,MATCH(LARGE(\$C\$2:\$C\$15,ROW(A1)),\$C\$2:\$C\$15),1)
or
=INDEX(\$A\$2:\$C\$15,MATCH(SMALL(\$C\$2:\$C\$15,ROW(A1)),\$C\$2:\$C\$15),1)

If this doesnt work for you, please show what your expected outcome is, as well as how you arrived at that

3. ## Re: Generate "ascending order no" for columns of data that are not in order

hi abreichenbach, try this in D2:
=SUMPRODUCT((\$B\$2:\$B\$15=B2)*(\$C\$2:\$C\$15<C2))+1

and in D19:
=IF(C19=999,999,SUMPRODUCT((\$B\$19:\$B\$32=B19)*(\$C\$19:\$C\$32<C19))+1)

4. ## Re: Generate "ascending order no" for columns of data that are not in order

I'm not sure your formulas get what I need. I have uploaded a new file, to make it more clear. The data can actually go in one table, but for some reason I thought that separating the data into (2) tables for the forum would be better...but I think it just made it confusing.

I have uploaded a single table, which is how it exists now. I need formulas for the orange columns of data, "TimeStamp Order", and "Distance Order", which I manually generated by sorting other columns as follows:

To generate the "TimeStamp order", I used the auto-filter to filter for a single "unique site identifer", sorted "TimeStamp" in ascending order, manually generated the "TimeStamp Order" (1,2,3, in D2,D3,D4, etc), repeated this process for each "unique site identifier".

I repeated the process to generate "Distance Order". I filtered for a single "unique site identifer", sorted "Distance" in ascending order, then manually generated the "TimeStamp Order" (1,2,3, in F1,F2,F3, etc), but left "999" values as "999".

Then I turned off the auto-filter, and sorted the table back to it's original state based on "Data Sequence" in ascending order.

"Unique Site Identifier", "TimeStamp", and "Distance" was data collected in the field, and all other remaining columns were manually generated.

5. ## Re: Generate "ascending order no" for columns of data that are not in order

Actually, at first glance, I think benishiryo's formulas work...I am going to apply it to another dataset and will report back.
Thanks!

6. ## Re: Generate "ascending order no" for columns of data that are not in order

Ok, so benishiryo's formulas work for the dataset that I loaded, however, I realized it doesn't work for about 10% of the datasets. I've uploaded a new file, Distance_order_forum.xlsx, with an additional column added, called "Distance on New Tape".

If "Distance on New Tape" was recorded, that means an additional measuring tape was used, because using one measuring tape was not long enough.

The data highlighted in yellow is the expected result, which was manually derived by excluding the "999" records and manually typing in (1,2,3, etc), from top to bottom.

The data highlighted in orange is the formula that needs to be adjusted.

Two sites were loaded, where unique sites are identified in Column A, called "Unique Site-Year Identifier".

Thanks again

7. ## Re: Generate "ascending order no" for columns of data that are not in order

Ok, I thought I solved it but I was wrong...I thought I adjusted the formula correctly in column I, however, I was referencing column G, when I should have been referencing column F. Ignore this uploaded file.

8. ## Re: Generate "ascending order no" for columns of data that are not in order

Try this :
Formula:
`Please Login or Register  to view this content.`

To show you why the numbers are different than what you have, I've added Columns L,M,N to show what the comparision is looking at (only for the first site code,BTW)

9. ## Re: Generate "ascending order no" for columns of data that are not in order

Thanks for info, dredwolf. I realized the same thing.

In the end, I realized that the title of the post only holds true for "TimeStamp", which is not in ascending order but I needed the ascending order no.

For "Distance", because we use more than one tape for distance, and also because we don't always survey in order, the only way to generate the ascending order no for "Distance" is for the surveyor to somehow mark, in an adjacent column, the ascending order no. If we forced the surveyor to always survey in order, and only ever used one measuring tape, benishiryo's formula for "Distance order" would work.

I appreciate everyone's help!

10. ## Re: Generate "ascending order no" for columns of data that are not in order

You are welcome

and what you Could do, using the formulas, is have one report for Time order and one for Distance order, not sure if it would work for you, but it is possible

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1