SKU Matching Made Easy

SKU Matching Made Easy

How can I easily match products in the shop with an external list?

In product management, you often receive external lists from manufacturers with the current product range. In your own shop, you should then check which products need to be deactivated or even deleted. Here, we show you two ways to quickly accomplish this with cobby by filtering for specific attributes, applying the xlookup, and deactivating or activating the corresponding products all at once instead of updating each product individually.

In the demo, you can read everything in the step-by-step guide. Good luck!

Demo

Step-by-Step Guide

Option 1: cobby Tool and cobby Filter in use (for discontinued products)

In this case, we have received a list of discontinued products from the manufacturer, which includes the corresponding SKUs. This manufacturer list is to be compared with our products in the shop so that the products that are now being discontinued can be removed from the shop.

Advantages:

• time savings through simple filter function
• many products can be deactivated at the same time

In the following, you will learn how to:

• load an external list and your cobby products
• filter for the discontinued products in cobby
• deactivate them

1. First, we open the external list. In our case, it is a list of SKUs that will be discontinued, which we want to deactivate.

2. We would like to immediately take advantage of the cobby filter and only load the products that are currently in the external list from our shop into Excel. However, we first need to load products into Excel so that we can use the cobby functions.

3. The products and attribute sets have been successfully loaded. Go back to your spreadsheet with the external list for further processing.

4. To deactivate these discontinued SKUs in your shop, first mark all the SKUs.

5. Then click on "Copy selection for filter" using the tools button.

6. Now we would like to load the copied products from the shop into Excel.

• To do this, we go to the "AllProducts" worksheet, click on "Load Products" and then on the small arrow below it, and select "Filter..."
• Don't worry, the worksheet with the external list won't disappear!

[You can of course also work with two different workbooks. Learn more about it here in our docs.]

7. Paste the copied SKUs into the filter. Then click "Apply Filter" below.

​💡 Please make sure that when you load products in the "Reduced View," only active shop products are loaded. If you also want to see the disabled products, you need to uncheck the box for "only active products" in the filter.

[If you want to learn more about the filter, check here in the course.]

This way, only the filtered SKUs are loaded, so you have all the discontinued products at a glance.

8. Set the status in the attribute "Enable Product" to Disabled to deactivate the products. Apply this to all products.

9. Finally, click on "Save Products" to apply your changes.

Option 2: xlookup

(1) xlookup - discontinued products

In this case, we received a large list of discontinued products from the manufacturer, which includes the corresponding SKUs. We always recommend using Excel formulas for large lists. This manufacturer list should be compared with our products in the shop so that the products that are now being discontinued can be removed from the shop.

Advantages:

• time savings with a high number of discontinued products

In the following, you will learn how to:

• use the xlookup formula to filter for discontinued products
• hide irrelevant values using the Excel filter
• deactivate the discontinued products

