Count Of Types

[ellison]

Hi, am trying to find a way of counting "variations".
The raw data is as per columns A&B, and I'm trying to do a count in Col C of the number of different types listed for each model.

Model	Type	Count-Of-Types
ABC123	black	1
54123	black	3
54123	14	3
54123	grey	3
54123	grey	3
54123	grey	3
54123	grey	3
XYZ789	white	3
XYZ789	red	3
XYZ789		3
XYZ789	12	3

At the moment, I use a long winded method of:
1. copy original info 2. dedupe 3. pivot table with counts of (only) the models 4. lookup back to the original info 5. hope I haven't left anything off any of the steps 6. try and get back my train of thought (oops!)

I normally use this on pretty big datasets, up to 100K, but there are only ever 2 fields.

The added complications seem to be:
i) although there is always a model, there isn't always a type listed - sometimes they are left blank).
ii) both the model and the type can be alphanuemeric strings, but also some are made entirely of digits and some are just letters

I'd be happy to use helper columns, code or whatever could help to make this a bit quicker / easier / foolproof (especially with my stickY fingers!)

Thanks for looking.

 

[Snakehips]

@ellison Maybe like:

Book1
	A	B	C	D	E
1	Model	Type	Count-Of-Types		Helper
2	ABC123	black	1		1
3	54123	black	3		1
4	54123	14	3		1
5	54123	grey	3		0.25
6	54123	grey	3		0.25
7	54123	grey	3		0.25
8	54123	grey	3		0.25
9	XYZ789	white	3		1
10	XYZ789	red	3		1
11	XYZ789		3		0
12	XYZ789	12	3		1
Sheet1

Cell Formulas
Range	Formula
C2:C12	C2	=IF(A2="","",SUMIF(A:A,A2,E:E))
E2:E12	E2	=IFERROR(1/COUNTIFS(A:A,A2,B:B,B2),0)

Hope that helps.

 

[Peter_SSs]

What about this?

ellison.xlsm
	A	B	C
1	Model	Type	Count-Of-Types
2	ABC123	black	1
3	54123	black	3
4	54123	14	3
5	54123	grey	3
6	54123	grey	3
7	54123	grey	3
8	54123	grey	3
9	XYZ789	white	3
10	XYZ789	red	3
11	XYZ789		3
12	XYZ789	12	3
13	ABC999		0
Count Types

Cell Formulas
Range	Formula
C2:C13	C2	=IFERROR(ROWS(UNIQUE(FILTER(B$2:B$13,(A$2:A$13=A2)*(B$2:B$13<>""),NA()))),0)

 

[ellison]

Hi, both work thanks... and work beautifully on the smaller sets of data.

But both seem to go quite slow on the larger sets of data (eg 100K rows).

???

 

[Peter_SSs]

But both seem to go quite slow on the larger sets of data (eg 100K rows).

Not too surprising. That is a lot of data to process!

1. Would a vba solution be acceptable?
2. Is your real data grouped by Model and Type like your sample? .. and if not, could we sort it that way to help processing speed?

 

[ellison]

Not too surprising. That is a lot of data to process!

1. Would a vba solution be acceptable?
2. Is your real data grouped by Model and Type like your sample? .. and if not, could we sort it that way to help processing speed?

Hi peter, sorry.... you’re right, it is a lot of data grr!

Absolutely yes, vba solution would be great.

And yes, the data could be grouped together (although it currently isn’t when it’s in its raw format, it would just be a quick extra step to include).

thanks

 

[Peter_SSs]

Hmm, I probably didn't ask enough questions about the desired results layout etc but you could consider a Pivot Table or try this with a copy of your data.

Sub Count_Types()
  Dim d As Object
  Dim a As Variant, b As Variant
  Dim i As Long, k As Long
  
  Application.ScreenUpdating = False
  Set d = CreateObject("Scripting.Dictionary")
  With Range("A1", Range("A" & Rows.Count).End(xlUp).Offset(1)).Resize(, 2)
    .Sort Key1:=.Columns(1), Order1:=xlAscending, Key2:=.Columns(2), Order2:=xlAscending, Header:=xlYes
    a = .Value
    ReDim b(1 To UBound(a), 1 To 2)
    For i = 2 To UBound(a) - 1
      If Len(a(i, 2)) > 0 Then d(a(i, 2)) = 1
      If a(i, 1) <> a(i + 1, 1) Then
        k = k + 1
        b(k, 1) = a(i, 1)
        b(k, 2) = d.Count
        d.RemoveAll
      End If
    Next i
    .Offset(1, 2).Resize(k, 2).Value = b
  End With
  Application.ScreenUpdating = True
End Sub

For the sample data I used in post #3, this code produced the following:

ellison.xlsm
	A	B	C	D
1	Model	Type	Model	Count
2	54123	14	54123	3
3	54123	black	ABC123	1
4	54123	grey	ABC999	0
5	54123	grey	XYZ789	3
6	54123	grey
7	54123	grey
8	ABC123	black
9	ABC999
10	XYZ789	12
11	XYZ789	red
12	XYZ789	white
13	XYZ789
14
Count Types (2)

 

[ellison]

Thanks so much Peter,

I added in a copy of the original data in columns I and J, put in the (original) row number in Col H (just as a double check), then in Col K, I put in a lookup back to the counts produced by your VBA (columns C & D).

I think I can now keep this a template once it's set up and copy and paste info into it to get the counts that we need out quickly & reliably (without my fat fingers wreaking too much haVOc!)

Quick excerpt:

Model-V2	Type-V2	Model-V3	Count-Of-Types				Original-Row-Number	Original-Model	Original-Type	Looked-Up-Count-Of-Variations
54123	14	54123	7				2	ABC123	black	1
54123	14	ABC123	1				3	54123	fffff	7
54123	14	ABC124	1				4	54123	14	7
54123	14	ABC125	1				5	54123	grey	7
54123	14	ABC126	1				6	54123	p	7
54123	14	ABC127	1				7	54123	red	7
54123	14	ABC128	1				8	54123	grey	7
54123	14	ABC129	1				9	XYZ789	white	118
54123	14	ABC130	1				10	XYZ789	red	118
54123	14	ABC131	1				11	XYZ789		118
54123	14	ABC132	1				12	XYZ789	12	118
54123	14	ABC133	1				13	ABC124	black	1
54123	14	ABC134	1				14	54123	black	7
54123	14	ABC135	1				15	54123	14	7

PS Thanks aGain!

 

[Peter_SSs]

You are welcome. Thanks for the follow-up.