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 …
Best Answer
Use
IMPORTRANGE()
to import data andVLOOKUP()
to find match between Product Section.Here's a sample :
First you need to import data from another sheet with
IMPORTRANGE()
To find match between sections use VLOOKUP
A3:B10
is the range containing price and section 1 labels from sheet 1D3:D10
is section 2 labels range from sheet 2E3:E10
is the price column from sheet 2https://docs.google.com/spreadsheets/d/1lQ8vwvrzvo-kg-FkeZVapeUHoebf8t6ISbHD2kCMc7w/edit#gid=0
is my spreadsheet