Classifications

[2022]

I have a spreadsheet where the classifications of some products are incorrect.

I've re-created a simple example below.

Context:

Column A has a list of Supermarkets that supply products.

Column B has the ID for those products.

Column C has the product description.

Column D has the classification.

In this instance, there are two products with multiple classifications.

In row 5, the classification for the Apple (product ID 1) is 'Vegetable' but it should only be fruit!

In row 7, the classification for the Kale (product ID 3) is 'Fruit' but it should only be Vegetable!

The real data has thousands of rows with instances like this, where an ID has multiple classifications, when each ID should only have ONE classification (and there are about a dozen classifications).

Is there a quick way to identify all the instances in the table where the ID column has MULTIPLE classifications in column D, but also find out what those multiple classifications are?

Grocery Store	ID	Product	Classification
Kroger	1​	Apple	Fruit
Tesco	2​	Pear	Fruit
Boots	3​	Kale	Vegetable
Costco	1​	Apple	Vegetable
Carrefour	2​	Pear	Fruit
Walmart	3​	Kale	Fruit

 

[mumps]

A macro could be designed to tell you if any product has multiple classifications but has no way of knowing which of the multiple classifications is the correct one unless you tell it. The only way I can think of doing that is to have a separate sheet which lists all the products in one column and the corresponding correct classification in the adjacent column. In this way, all the products and their classification in your data sheet can be compared to the list in the other sheet and if a product has a wrong classification, the correct one can be used to replace it. If this would work for you, it would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your two sheets. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[2022]

Thanks for your response, @mumps

I don’t need to identify the correct classifications, at this stage.

I just need to identify IDs that are listed multiple times with a classification that’s different.

So if 1 was listed multiple times in the data, but the classification was always ‘fruit’ that would be fine.

But if 1 was listed multiple times, but it had a ‘fruit’ classification in one row, then a ‘veg’ classification in another row, then a ‘pizza’ classification in another row, I’d want to filter for instances like that.

If 3 was listed over a thousand times in the data, but the classification was always ‘vegetable’ then I’d ignore that, because I’d know that all the supermarkets supplying it are indeed supplying a vegetable (kale).

Is that possible?

And would you still need me to upload a file ?

Thank you in advance.

 

[mumps]

And would you still need me to upload a file ?

It would make it easier to test a possible solution. How do you want to identify IDs that are listed multiple times? Do you want to highlight them? Please clarify in detail.

 

[2022]

It would make it easier to test a possible solution. How do you want to identify IDs that are listed multiple times? Do you want to highlight them? Please clarify in detail.

I wanted to just filter by the IDs where they have multiple incorrect classifications.

But highlighting them would work, as well, as then one can filter by colour.

Ok, I’ll upload a file tomorrow, as I’m using a mobile phone, at the moment. Thanks for your suggestions, so far!

 

[shakethingsup]

sounds like you have rules for IDS and classifications already? or am I missing something?

Do you have a list of unique IDs and their default correct classification? If you do, couldn't you just create helper columns and look up against this data set? and then filter?

=IF(D2=XLOOKUP(B2,G:G,H:H,"not found"),"correct","incorrect")

a	b	c	d	e	f	g	h
Grocery Store	ID	Product	Classification	Correct or not?		ID	Classification
Kroger	1	Apple	Fruit	correct		1​	Fruit
Tesco	2	Pear	Fruit	correct		2​	Fruit
Boots	3	Kale	Vegetable	correct		3​	Vegetable
Costco	1	Apple	Vegetable	incorrect
Carrefour	2	Pear	Fruit	correct
Walmart	3	Kale	Fruit	incorrect

 

[2022]

That’s a good question.

I don’t have a list of all the correct classifications, yet. But I want to identify the rows of data where IDs have more than one classification, at the moment, as they’re likely to cause issues with the output, because they have multiple classifications…..

 

[2022]

Actually, I’ve thought of a solution!

If you put in a formula in column H to check if the IDs in different rows are consecutive ie put this in column H =if(B3=B2,”Consecutive”,””)

then add this to column I ie =IF(H2="","",IF(AND(H2="CONSECUTIVE",D2=D3),"Ok","Not ok"))

You can then filter by everything that's not ok and you'll have your list of IDs which have multiple classifications (it won't return all rows with the different classifications, but it at least identifies the problematic IDs).

 

[2022]

Here's the data I tested it with

Grocery Store	ID	Product	Classification
Kroger	1​	Apple	Fruit
Costco	1​	Apple	Vegetable
Tesco	2​	Pear	Fruit
Carrefour	2​	Pear	Fruit
Boots	3​	Kale	Vegetable
Walmart	3​	Kale	Fruit
Carrefour	4​	Banana	Fruit
Tesco	4​	Banana	Fruit

 

[2022]

You have to sort the IDs into ascending order before you do the consecutive column checks, though!