Locate Duplicate SKU's and extract data

[colarguns]

Hi,


I am attaching an example file, to assist in the explanation. The workbook contains (3) worksheets. One sheet is titled "PRODUCTS" and the others are "INVENTORY SKUS", and "PRICE". The PRODUCTS sheet contains all products offered, and the INVENTORY SKUS sheet contains all SKU's available, that have inventory amounts by sku, and the PRICE sheet contains pricing by SKU. The supplier we use, does not make this easy for us, so we have to FIND the products in their PRODUCTS sheet, that match the SKU's from the Inventory Sku's sheet.

The main objective, is to pull all products that are available, based on the INVENTORY SKU sheet. The reason for this, is because the Inventory SKU sheet has a column titled "Programmed", which tells us that the products marked with "y" in that column are available to advertise. The PRODUCTS sheet is roughly 80,000 rows, and has 101 columns of data. This is a minimized version, so as not to be too large. I am looking for a more automated approach to this, as we will be doing this daily.

The supplier's product file is only updated once every two months, and the supplier's inventory is updated daily. For this example, we are only using one category of product (rugs), to provide us with a basis on how we need to approach this. We tried conditional formatting, to find duplicates on these sku's in the product sheet, but due to the file being so large, Excel was not finding all duplicates. We tried using the Power Query, but need more training, as we are new to that add-in.

To be more organized in thought, we would do the following:
1. Go to Inventory sheet, filter the product we wish to sell, and filter the column programmed to "Y".
2. Go to the Products Sheet, paste the sku's from the "Inventory Sku's worksheet into column "A", and find duplicate SKU's in column "B" that match the ones we pulled from the Inventory sheet.
3. Extract the products from the Products sheet into the "Final Results" worksheet, that matched the SKU's we pulled from the Inventory sheet.
4. Then we need to associate a price for each product, which is going to be the price sheet. Here we have to find the duplicate sku's once again!
5. Then apply the pricing (MAP and MSRP) columns, to the Final Results worksheet.

Hopefully this all made sense. I tried to keep it short and sweet. If we can get help on this, I will sell (at cost), any product you wish, that we offer on our site. We sell Home Furnishings and Decor, as well as Outdoor furnishings. We would like to have more of an automated process, with all these files. On the actual product xls file we receive, they have about 15 worksheets in one workbook, that have different product categories. So for this example, we are only using rugs.

Thank you!