Hi,
I have a small file that I can attach, if needed. It is about 400kb in size. I have tried multiple different angles on this work, but just can't seem to get this working correctly. 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 from the "Products" sheet, 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.
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. 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!
I have a small file that I can attach, if needed. It is about 400kb in size. I have tried multiple different angles on this work, but just can't seem to get this working correctly. 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 from the "Products" sheet, 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.
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. 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!
