Distinct Result from Multiple Countif

[Ravin]

all, I need help

I have 3 columns of data

store id, store name and item no

if I create a Pivot by store name and count of item no

I get 250 store names and counts

this works

the problem is I am trying to perform a distinct count of SKUs across 5 stores

I can do this in a pivot using distinct count and selecting the 5 stores in the Pivot

the issue I have is that the list I am working from has 5000 rows with 5 stores each

Can I either do this via a Pivot

I have tried arrays but it is incredibly slow

here is an example of the data

Store Id Store Name item no
238 St Albans 100034
397 Watford 100034
238 St Albans 100103
397 Watford 100103
238 St Albans 100269
238 St Albans 100324
397 Watford 100324
238 St Albans 100803
397 Watford 100803

I'm basically doing a pivot

where I am saying

store 238 has 5 items
store 397 has 4 items

what I want is a formula that

tells me the distinct occurrences across both stores

so I would define "st albans" and "watford" and the distinct count would be 5

equally I have to lookup from the text "st albans"

Can you help please

R

 

[Marcelo Branco]

oops... the code above has a typo

Should be

Select Case vData(i, 1)
    Case 238, 397 'IDs of interest
        If vData(i, 4) = sCat Then dic(vData(i, [COLOR=#ff0000]3[/COLOR])) = Empty
End Select

M.

 

[Ravin]

Hi Marcelo

Both the array and Macro work but the macro is much more efficient

So your code ends up being

Sub xTest()
Dim sCat As String
Dim dic As Object, vData As Variant, i As Long
Dim StartTime As Double, ElapsedTime As Double

StartTime = Timer
sCat = "Doors"

Set dic = CreateObject("Scripting.Dictionary")
dic.CompareMode = vbTextCompare

'vdata has 550000 rows
vData = Range("A2:D" & Cells(Rows.Count, "A").End(xlUp).Row)

For i = 1 To UBound(vData, 1)
Select Case vData(i, 1)
Case 238, 397 'IDs of interest
If vData(i, 4) = sCat Then dic(vData(i, 3)) = Empty
End Select
Next i

Range("M2") = dic.Count
ElapsedTime = Timer - StartTime
With Range("O2")
.Value = ElapsedTime
.NumberFormat = "0.0000"
End With
End Sub



So question if my raw data is in Columns A:D

but my results layout is as follows below F:N

So the sector has 5 results defined by name (G:K) for each sector defined in Column F

The category I am looking at is in column L

and the distinct result needs to go into column M for each row

So for row 2 the distinct result needs to go into M2 for the stores defined in G2:K2

how do I amend the code without specifying the case, each store ID separately

R

A

B

C

D

E

F

G

H

I

J

K

L

M

N

1

Store ID

Store Name

SKu

Category

Sector

1

2

3

4

5

Category

Result

Timer

2

238

St Albans

100034

Doors

AL11

St Albans

Watford

Hemel

Borehamwood

Barnsley

Doors

3

297

Watford

100034

Doors

AL23

Watford

St Albans

Hemel

Borehamwood

Barnsley

Timber

4

96

Hemel

100034

Doors

AL34

Luton

Dunstable

Hemel

St Albans

Stevenage

Doors

5

467

Borehamwood

100034

Doors

6

247

Barnsley

101000

Doors

7

296

Blackpool

200604

Doors

8

37

Bridgwater

101000

Doors

9

77

Bristol

123456

Doors

<tbody>
</tbody>

****** id="cke_pastebin" style="position: absolute; top: 104px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">

	A​	B​	C​	D​	E​	F​	G​	H​
1​	StoreId	StoreName	itemno	Category		StoreName	Category	Result
2​	238​	St Albans	100034​	Doors		St Albans	Doors	2​
3​	397​	Watford	100034​	Doors		Watford
4​	238​	St Albans	100103​	Timber
5​	397​	Watford	100103​	Timber
6​	238​	St Albans	100269​	Paint
7​	238​	St Albans	100324​	Woodcare
8​	397​	Watford	100324​	Woocare
9​	238​	St Albans	100803​	Doors
10​	397​	Watford	100803​	Doors
11​

<tbody>
</tbody>

</body>

 

[Marcelo Branco]

I suggest a UDF (User Defined Function)

Something like this

Function GetCount(rIds As Range, rCat As Range)
    Dim sCat As String, sId As String
    Dim dic As Object, vData As Variant, i As Long
        
    sCat = rCat.Value
    sId = "," & Join(Application.Transpose(Application.Transpose(rIds)), ",") & ","
    vData = Range("A2:D" & Cells(Rows.Count, "A").End(xlUp).Row)
    
    Set dic = CreateObject("Scripting.Dictionary")
    dic.CompareMode = vbTextCompare
    
    For i = 1 To UBound(vData, 1)
        If InStr(1, sId, "," & vData(i, 1) & ",") > 0 Then
            If vData(i, 4) = sCat Then dic(vData(i, 3)) = Empty
        End If
    Next i
        
    GetCount = dic.Count
End Function

Put
StroreIDs in G2:K2, not the Names ( i think it's safer and faster to use Ids)
Category in L2

Formula in M2
=GetCount(G2:K2,L2)

M.