Need help extracting information from different worksheets onto 1 report

[jmorales]

I am struggling trying to find a solution for this problem.

Scenario... Excel file with three worksheets titled 2018, 2019, and 2020.

Each worksheet has the following columns... seller ID, seller name, total sales, total units.

The rows contain a bunch of customers with the above information.  Some customers are found on all three worksheets, some customers are only found on one or two worksheets.  Every year we will add a new worksheet with that year's data.

What I'm trying to get...  I need a report (pivot table, power bi, ?what ever) that will pull the following data.. customer ID, customer name and let's say the 2019 and 2020 total sales figures side by side.  Ideally, I would like an additional column with the differential between the two years figures.  Can anyone suggest something to do this comparison for me?

[#2]

Hello,

The best way to go about your data is to have a single sheet. Put all the years in a single column for example
In column A, have all the ID listed.
Column B, Date of Transaction ie. 01/12/2017
In column C, Seller Name.
Column D, Units
Column E, Price
Column F, Total

Then, you have extract the Year and Month from Column B into columns G and H using the formula
Column G, =YEAR(B2)
Column H, =TEXT(B2,"mmmm")

After doing all the above, you can easily use PivotTable to create a nice report. So easy!

[#3]

@jmorales A similar solution to what @Abiola1 suggested is demonstrated in the attached workbook. I've used the column headers as you specified. First add one column to each sheet with the year. Then put everything in one sheet and create a pivot table (as shown in the file). Add a "Calculated Item" for the difference between two years.