Align data from two columns


Assume two data lists. List1 has Test1,Test2 and Test3 in A2:A4 and List2 has Test1,Test4,Test2 in B2:B4. I would like to align data from both lists to appear as follows:

List1    List2
Test1   Test1
Test2   Test2

You may refer to my solution in this workbook.

You may watch a short video of my solution here

Leave a Comment

Your email address will not be published.


  • Is there as way to easily replace value based on the value in another field. For example:
    Say I have 2 Columns in my power query results, Col1 and Col2
    Col1 has values A, B, C & D
    Col2 has values 1, 2, 3 & 3

    I want to replace value in Col2 to 4 whenever value in Col1 is D.

    • Hi,

      I have assumed that the headings of the two columns are Text and Numbers. Add this custom column and enter this formula there

      if [Text]=”D” then 4 else [Numbers]

  • Hi,
    what if my “List 1” is in a different sheet than “List 2”.

    and what if I have more than two list.
    i.e. “List 1”, “List 2”, “List 3”?

  • HI,
    I have watched your you tube on ‘align data from two column’. As per your excel sheet there was power query in your excel whereby, mine does not have and some other parts it is not appearing which makes me unable to proceed with the job to be done.

    seeking your help or advice to sort the problem which i have to pair it or align it for over 7000 plus rows. Second column datas are jumbled up.

    Kindly advice me how to proceed with it.

    Thank you.

    • Hi,

      I am not sure of how to solve this without Power Query. Share the link from where I can download an Excel file with some dummy data and also show the expected result in that file.

  • Hi Ashish,

    Thank you for this excellent guide. I have a question regarding the final merge section of the video, as the latest version of Excel has an additional option here and I’m unsure as to which I should choose.

    When merging the two lists, should I select “Left Outer” under “Join Kind” (which is the default option) or should I choose something else like “Full Outer” which would be all rows from both columns.

    For reference, the two datasets I’m looking to sort are a reference number. In one set, the numbers are always integers and in the other, in some instances, the numbers are decimalised to specify a sub-section of this reference number. i.e. Column A has 12345 while Column B has 12345, 12345.1, 12345.2. I would like to arrange the data so that everything in Column B >= 12345 and <12346 is aligned principally with 12345 in Column A.

    Many thanks for any advice you can give and let me know if you need any further clarification.