Align two columns in excel based on 1 criteria

This has me stumped.

if you have data that looks like `ABCD`:

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:

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)?

• Do you have a list of all unique items in columns `A` and `C`? if yes, `VLOOKUP` could do the job – cybernetic.nomad Jan 11 at 15:20
• i dont; i am confused about how to compare the two, since down the column for company 1, that 1 criteria needs to match constantly so it only compares data that falls under the 1, for example. and its a data sheet of +10000 rows so using A\$1 and manually changing that is impossible. also, not sure about inserting this missing data below the necessary number – user983902 Jan 11 at 16:23

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:

• this works for unique cases, so thank you. is there a way to make it work, if the name sally was in both H3 and H5, so it would grab the correct salary? or if it were in h9? i should have had an example like that in my example data – user983902 Jan 11 at 18:19
• You may want to read this to help you create a Minimal, Complete and Verifiable Example – cybernetic.nomad Jan 11 at 18:57
• got it - thank you for the links. ive attached a smaller dataset with my problem – user983902 Jan 11 at 19:22