1. First, we switch from the external list to the AllProducts worksheet. Then we create our own view for editing. This is simply for easier editing and clarity.
[If you'd like to know how this works, learn more about it here in this article.]

2. Add an auxiliary column then.
​ [Learn how to add auxiliary columns here in this article.]

3. Enter =xlookup in the first cell of the auxiliary column. Then double-click on the blue suggested formula to select the formula.

4. Click on fx to open the function field. A window will then open with the three required function parameters that need to be filled.

5. The search criterion is the SKU. 

• Click in the field for the search criterion.
• Select the SKU in C3 by clicking on the corresponding cell in the table.

(Remember this procedure, you will need it for every parameter.)

6. For the search matrix, we first go to the bottom of the spreadsheet with the discontinued products and select column A.

7. The return matrix is the same as the search matrix, as we only want to return one value. Therefore, copy the value from the search matrix to the return matrix.

8. When #N/A appears, it means that no value was found. Therefore, the SKU is not included in the list of discontinued products. However, we apply the formula as usual to all SKUs.

9. Then click the small arrow in the upper right corner of the help column (next to the column name) to open the Excel filter. In the selection, remove the checkmark next to #N/A so that these SKUs are no longer displayed. The remaining SKUs are on our discontinuation list and are needed later.

10. Set the first product in the "Enable Product" column to Disabled. Repeat this for all remaining products by dragging the cell down or by double-clicking the square to apply it to the entire column.

11. Finally, click on "Save Products" to apply your changes.

(2) xlookup - current product range

In this case, we have received a list of the current product range from the supplier. Therefore, all other products are not available. So we don't want to offer any other products from this manufacturer in our shop anymore.

Advantages:

• time savings when updating product status if only the current product range is known

In the following, you will learn how to:

• use the XLOOKUP formula to search for the current products according to the list and filter out all other products
• deactivate products that are no longer part of the current range

1. First, switch from the supplier list to the AllProducts worksheet. Then, use the cobby filter to filter out the supplier "CobbyCraft." Note: We have also unchecked the box for "only active products" here.

2. First, we create our own product view for editing (hide irrelevant columns, create an auxiliary column).

3. Enter "=xlookup" in the first cell of the auxiliary column. Then double-click on the blue suggested formula to select the formula.

4. Then click the function key at the top. A window will open with the function parameters search criterion, search matrix, and return matrix, which all three need to be filled.

5. The search criterion is the SKU that you select by clicking in the field (e.g., C3).

6. The search matrix is then located in the list "Current Selection-CobbyCraft". Click on column A there.

7. The return matrix is again the same as the search matrix, as we have no other value. Since the product was found in the external list, the SKU will be displayed to you as the value - keyword return matrix.

8. Now apply the formula to all other products.

If a SKU is displayed, it means that the product is present on the list. In the case of #N/A, no value was found, so the SKU is not on the list.

9. Since we only want to display all SKUs that are no longer listed on the manufacturer list, we will deselect all SKUs in the first step.

• To do this, we click on "(Select All)". 
• Scroll down and only check the box for #N/A, where no value was found, and click OK.

We no longer want to offer the displayed products in our shop, which is why we now need to set all products (some still have the status set to Enabled) to Disabled.

10. Set the first SKU to Disabled and apply this to all other products.

11. Finally, click on "Save Products" to apply your changes.

Now, of course, you can also check in the opposite direction to see if all the products listed on the manufacturer's product list are also set to Enabled in our system. To do this, you simply need to click on "(Select All)" again in the Excel filter and uncheck "NV."

(3) xlookup - available/not available

In this case, we received a list from the supplier of available and unavailable products, which also includes the corresponding SKUs. This supplier list is to be matched with our product data in the shop so that the products that are no longer available are removed from the shop.

Advantages:

• quick comparison of one's own product range and manufacturer lists regarding delivery status

In the following, you will learn how to:

• use the XLOOKUP formula to compare a manufacturer list and your product range regarding specific parameters such as availability
• apply the Excel filter to filter for the relevant product data
• activate/deactivate products in your shop

1. First, we switch from the supplier list to the AllProducts spreadsheet. Here, we filter using the cobby filter for the supplier FusionCobby and click on "Apply Filter." Note: We have removed the checkmark for "only active products."

2. Create your own product view for editing (hide irrelevant columns, add a helper column). For verification, you can display the attribute "Manufacturer".

3. Apply the xlookup formula and then click on the function bar fx. A window will open with the three relevant function parameters search criteria, search matrix, and return matrix.

4. The search criterion is the SKU.

5. We select the search matrix from the manufacturer list. Choose column A there.

6. The return matrix here is column B (Status) in the supplier list.

7. In the first row, the value for the SKU is then displayed: the product is available. We then apply this to all remaining products so that you can see the delivery status of all products.

8. Using the Excel filter, we will now filter for all available products. We want all available items to have the status set to Enabled. To check this, one could scroll through the entire list. However, with many products, the list would be very long, which is why there is a better alternative...

9. ... we use the Excel filter in the Enabled column once again. Here, you can see right away that there are no Disabled items - so all listed products must be set to Enabled. Perfect! (Otherwise, you could filter out the Disabled products and set their status to Enabled.)

10. After that, we will check to see if all "unavailable" products in our product range are set to Disabled. In this case, you will see that all products with the status "No longer available" are still set to Enabled. Since they are no longer available, we will set them to Disabled so that they are no longer visible in the shop.

11. We apply the change again to all visible products. Thus, all products are set to inactive.

12. Then click on "Save Products".

13. Finally, we filter for #NV. These are products that were not on the manufacturer list but are still in our product range.​

With the help of cobby, you can see here that the supplier forgot to list two products that we still offer and are available in the shop.

As a shop owner, you should ask the manufacturer in such a case whether the products are still available. Otherwise, the status should be set to Disabled!

Excellent! 🎉

Now you know how to match your product data with manufacturer lists using the cobby filter or the xlookup, and for example, deactivate products that are no longer available in your shop.