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.


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

  • 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:


And populate down

3) in K1, use this formula:


And, again, populate down

You should get something like this:

enter image description here

  • 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

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.