+ Reply to Thread
Results 1 to 15 of 15

Match One Column's Contents with Anothers

  1. #1
    Registered User
    Join Date
    08-13-2015
    Location
    London
    MS-Off Ver
    14.3.8
    Posts
    10

    Match One Column's Contents with Anothers

    I need the order of the contents in one column to match the order of the contents in another. For example:

    Column A:
    A
    C
    D
    N
    G

    Column B:
    C
    D
    G
    N
    A

    How can I make it so column A's contents match the order of column B's contents assuming both columns have the exact same information, just in a different order? I want to be able to do this without having to copy and paste to move things around. I have documents with thousands of rows that need to match a specific order.

    Thanks!

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Match One Column's Contents with Anothers

    You mean so the result looks like this?

    Row\Col
    A
    B
    1
    Hdr1
    Hdr2
    2
    A A
    3
    C C
    4
    D D
    5
    G G
    6
    N N
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Registered User
    Join Date
    08-13-2015
    Location
    London
    MS-Off Ver
    14.3.8
    Posts
    10

    Re: Match One Column's Contents with Anothers

    Not alphabetically, but so that the order of one column matches another. So in my example, both columns will look like Column B, in that order.

  4. #4
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Match One Column's Contents with Anothers

    Quote Originally Posted by AlexNsvc View Post
    How can I make it so column A's contents match the order of column B's contents assuming both columns have the exact same information, just in a different order?!
    with column C, try:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    and drag down. If you see any FALSE => order1 <> order2
    Last edited by sandy666; 03-29-2016 at 04:49 PM.

  5. #5
    Registered User
    Join Date
    08-13-2015
    Location
    London
    MS-Off Ver
    14.3.8
    Posts
    10

    Re: Match One Column's Contents with Anothers

    Hey Sandy,

    This is helpful for finding out which are out of place. But if I have thousands of FALSE, it won't save me very much time.

    Do you know of a way to order the column based on the order of column A or B?

  6. #6
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Match One Column's Contents with Anothers

    assumed exactly the same info in A and B you can use simply (in B1) =A1 and drag down or if you want order from B, in A1: =B1 and drag down.
    I don't think you need any giant formula to re-ordering column, but maybe I am wrong

  7. #7
    Registered User
    Join Date
    08-13-2015
    Location
    London
    MS-Off Ver
    14.3.8
    Posts
    10

    Re: Match One Column's Contents with Anothers

    Another nice suggestion.

    Here's another curveball, if we want column B to match column A's order but column B also has column C,D,E & F contents that need to lineup. Then what is the next step? I'm asking for some kind of advanced Excel filter which may not be possible but if there's a formula for this please let me know.

  8. #8
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Match One Column's Contents with Anothers

    You mean you want the same order in all columns , B,C,D,E,F like A order? (with exactly the same information)

  9. #9
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Match One Column's Contents with Anothers

    If you post a workbook with a realistic example (but no sensitive data), I will add a macro and include instructions for use.

  10. #10
    Registered User
    Join Date
    08-13-2015
    Location
    London
    MS-Off Ver
    14.3.8
    Posts
    10

    Re: Match One Column's Contents with Anothers

    I have attached a quick example that I just made quickly.

    Basically just need column C to match column A, but all columns after column C move with column C.

    Thanks for looking into this everyone.
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    08-13-2015
    Location
    London
    MS-Off Ver
    14.3.8
    Posts
    10

    Re: Match One Column's Contents with Anothers

    I've heard that this may need to be done with vlookup?

  12. #12
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Match One Column's Contents with Anothers

    See attached.
    Attached Files Attached Files

  13. #13
    Registered User
    Join Date
    08-13-2015
    Location
    London
    MS-Off Ver
    14.3.8
    Posts
    10

    Re: Match One Column's Contents with Anothers

    The end result looks correct to me, do you know how to do this on Mac Excel v.14.5.3?

  14. #14
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Match One Column's Contents with Anothers

    Never touched a Mac.

  15. #15
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Match One Column's Contents with Anothers

    With formula you need to copy data from original place to new place (e.g. mentioned VLOOKUP)
    so, with you example:
    1. Delete empty column B. You will get columns A B C D E with no empty data.
    2. in G2 put:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    and drag to the right and down as far as you see "no more data"
    Last edited by sandy666; 03-29-2016 at 07:21 PM. Reason: formula updated

+ 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. Excel 2013 - Lock Cells dependent on anothers contents
    By suzanneb in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-27-2014, 05:15 AM
  2. Replies: 6
    Last Post: 12-07-2012, 05:38 PM
  3. [SOLVED] Vlookup? Pivot Table? How do I match column A and B to contents in column C?
    By rushnit in forum Excel Formulas & Functions
    Replies: 17
    Last Post: 08-08-2012, 10:16 AM
  4. Replies: 4
    Last Post: 07-27-2011, 04:05 PM
  5. condetional copy from tow columns to anothers
    By abdi1 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-16-2011, 05:09 AM
  6. adjust one cells values depending on anothers
    By M_Jack in forum Excel General
    Replies: 2
    Last Post: 10-23-2009, 02:05 PM
  7. Need Help testing if a cells contents match that of a cell in another column
    By jmherbert in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-26-2008, 03:56 PM

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