1

Our bookstore has two separate documents in Google Sheets (one containing purchase data and one with sales data). Each document contains a sheet with a pivot table that sums up total purchases/sales by book category. I wanted to create a third sheet that shows the difference between the two (i.e. profit margin by category).

I was able to find the IMPORTRANGE function that can pull the data range from one sheet to another, but I cannot figure out how to generate a new pivot table showing the difference between the two tables.

Can someone explain to me how to do this? (Assume for the sake of simplicity that the two documents are named A and B and that the data is in a sheet called Sheet 1 in each of them, each with two columns Section and Total).

EDIT: Here are two screenshots of the pivot tables I'm trying to get the difference of. They are in separate documents, and Google Sheets will import them into a single document, saying that the data is too large ...

enter image description here

enter image description here

  • Can you provide some sample data with the kinds of result you expect ? – PaulJ Aug 13 at 20:05
  • I can't figure out how to embed tables in my post, but each sheet has two columns (Section and Total). The "Total" column is numeric (a dollar amount). I want to create a third table that is the difference between the "Total" field in the Sales and Purchases pivot tables for each individual section. So for instance, if I have Cooking: $7500 and Cats: $3000 in Sales sheet, and Cooking: $5500 and Cats: $1000 in Purchases I want Cooking: $2000 ($7500-$5500) and Cats: $2000 ($3000-$1000) in the new table. – Jesse T. Aug 13 at 21:30
  • I have added some screenshots of the tables if that helps – Jesse T. Aug 13 at 22:57
  • Correct me if i'm wrong but you just need : FICTION GENERAL : 66k-32k = 34k // and the difficulty is that the product sections (Col A) are not in the same rows sometimes. (I.E. pic 1 row 10 : Biography and Memoir // pic 2 row 7 : Biography and Memoir) if you confirm that this is the only problem, there is a quick solution – PaulJ Aug 14 at 7:28
  • @PaulJ - Yes, one problem I am having is that the same book categories are in different rows in each sheet. The other problem is that the sheets are in different documents, and I am having trouble understanding how to import the data from one sheet to the other. – Jesse T. Aug 14 at 16:27
1

Use IMPORTRANGE() to import data and VLOOKUP() to find match between Product Section.

Here's a sample :

First you need to import data from another sheet with IMPORTRANGE()

You need to accept the access between sheets when you use IMPORTANGE: The best is to do this with a sample of every sheets to be sure you allow access for each one.

To find match between sections use VLOOKUP

=ArrayFormula(
        ArrayFormula(
              VLOOKUP(D3:D10,IMPORTRANGE("https://docs.google.com/spreadsheets/d/1lQ8vwvrzvo-kg-FkeZVapeUHoebf8t6ISbHD2kCMc7w/edit#gid=0","Sheet1!$A$3:$B$10"),2,false))

-IMPORTRANGE("https://docs.google.com/spreadsheets/d/1lQ8vwvrzvo-kg-FkeZVapeUHoebf8t6ISbHD2kCMc7w/edit#gid=0","Sheet1!E3:E10"))

A3:B10 is the range containing price and section 1 labels from sheet 1
D3:D10 is section 2 labels range from sheet 2
E3:E10 is the price column from sheet 2
https://docs.google.com/spreadsheets/d/1lQ8vwvrzvo-kg-FkeZVapeUHoebf8t6ISbHD2kCMc7w/edit#gid=0 is my spreadsheet

  • Thank you! This didn't end up being exactly what I needed, but it was close enough that I was able to figure out something that works, so I'll mark as accepted answer. Messing around with it for my specific case, I ended up with this: =ArrayFormula(VLOOKUP(A2:A587, A2:B587, 2, False) - ArrayFormula(VLOOKUP(A2:A587, IMPORTRANGE("https://docs.google.com/spreadsheets/d/4Fkz9ByLvdNF4neiHcd4PyxSJbgIg5g_TWXLJG5A17yY/edit#gid=403418651","Purchases by section!$A$2:$B$587"),2,false))) in the Sales worksheet. Let me know if there is any way to simplify/improve this. – Jesse T. Aug 17 at 18:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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