Forum Discussion
How to effectively assign many items to multiple categories
Please, I have thousands of items in Excel - products for eshop, in separate rows, to which I need to assign more categories in the category column - from the total number of about 120 categories with subcategories. Each item will be marked with multiple categories, in woocommerce format. Categories are separated by a comma and the path to the subcategory is indicated by the ">" character, example:
| sku | name | category |
| 1245 | Surface Studio 2019 | desktops, microsoft, computers > all in one |
The question is, how to do this as effectively as possible?
7 Replies
Some things which are obvious for you are not clear for us. Better to submit small sample file (few records) to illustrate how the data is organized.
At the moment I do not have a tree of categories in its final form and I would prepare it according to what you advise me. But I'm sending a sample file to give you an idea of what I mean. There is a sheet of products and then categories.
Hi Temp,
Without understanding how your category is input currently (by hand?), my initial thought is for you to set up individual columns for each category, desktop, microsoft, etc. Create as many columns as the maximum entry will use, then place a list using data validation in each cell of each column. Data validation will drag and drop. Go to each item and select their categories across the row, include a subcategory column and select the subcategory as well, and then concatenate them in the final "category" row in the format you described. For the data validation, you can create one list for the categories and one list for the subcategories. So using your example, if you created four columns, the first 3 with the categories listed, and the fourth with the sub-category listed, if the item was in cell b2, in cell "c2" you would select "desktops", in "d2" you would select "microsoft" etc. In the category column ("g2" in this ex.) you would have =concatenate(c2&", "&d2&", "&e2&" > "&f2). (You can also put the comma-space in a cell and reference it instead of writing it out). Also, putting the comma-space in its own cell gives you the option of an if statement to filter it out so you don't have something that looks like "desktops,,, >"
- I will definitely try it, thank you
I'd suggest to denormalise source data as
First table is SKU with names, another one SKU with categories. With that they could be merged in resulting table, preferably with Power Query. In second table SKU and categories could be selected from drop-down lists.
