+ Reply to Thread
Results 1 to 14 of 14

How to order multiple rows according to the reference order?

  1. #1
    Registered User
    Join Date
    06-28-2016
    Location
    Bangkok
    MS-Off Ver
    2010
    Posts
    80

    How to order multiple rows according to the reference order?

    Hi everyone,

    Could anyone provide me step-by-step guide or VBA / Macro that order multiple rows according to the reference order?

    For example
    • Category 'AA1' with its data is currently at row 25 in the input tab, but it should be at row 2 according to the reference order.
    • Category '0.0.0.0' with its data is currently at row 40 in the input tab, but it should be at row 3 according to the reference order.
    • Category '123.45.6.789' with its data is currently at row 61 in the input tab, but it should be at row 4 according to the reference order.
    • and etc.

    Please be informed that some of category may not exist in the input sheet. But, it should be appeared at output.
    • There is no category 'BB299' to 'BB498' in the input sheets. But these categories (rows) should be appeared at the output sheet.

    The last requirement is to fix all columns. In the future, if there is set 1.1. It should be appeared at the last column ('D'). If there is set 1.2 in another input, it should be appeared at the last column. ('E') This is same as row dimension: if there is a new category 'BB499' in another input, it should be appeared at row 501.

    Please take a look at the file attached for example of input, reference order and expected output.

    Thank you and have a nice day

    N.B. # 1 The real data contains more than 20,000 records, but they are sticky confidential.
    Attached Files Attached Files
    Last edited by cmokasak; 07-26-2016 at 12:55 AM.

  2. #2
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: How to order multiple rows according to the reference order?

    b2 on the sheet referance order =if(vlookup($A2,Input!$A$2:$C$300,column(),0)=0,"",vlookup($A2,Input!$A$2:$C$300,column(),0)) and drag across.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  3. #3
    Forum Expert José Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: How to order multiple rows according to the reference order?

    Hi
    In 'Reference Order'!B2 use the following formula anc copy down and forward.
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Adjust ranges as you need.

  4. #4
    Registered User
    Join Date
    06-28-2016
    Location
    Bangkok
    MS-Off Ver
    2010
    Posts
    80

    Re: How to order multiple rows according to the reference order?

    Thank you oeldere and Jose Augusto for your solution.
    I will adapt your code to the real data and will summarize the post by the end of tomorrow.

  5. #5
    Registered User
    Join Date
    06-28-2016
    Location
    Bangkok
    MS-Off Ver
    2010
    Posts
    80

    Re: How to order multiple rows according to the reference order?

    Hi both,

    How about just order rows without fixed columns?

  6. #6
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: How to order multiple rows according to the reference order?

    How about just order rows without fixed columns?
    why should you do that, it only takes time to count.

    Make the range big enough, so it fits all rows.

    Excel 2013 has > 1 mln rows which need to be counted if you use whole columns.

    That is why we advice you to use a fixed range.

  7. #7
    Registered User
    Join Date
    06-28-2016
    Location
    Bangkok
    MS-Off Ver
    2010
    Posts
    80

    Re: How to order multiple rows according to the reference order?

    Deleted because of duplicated reply
    Last edited by cmokasak; 07-25-2016 at 08:49 AM.

  8. #8
    Registered User
    Join Date
    06-28-2016
    Location
    Bangkok
    MS-Off Ver
    2010
    Posts
    80

    Re: How to order multiple rows according to the reference order?

    I am agree with you.

    But when I executed with a table of 2,000 columns × 2,000 rows, my computer become freezed.
    Last edited by cmokasak; 07-25-2016 at 08:52 AM.

  9. #9
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: How to order multiple rows according to the reference order?

    In that case it will definitly not work on the whole worksheet sheet which is below.

    Worksheet size

    1,048,576 rows by 16,384 columns

  10. #10
    Registered User
    Join Date
    06-28-2016
    Location
    Bangkok
    MS-Off Ver
    2010
    Posts
    80

    Re: How to order multiple rows according to the reference order?

    Thank you for your reply.

    I will retry the code with faster computer.

    Sorry my main language is not english. What you have mentioned: any worksheet with less than 1048576 rows × less than 16384 columns should work with your formula. Am I right?

  11. #11
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: How to order multiple rows according to the reference order?

    No, I'm telling you to work with fixed range, since the range of a excel workstheet is 1,048,576 rows by 16,384 columns.

    First run the test on a faster computer.

  12. #12
    Registered User
    Join Date
    06-28-2016
    Location
    Bangkok
    MS-Off Ver
    2010
    Posts
    80

    Re: How to order multiple rows according to the reference order?

    Thank you for your suggestion.
    I will retry the code with faster computer tomorrow.
    Then, I will inform you the results.
    Have a nice day

  13. #13
    Registered User
    Join Date
    06-28-2016
    Location
    Bangkok
    MS-Off Ver
    2010
    Posts
    80

    Re: How to order multiple rows according to the reference order?

    Thank you oeldere and José Augusto.

    Your code is working

    N.B. I have added a reputation for both of you.

  14. #14
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: How to order multiple rows according to the reference order?

    You're welcome. We appreciate the feedback!

    Thanks for the rep.

    Your code is working
    It is not a code, it is a formula.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 4
    Last Post: 12-02-2014, 03:46 AM
  2. Replies: 1
    Last Post: 12-01-2014, 03:01 PM
  3. [SOLVED] Order Form to Summarise Order on another sheet with a Submit Order Button
    By KazzICC in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-01-2014, 03:25 AM
  4. Using Macro to put rows in multiple sheets in ascending order
    By harjas in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-28-2012, 11:59 AM
  5. [SOLVED] How to consolidate data containing multiple colums and rows into single row in order?
    By seaspi in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-01-2012, 05:33 AM
  6. Multiple rows make a single order and Pivot table
    By saurya_s in forum Excel General
    Replies: 5
    Last Post: 10-30-2008, 01:51 PM

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