+ Reply to Thread
Results 1 to 9 of 9

Order two columns based off two other columns order

  1. #1
    Registered User
    Join Date
    12-03-2020
    Location
    australia
    MS-Off Ver
    365
    Posts
    55

    Exclamation Order two columns based off two other columns order

    Hi,

    Would someone be able to help me out with a problem I have.

    Basically I have two lists, one is the unordered (raw) data with various other information and the other is the ordered list without the various information.

    Is it possible to order the raw data columns based off the ordered list columns?

    I have attached the excel file, where I have highlighted the raw data with various information to the left and right of "first names" and "last names".

    I have also the ordered "first name" and "last names".

    What I want to learn is how to order the raw data such that I get the desired result.

    I have tried to make another column right next to the last name on the daw that and used the =MATCH function, but the issue is when I have identical first name, it fails.

    I use excel 365.

    Thank you
    Attached Files Attached Files
    Last edited by bob112233; 11-22-2021 at 05:14 AM.

  2. #2
    Forum Expert WideBoyDixon's Avatar
    Join Date
    10-03-2016
    Location
    Sheffield, UK
    MS-Off Ver
    365
    Posts
    1,899

    Re: Order two columns based off two other columns order

    I guess this is just generic data but I would possibly approach like this:

    Please Login or Register  to view this content.
    Replace J$3:J$7 and K$3:K$7 with the ranges of the first and second names. You can then copy those formulas down the column. Then to get the data for each name:

    Please Login or Register  to view this content.
    Again, you'd need to adjust the ranges as appropriate. This formula can then be copied down and into the other columns. See attached.

    WBD
    Attached Files Attached Files
    Office 365 on Windows 11, looking for rep!

  3. #3
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    62,051

    Re: Order two columns based off two other columns order

    Administrative Note:

    Members will tailor the solutions they offer to the version of Office (Excel) that you have. Please check that your forum profile is up-to-date in this respect: there are 2019, 2021 and 365 versions, so I'm not sure yours is correct here. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this. Thanks.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

  4. #4
    Registered User
    Join Date
    12-03-2020
    Location
    australia
    MS-Off Ver
    365
    Posts
    55

    Re: Order two columns based off two other columns order

    I'm not sure if I understand your logic. What happens when there are two identical first names?

  5. #5
    Forum Expert WideBoyDixon's Avatar
    Join Date
    10-03-2016
    Location
    Sheffield, UK
    MS-Off Ver
    365
    Posts
    1,899

    Re: Order two columns based off two other columns order

    The logic is sound. The only issue is if you have two identical names (e.g. two people called John Smith). Did you open the attachment I sent which has two people called John but the information is correctly retrieved?

    WBD

  6. #6
    Forum Expert WideBoyDixon's Avatar
    Join Date
    10-03-2016
    Location
    Sheffield, UK
    MS-Off Ver
    365
    Posts
    1,899

    Re: Order two columns based off two other columns order

    I see you've updated your MS Office version to 365. In that case, you could also use XLOOKUP in A11:

    Please Login or Register  to view this content.
    That would also work copied across and down.

    WBD

  7. #7
    Registered User
    Join Date
    12-03-2020
    Location
    australia
    MS-Off Ver
    365
    Posts
    55

    Re: Order two columns based off two other columns order

    Quote Originally Posted by WideBoyDixon View Post
    The logic is sound. The only issue is if you have two identical names (e.g. two people called John Smith). Did you open the attachment I sent which has two people called John but the information is correctly retrieved?

    WBD
    Hi,

    Thank you for your quick replies. Yes I've opened it, i'm just trying to get my head around it so I can learn from it.

    Can I ask why when I tried to approach it using the =MATCH function then use normal sort by the match column, i wasn't able to resolve the identical first name issues.

    I've attached the code down below.
    Attached Files Attached Files

  8. #8
    Forum Expert WideBoyDixon's Avatar
    Join Date
    10-03-2016
    Location
    Sheffield, UK
    MS-Off Ver
    365
    Posts
    1,899

    Re: Order two columns based off two other columns order

    I'm not sure what you've attached there but perhaps you were only using the first name in the MATCH() formula. My solution concatenates the first and second names to do a lookup on multiple columns.

    WBD

  9. #9
    Registered User
    Join Date
    12-03-2020
    Location
    australia
    MS-Off Ver
    365
    Posts
    55

    Re: Order two columns based off two other columns order

    Thank you for your time WBD, really appreciate it. I will study your logic sorry I'm a slow learner.

+ 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. How to order columns of Pivot table in correct month order
    By Cordelia123 in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 04-23-2021, 08:29 AM
  2. [SOLVED] Find value in two columns and return based on order
    By D-smoke in forum Excel General
    Replies: 6
    Last Post: 05-16-2018, 08:01 AM
  3. Export columns based on alphabetical order
    By rpetty in forum Excel General
    Replies: 6
    Last Post: 07-26-2017, 10:48 PM
  4. Replies: 7
    Last Post: 04-23-2014, 10:45 AM
  5. Replies: 0
    Last Post: 03-19-2013, 12:23 AM
  6. How to order columns based on maximum value in each column?
    By guilhermepc in forum Excel General
    Replies: 0
    Last Post: 01-16-2012, 08:14 PM
  7. how to re-arrange columns based on a specified order
    By 2seas in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 01-05-2012, 10:06 AM

Tags for this Thread

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