Count values in one column, compared to other columns

My goal is to isolate rows by counting possible values in one column when compared to other columns.

I have about 1.5 million individual rows of data and I need to find out which ones have more than one value in column F.

Basically, in the attached example, I want a count of unique cells in column F (which may be variable) that match column A & B & C & D (which will be constant).

It seems easy, but I can't wrap my head around this one...

Please help.

Hi

And what would be the correct result for ID 1501645 (but only because all the items in B, C, D are the same)? 2 perhaps?

rylo

Correct. And for the second series (BRAKE COMPONENTS) there should be 3, (STRUTS & COMPONENTS) would be 2 and the last one (EVAPORATOR & HEATER COMPONENTS) is 1.

Hi

This is a cumbersome process, but should get you there.

Using your example file:
H1:L1 has the values ID, SECTION, GROUP, SUBGROUP AND ILLUSTRATION.
Select the range A:F, advanced filter, unique, copy to another range, and output to H1:L1 - this should give you unique instances of the items you want.
Then select columns H:K, advanced filter, unique, another location, and output to N1. This should give you a unique list of the 4 items you want to group.
R2: =SUMPRODUCT(--($H$2:$H$9=N2),--($I$2:$I$9=O2),--($J$2:$J$9=P2),--($K$2:$K$9=Q2)) Copy down to R5

As I said, this is for your example file, and the ranges will have to be expanded. Hopefully it will get you there.

rylo

Hi

Another way with a UDF.

Open a general module in your workbook, and put in the code

Please Login or Register  to view this content.

G2: =myfunc($A$2:$A$37,A2,B2,C2,D2)
G3: =IF(AND(A3=A2,B3=B2,C3=C2,D3=D2),"",myfunc($A$2:$A$37,A3,B3,C3,D3))
Copy from G3 down to G27

HTH

rylo

rylo, thank you. The second answer is what I needed. I literally have 1.5 million rows that I need to touch. That works great.

The only thing I won't need is the formula on G3. I want to see the count on each line. You're a pro. Thanks again.