You must read this article first: Grouping Text in Pivot Tables. This article explains how to combine multiple text items into a single group. This is used for classifying or for correcting spelling mistakes. If new items appear, they remain ungrouped. That way, you have to keep doing the Grouping update repetitively. An alternative is to use VLOOKUP based text grouping. Read on to find out how this is done.
Contents
Correcting spelling mistakes using Pivot
Work on a field which is text based. Choose multiple items, Right click and select Group option.
A new field is added. This field will have the name Product2 because it was derived from the Product field. The selected items will be called Group1 – because Excel does not know what name you want to specify for the newly created group. You can type the desired caption and overwrite the default Group1 caption.
Now right click inside the original Product column and choose Remove. The grouping is now applied automatically. The numbers from the three items will be combined using the current summary operator (Sum, Count, etc.)
That’s it. Exactly the same approach is used for classifying items. For example, select all the drinks and group them as alcoholic beverages.
Disadvantages of ad-hoc grouping
This method looks very powerful. But in reality it is useful only for one-off corrections or classification. In real life, you will continue to receive more data in future. More mistakes will also be added in future. At that point of time, readjusting existing groups by adding new items becomes very cumbersome.
That is why it is better to use a VLOOKUP based approach. This way, we can clean up the data at raw data level rather than Pivot Level. In addition, incorporating future mistakes or unclassified items can also be managed easily.
VLOOKUP based text grouping
Now let us consider the data where we have the actual name of the product but we want to classify or categorize the actual products based upon some categories and subcategories.
For example, the data below needs to be classified as various types of alcoholic beverages. Beer, Spirit and Wine. Of course, it could have been done using manual grouping as we saw above. But let us see the advantage of using VLOOKUP based text grouping.
The first step is to create a separate master file containing all the products in the first column and further levels of categorization or classification in subsequent columns. Convert this to a table and specify a name for the table.
Now go back to the raw data and add a new column. Use VLOOKUP on the product name to get the level of classification you want.
This is called Vlookup based text grouping. This provides you long-term benefits.
Now next time, if you append more data and there is a new and unclassified item in it, the output will show #N/A. In that case you just have to add that item to your master table and classify it once. The same approach works with spelling variations or spelling mistakes.
Related articles
Vlookup based text grouping is just one of a series of articles covering various types of grouping scenarios with Pivot Tables. Read the entire series here:
Data Grouping: Knowledge Pack (9 articles)
2 Responses
Vlookup value is 3AV621????ADA??A with data 3AV6218001ADA22A how it works
Vlookup supports wildcard characters ? and *
? represents a single character (any character)
* represents multiple characters
A?B* will search for all items starting with A, with any character in second position, B as the third character, followed by any number of characters.
The pattern you have shown above works because of the ? character.