0

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

Improve this question
2
  • Do you have a list of all unique items in columns A and C? if yes, VLOOKUP could do the job
    – cybernetic.nomad
    Commented Jan 11, 2019 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
    Commented Jan 11, 2019 at 16:23

1 Answer 1

Reset to default
0

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

Improve this answer
3
  • 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
    Commented Jan 11, 2019 at 18:19
  • You may want to read this to help you create a Minimal, Complete and Verifiable Example
    – cybernetic.nomad
    Commented Jan 11, 2019 at 18:57
  • got it - thank you for the links. ive attached a smaller dataset with my problem
    – user983902
    Commented Jan 11, 2019 at 19:22

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .