Jan 17 2021 08:13 AM
Jan 17 2021 08:13 AM
Okay, so I am an excel beginner. I know your very basics but that is about it. Here is my project issue.
I have a master list with over 4000 product sku numbers on it.
I have six companies that order from me and each company has its own excel sheet. On each of their excel sheets I have the product numbers for each product that they order and then I have the quantity and the price for each product that they have ordered for the past 6 years.
On my master list I want to match up what each company has bought just for the year ending in 2020. Is there a way for my master list to identify each product on each companies list and and add the quantity bought for each company. I tried vlookup but couldn't figure out how to only get the quantity for 2020 and have it match the product sku numbers on my master list.
Thank you so much!!!
Jan 17 2021 09:43 AM
An alternative to the Power Query solution that @Sergei Baklan suggested--not all of us have access to Power Query (I don't on my Macs)--is to rethink your workbook.
I worked not too long ago with an acquaintance whose small business has several hundred SKUs, and, like you, some six or seven major customers. We integrated ALL of his order records into a single table--i.e., every customer's orders mixed in with the others--by just adding a column that identified the customer ID or name.
I'm assuming your six distinct sheets are laid out in very much the same way, perhaps identically. It should be quite easy to copy and paste into a single consolidated database (ie., a table), ensuring first that you've included a column to identify each row of each transaction with the appropriate customer
Once you've done that, you can use the Pivot Table or possibly FILTER function, or SUMIF to extract very tightly defined summary reports by customer, by year, by category of SKU...whatever. It's simply a LOT easier to produce summary reports from a single table or database.
As Sergei noted, if you want more specific help, it would help if you could supply a sample of your spreadsheets....just don't include proprietary data.