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

 

[Micron]

Or just apply conditional formatting on sorted B values without the helper column(s)?

CF rule: =AND($B2=$B3,$D2<>$D3)

Not sure what the effect would be if you had 1,1,1 and Fruit, Vegetable, Grain
Edit -
Answer:

 

[2022]

Or just apply conditional formatting on sorted B values without the helper column(s)?
View attachment 98503
CF rule: =AND($B2=$B3,$D2<>$D3)

Not sure what the effect would be if you had 1,1,1 and Fruit, Vegetable, Grain
Edit -
Answer:
View attachment 98504

Thanks!

 

[2022]

I’d like to take this a bit further….

I need to isolate all the IDs that multiple classifications - so in the example above, I’d need to filter by all rows with the ID 1 and all rows with the ID 3.

Is there a way to do that, without manually selecting each ID?

 

[2022]

I responded to another solution just now.

But would also like to respond to the one I created above.

Is there a way to filter by all the IDs that have multiple classifications?

So in the data set above, is there a way to filter for all rows which contain ID 1 and ID 3 (which both have multiple classifications), without having to manually select each ID?

 

[mumps]

Try this macro:

Sub FilterProducts()
    Application.ScreenUpdating = False
    Dim v() As Variant, lRow As Long, i As Long, x As Long, rng As Range, srcRng As Range
    Dim dic As Object, arr() As Variant, cnt As Long, val As String
    lRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    v = Range("B2:B" & lRow).Resize(, 2).Value
    Set dic = CreateObject("Scripting.Dictionary")
    For i = LBound(v) To UBound(v)
        If Not dic.exists(v(i, 1)) Then
            dic.Add v(i, 1), Nothing
            Range("A1").AutoFilter 2, v(i, 1)
            Set srcRng = Range("D2:D" & lRow).SpecialCells(xlVisible)
            val = srcRng.Cells(1, 1)
            For Each rng In srcRng
                If rng = val Then cnt = cnt + 1
            Next rng
            If cnt <> [subtotal(103,A:A)] - 1 Then
                x = x + 1
                ReDim Preserve arr(1 To x)
                arr(x) = v(i, 1)
            End If
        End If
        cnt = 0
    Next i
    Range("A1").AutoFilter
    Cells(1, 1).Sort Key1:=Columns(2), Order1:=xlAscending, Orientation:=xlTopToBottom, Header:=xlYes
    Range("A1").AutoFilter Field:=2, Criteria1:=arr, Operator:=xlFilterValues
    Application.ScreenUpdating = True
End Sub

 

[2022]

Try this macro:

Sub FilterProducts()
    Application.ScreenUpdating = False
    Dim v() As Variant, lRow As Long, i As Long, x As Long, rng As Range, srcRng As Range
    Dim dic As Object, arr() As Variant, cnt As Long, val As String
    lRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    v = Range("B2:B" & lRow).Resize(, 2).Value
    Set dic = CreateObject("Scripting.Dictionary")
    For i = LBound(v) To UBound(v)
        If Not dic.exists(v(i, 1)) Then
            dic.Add v(i, 1), Nothing
            Range("A1").AutoFilter 2, v(i, 1)
            Set srcRng = Range("D2:D" & lRow).SpecialCells(xlVisible)
            val = srcRng.Cells(1, 1)
            For Each rng In srcRng
                If rng = val Then cnt = cnt + 1
            Next rng
            If cnt <> [subtotal(103,A:A)] - 1 Then
                x = x + 1
                ReDim Preserve arr(1 To x)
                arr(x) = v(i, 1)
            End If
        End If
        cnt = 0
    Next i
    Range("A1").AutoFilter
    Cells(1, 1).Sort Key1:=Columns(2), Order1:=xlAscending, Orientation:=xlTopToBottom, Header:=xlYes
    Range("A1").AutoFilter Field:=2, Criteria1:=arr, Operator:=xlFilterValues
    Application.ScreenUpdating = True
End Sub

Thanks for your repsonse, mumps.

I tried it and it partially worked. But it only returned one row with the '1' ID when it should have returned multiple rows with the '1' ID and also the '3' ID....

So it returned this:

Grocery Store	ID	Product	Classification		Checks?
Kroger	1	Apple	Fruit	CONSECUTIVE	Not ok

But was missing the other row with the 1 ID below and the 3 IDs....

Grocery Store	ID	Product	Classification		Checks?
Kroger	1​	Apple	Fruit	CONSECUTIVE	Not ok
Costco	1​	Apple	Vegetable
Tesco	2​	Pear	Fruit	CONSECUTIVE	Ok
Carrefour	2​	Pear	Fruit
Boots	3​	Kale	Vegetable	CONSECUTIVE	Not ok
Walmart	3​	Kale	Fruit
Carrefour	4​	Banana	Fruit	CONSECUTIVE	Ok
Tesco	4​	Banana	Fruit

 

[mumps]

This is what I get when I run the macro:

Book1
	A	B	C	D	E	F
1	Grocery Store	ID	Product	Classification		Checks?
2	Kroger	1	Apple	Fruit	CONSECUTIVE	Not ok
3	Costco	1	Apple	Vegetable
6	Boots	3	Kale	Vegetable	CONSECUTIVE	Not ok
7	Walmart	3	Kale	Fruit
Sheet1

If it is not working for you, could you upload a copy of your actual file (de-sensitized if necessary) 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.