+ Reply to Thread
Results 1 to 10 of 10

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

  1. #1
    Registered User
    Join Date
    12-26-2012
    Location
    US
    MS-Off Ver
    Excel 2007
    Posts
    11

    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.

    Many thanks in advance!
    Attached Files Attached Files

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,243

    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
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,142

    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)

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

  4. #4
    Registered User
    Join Date
    12-26-2012
    Location
    US
    MS-Off Ver
    Excel 2007
    Posts
    11

    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.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    12-26-2012
    Location
    US
    MS-Off Ver
    Excel 2007
    Posts
    11

    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. #6
    Registered User
    Join Date
    12-26-2012
    Location
    US
    MS-Off Ver
    Excel 2007
    Posts
    11

    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
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    12-26-2012
    Location
    US
    MS-Off Ver
    Excel 2007
    Posts
    11

    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.
    Attached Files Attached Files
    Last edited by abreichenbach; 01-20-2013 at 12:54 AM.

  8. #8
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

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

    Try this :
    Formula: copy to clipboard
    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)
    Attached Files Attached Files
    A picture may be worth a thousand words, BUT, a sample Workbook is worth a thousand screenshots!
    -Add a File - click advanced (next to quick post), scroll to manage attachments, click, select add files, click select files, select file, click upload, when file shows up at bottom left, click done (bottom right), click submit
    -To mark thread Solved- go top of thread,click Thread Tools,click Mark as Solved
    If you received helpful response, please remember to hit the * of that post

  9. #9
    Registered User
    Join Date
    12-26-2012
    Location
    US
    MS-Off Ver
    Excel 2007
    Posts
    11

    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. #10
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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