Home

Categories vs Sub-categories in my data set

Julia_
Occasional Visitor

Hello everyone

For my data set below, I'd like to be able to have a new column where I can see all rows with industry codes that start with "01" for example, and in the new column (cell C1 below) to have "Chemical" as the general industry. Right now I have sub-categories and a lot of times in a graph let's say, I want to analyze all data for the "Chemical" industry in general as opposed to looking at it by sub-categories. I hope my explanation is clear :). Thank you all in advance for your help. 

 

A1        B1
0101 chemical general
0101 chemical general
0102 petrochemical (incl. methanol and syngas)
0102 petrochemical (incl. methanol and syngas)
0201 life sciences general
0201 life sciences general
0202 active pharmaceutical ingredients (API) and additives
0202 active pharmaceutical ingredients (API) and additives

 

1 Reply

You can use the LEFT function, say in column C

=Left(A2,2) Will return the 01 or 02 digits code that refers to the category here.

Then, in another column, D, you need to use VLookup function to return the name of the category using the code. If you have another standalone table with the list of category codes in column 1 and names in column 2, you can use a formula like this:

=VLookup(C2,ReferenceTable,2,0)

where:

C2: is the cell you have with the LEFT function I explained above.

ReferenceTable: The range with the standalone table with the category codes and names.

2: is the column index number for the category names in the table above.

0: is the method of selection and searching which is "Exact match" that suits your data here.

If you do not have or cannot create such a standalone table with category codes and names, this will be hard to extract data from these cells, as we do not know if the category name consists of one word or two, therefore we cannot use a character to separate the category name.

Hope this helps...

Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Edge insider Dev bypasses IE mode website list
HotCakeX in Enterprise on
4 Replies