Excel Question: VLOOKUP and beyond?
January 15, 2015 9:31 AM Subscribe
How can I combine these two tabs of data in Excel using VLOOKUP and/or some other function?
I have two tabs; there are a number of columns on each sheet so I'm only sharing the ones that I think are relevant below.
The first tab has data for appointments, one line for each date each person had an appointment (with unique ID for each person&date). Some people have more than one appointment, others do not.
Smith,1/1/2015, Smith01012015
Smith,1/12/2015, Smith01122015
Kringle, 1/4/2015, Kringle01042015
Jones, 1/2/2015, Jones01022015
Jones, 1/3/2015, Jones01032015
The second tab has data for payments, one line for each thing billed on each date each person had an appointment. Some people have multiple things billed on each date and some only have one.
Smith, 1/1/2015, Smith01012015, $300
Smith, 1/1/2015, Smith01012015, $400
Smith, 1/1/2015, Smith01012015, $25
Smith,1/12/2015, Smith01122015, $500
Smith,1/12/2015, Smith01122015, $50
Kringle, 1/4/2015, Kringle01042015, $85
Jones, 1/2/2015, Jones01022015, $600
Jones, 1/2/2015, Jones01022015, $10
Jones, 1/3/2015, Jones01032015, $325
What is the best way to pull together this data into one tab that has the total payments for each date each person was seen? Desired final product will look like this:
Smith,1/1/2015, Smith01012015, $725
Smith,1/12/2015, Smith01122015, $550
Kringle, 1/4/2015, Kringle01042015, $85
Jones, 1/2/2015, Jones01022015, $710
Jones, 1/3/2015, Jones01032015, $325
I used a VLOOKUP to pull the $ data from tab 2 to tab 1, but it only would pick up the first line for each person/date. Other than the person's name and the appointment date, there is no other field that is on both tabs.
I thank you for any help you can provide.
I have two tabs; there are a number of columns on each sheet so I'm only sharing the ones that I think are relevant below.
The first tab has data for appointments, one line for each date each person had an appointment (with unique ID for each person&date). Some people have more than one appointment, others do not.
Smith,1/1/2015, Smith01012015
Smith,1/12/2015, Smith01122015
Kringle, 1/4/2015, Kringle01042015
Jones, 1/2/2015, Jones01022015
Jones, 1/3/2015, Jones01032015
The second tab has data for payments, one line for each thing billed on each date each person had an appointment. Some people have multiple things billed on each date and some only have one.
Smith, 1/1/2015, Smith01012015, $300
Smith, 1/1/2015, Smith01012015, $400
Smith, 1/1/2015, Smith01012015, $25
Smith,1/12/2015, Smith01122015, $500
Smith,1/12/2015, Smith01122015, $50
Kringle, 1/4/2015, Kringle01042015, $85
Jones, 1/2/2015, Jones01022015, $600
Jones, 1/2/2015, Jones01022015, $10
Jones, 1/3/2015, Jones01032015, $325
What is the best way to pull together this data into one tab that has the total payments for each date each person was seen? Desired final product will look like this:
Smith,1/1/2015, Smith01012015, $725
Smith,1/12/2015, Smith01122015, $550
Kringle, 1/4/2015, Kringle01042015, $85
Jones, 1/2/2015, Jones01022015, $710
Jones, 1/3/2015, Jones01032015, $325
I used a VLOOKUP to pull the $ data from tab 2 to tab 1, but it only would pick up the first line for each person/date. Other than the person's name and the appointment date, there is no other field that is on both tabs.
I thank you for any help you can provide.
Best answer: You almost certainly want a pivot table. Ignore Tab 1 entirely, create a Pivot Table on the Tab 2 data where the rows are Column 3 from Tab 2, and the values is the money column. That should work fine.
The other option -- and this is not ideal -- is to use sumifs. The formula would go in column 4 on Tab 1 and look like:
=sumifs(MoneyColumnTab2,PersonColumnTab2,CurrentRowPersonColumnTab1,DateColumnTab2,CurrentRowDateColumnTab1)
which translates to: give me a sum of MoneyColumnTab2, if PersonColumnTab2 is equal to the current value of Person in this row, and if DateColumnTab2 is equal to the current value of Date in this row. You could also just do it on the concatenated column instead of doing those 2 columns separately.
Sumifs are pretty processor intensive and nowhere near as neat or reliable as a pivot table, so try to make the pivot table work, it's the desired way to do this.
posted by brainmouse at 9:43 AM on January 15, 2015
The other option -- and this is not ideal -- is to use sumifs. The formula would go in column 4 on Tab 1 and look like:
=sumifs(MoneyColumnTab2,PersonColumnTab2,CurrentRowPersonColumnTab1,DateColumnTab2,CurrentRowDateColumnTab1)
which translates to: give me a sum of MoneyColumnTab2, if PersonColumnTab2 is equal to the current value of Person in this row, and if DateColumnTab2 is equal to the current value of Date in this row. You could also just do it on the concatenated column instead of doing those 2 columns separately.
Sumifs are pretty processor intensive and nowhere near as neat or reliable as a pivot table, so try to make the pivot table work, it's the desired way to do this.
posted by brainmouse at 9:43 AM on January 15, 2015
Response by poster: Ugh, I forgot to mention one thing: tab 1 and 2 don't have all the same rows. All of tab 2 is on tab 1, but there's extra rows on tab 1 that aren't on tab 2. A pivot table would be the answer if both tabs had all the same rows, but where do I go from here?
posted by ThePinkSuperhero at 9:44 AM on January 15, 2015
posted by ThePinkSuperhero at 9:44 AM on January 15, 2015
What kind of data is on tab 1 that isn't on tab 2?
Is this something you need to do once or regularly?
posted by jeather at 9:48 AM on January 15, 2015
Is this something you need to do once or regularly?
posted by jeather at 9:48 AM on January 15, 2015
Response by poster: Sorry, I got it backwards- there are rows on tab 2 that aren't on tab 1. I need data from both tabs but only for the rows on tab 1. So I need to filter out all the rows that aren't on both tabs.
Right now, this is a one-off task.
posted by ThePinkSuperhero at 9:52 AM on January 15, 2015
Right now, this is a one-off task.
posted by ThePinkSuperhero at 9:52 AM on January 15, 2015
Best answer: So make the pivot table from tab 2 (using the unique name/date code), then vlookup from tab 1 into the pivot table.
posted by jeather at 9:53 AM on January 15, 2015 [1 favorite]
posted by jeather at 9:53 AM on January 15, 2015 [1 favorite]
Best answer: I might do a vlookup onto Tab 2 that was something like =if(iserror(vlookupformula),0,1) -- which will return a 0 if the vlookup gives an error (so the row isn't on tab 1), and a 1 if the vlookup doesn't (so the row is on tab 1) -- then put that column as a filter into your pivot table and only look at the 1s.
posted by brainmouse at 10:11 AM on January 15, 2015
posted by brainmouse at 10:11 AM on January 15, 2015
Response by poster: Ah ha! jeather got it! Pivot table, then the VLOOKUP. You are all the best. I'm not giving you the credit when I impress my boss, though :D
posted by ThePinkSuperhero at 10:14 AM on January 15, 2015
posted by ThePinkSuperhero at 10:14 AM on January 15, 2015
If you're feeling adventurous, SUMIFS would have also been your friend here.
Create a new tab, then copy/paste your list of names and dates (but not amounts). Use the Data->Remove Duplicates function to give you a list of unique names & Date combos. Finally, use the SUMIFS function to return the sum of lines where the names and dates match your selection criteria.
posted by Gee Your Hair Smells Terrific at 12:20 PM on January 15, 2015
Create a new tab, then copy/paste your list of names and dates (but not amounts). Use the Data->Remove Duplicates function to give you a list of unique names & Date combos. Finally, use the SUMIFS function to return the sum of lines where the names and dates match your selection criteria.
posted by Gee Your Hair Smells Terrific at 12:20 PM on January 15, 2015
This thread is closed to new comments.
You don't need tab one at all, just make a pivot table out of tab two. I'd probably concatenate the first 3 columns into one, so you are making the table out of two columns -- they will need header lines, btw -- but it's exactly the thing you use a pivot table for.
posted by jeather at 9:38 AM on January 15, 2015