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
Test3
Test4
You may refer to my solution in this workbook.
You may watch a short video of my solution here
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,
Any list can be on any worksheet – does not matter at all. For more than two lists, you will have to merge the data twice.
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,
I want to do same task on vba, can please share some content or guide how to do the same task on VBA:
Thanks
Hi,
I do not provide VBA support.
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.
Hi,
Share some data and show the expected result.
Great video! This exactly matches what I need to do.
Thank you.
Align data from two columns
{ 12 Comments }