How to effectively assign many items to multiple categories

%3CLINGO-SUB%20id%3D%22lingo-sub-1581065%22%20slang%3D%22en-US%22%3EHow%20to%20effectively%20assign%20many%20items%20to%20multiple%20categories%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1581065%22%20slang%3D%22en-US%22%3E%3CP%3EPlease%2C%20I%20have%20thousands%20of%20items%20in%20Excel%20-%20products%20for%20eshop%2C%20in%20separate%20rows%2C%20to%20which%20I%20need%20to%20assign%20more%20categories%20in%20the%20category%20column%20-%20from%20the%20total%20number%20of%20about%20120%20categories%20with%20subcategories.%20Each%20item%20will%20be%20marked%20with%20multiple%20categories%2C%20in%20woocommerce%20format.%20Categories%20are%20separated%20by%20a%20comma%20and%20the%20path%20to%20the%20subcategory%20is%20indicated%20by%20the%20%22%26gt%3B%22%20character%2C%20example%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20width%3D%22455%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2242%22%3Esku%3C%2FTD%3E%3CTD%20width%3D%22136%22%3Ename%3C%2FTD%3E%3CTD%20width%3D%22277%22%3Ecategory%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1245%3C%2FTD%3E%3CTD%3ESurface%20Studio%202019%3C%2FTD%3E%3CTD%3Edesktops%2C%20microsoft%2C%20computers%20%26gt%3B%20all%20in%20one%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%3CSTRONG%3EThe%20question%20is%2C%20how%20to%20do%20this%20as%20effectively%20as%20possible%3F%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1581065%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1581838%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20effectively%20assign%20many%20items%20to%20multiple%20categories%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1581838%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F729591%22%20target%3D%22_blank%22%3E%40temporalnaut%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20Temp%2C%3C%2FP%3E%3CP%3EWithout%20understanding%20how%20your%20category%20is%20input%20currently%20(by%20hand%3F)%2C%20my%20initial%20thought%20is%20for%20you%20to%20set%20up%20individual%20columns%20for%20each%20category%2C%20desktop%2C%20microsoft%2C%20etc.%20Create%20as%20many%20columns%20as%20the%20maximum%20entry%20will%20use%2C%20then%20place%20a%20list%20using%20data%20validation%20in%20each%20cell%20of%20each%20column.%20Data%20validation%20will%20drag%20and%20drop.%20Go%20to%20each%20item%20and%20select%20their%20categories%20across%20the%20row%2C%20include%20a%20subcategory%20column%20and%20select%20the%20subcategory%20as%20well%2C%20and%20then%20concatenate%20them%20in%20the%20final%20%22category%22%20row%20in%20the%20format%20you%20described.%20For%20the%20data%20validation%2C%20you%20can%20create%20one%20list%20for%20the%20categories%20and%20one%20list%20for%20the%20subcategories.%20So%20using%20your%20example%2C%20if%20you%20created%20four%20columns%2C%20the%20first%203%20with%20the%20categories%20listed%2C%20and%20the%20fourth%20with%20the%20sub-category%20listed%2C%20if%20the%20item%20was%20in%20cell%20b2%2C%20in%20cell%20%22c2%22%20you%20would%20select%20%22desktops%22%2C%20in%20%22d2%22%20you%20would%20select%20%22microsoft%22%20etc.%20In%20the%20category%20column%20(%22g2%22%20in%20this%20ex.)%20you%20would%20have%20%3Dconcatenate(c2%26amp%3B%22%2C%20%22%26amp%3Bd2%26amp%3B%22%2C%20%22%26amp%3Be2%26amp%3B%22%20%26gt%3B%20%22%26amp%3Bf2).%20(You%20can%20also%20put%20the%20comma-space%20in%20a%20cell%20and%20reference%20it%20instead%20of%20writing%20it%20out).%20Also%2C%20putting%20the%20comma-space%20in%20its%20own%20cell%20gives%20you%20the%20option%20of%20an%20if%20statement%20to%20filter%20it%20out%20so%20you%20don't%20have%20something%20that%20looks%20like%20%22desktops%2C%2C%2C%20%26gt%3B%22%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1582004%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20effectively%20assign%20many%20items%20to%20multiple%20categories%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1582004%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F729591%22%20target%3D%22_blank%22%3E%40temporalnaut%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESome%20things%20which%20are%20obvious%20for%20you%20are%20not%20clear%20for%20us.%20Better%20to%20submit%20small%20sample%20file%20(few%20records)%20to%20illustrate%20how%20the%20data%20is%20organized.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1582246%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20effectively%20assign%20many%20items%20to%20multiple%20categories%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1582246%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAt%20the%20moment%20I%20do%20not%20have%20a%20tree%20of%20categories%20in%20its%20final%20form%20and%20I%20would%20prepare%20it%20according%20to%20what%20you%20advise%20me.%20But%20I'm%20sending%20a%20sample%20file%20to%20give%20you%20an%20idea%20of%20what%20I%20mean.%26nbsp%3BThere%20is%20a%20sheet%20of%20products%20and%20then%20categories.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1582255%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20effectively%20assign%20many%20items%20to%20multiple%20categories%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1582255%22%20slang%3D%22en-US%22%3EI%20will%20definitely%20try%20it%2C%20thank%20you%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1582281%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20effectively%20assign%20many%20items%20to%20multiple%20categories%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1582281%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F729591%22%20target%3D%22_blank%22%3E%40temporalnaut%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI'd%20suggest%20to%20denormalise%20source%20data%20as%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20571px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F211820i9F1051C585FC6B2E%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EFirst%20table%20is%20SKU%20with%20names%2C%20another%20one%20SKU%20with%20categories.%20With%20that%20they%20could%20be%20merged%20in%20resulting%20table%2C%20preferably%20with%20Power%20Query.%20In%20second%20table%20SKU%20and%20categories%20could%20be%20selected%20from%20drop-down%20lists.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1583834%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20effectively%20assign%20many%20items%20to%20multiple%20categories%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1583834%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20to%20create%20drop-down%20lists%20so%20that%20the%20selected%20value%20is%20plain%20text%20suitable%20for%20csv%20and%20for%20import%20into%20woocommerce%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1584850%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20effectively%20assign%20many%20items%20to%20multiple%20categories%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1584850%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F729591%22%20target%3D%22_blank%22%3E%40temporalnaut%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EDrop-down%20list%20works%20only%20in%20Excel%20(xlsx)%20file.%20CSV%20is%20actually%20text%20file%2C%20you%20may%20add%20drop-down%20list%20while%20it%20is%20opened%20in%20Excel%2C%20but%20it%20will%20be%20lost%20as%20soon%20as%20you%20save%20the%20file%20in%20csv%20format.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

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:

 

skunamecategory
1245Surface Studio 2019desktops, microsoft, computers > all in one

The question is, how to do this as effectively as possible?

7 Replies
Highlighted

@temporalnaut 

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,,, >" 

Highlighted

@temporalnaut 

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.

Highlighted

@Sergei Baklan 

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.

Highlighted
I will definitely try it, thank you
Highlighted

@temporalnaut 

I'd suggest to denormalise source data as

image.png

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.

Highlighted

@Sergei Baklan 

How to create drop-down lists so that the selected value is plain text suitable for csv and for import into woocommerce?

Highlighted

@temporalnaut 

Drop-down list works only in Excel (xlsx) file. CSV is actually text file, you may add drop-down list while it is opened in Excel, but it will be lost as soon as you save the file in csv format.