Excel – Align two columns in excel based on 1 criteria

microsoft excelmicrosoft-excel-2016worksheet-function

This has me stumped.

if you have data that looks like ABCD:

example data

How do I get it to HIJK?
I am trying to put 2 pivot tables next to each other, so I can compare the two sheets, but when I do it naturally they misalign. the sheets have multiple data under each number, which is what I want to match by (the 1, 2, 3, 4, 5 here) but the data that accompanies these numbers (here, the states and names), don't match up between the two sheets so I want to see differences.

Edit:

example data 2

if data looks like example 2, with "eric" appearing both under 1, and 2 – is there a way to make it populate with the correct $ amount for each? instead of populating the pay with the first instance of "eric" (under 1)?

Best Answer

1) Generate a list of all unique values in columns A and C

2) In I1, use the following formula:

=IFERROR(VLOOKUP(H1,A:B,2,FALSE),"")

And populate down

3) in K1, use this formula:

=IFERROR(VLOOKUP(H1,D:E,2,FALSE),"")

And, again, populate down

You should get something like this:

enter image description here