Distinct Result from Multiple Countif

R

Ravin

Board Regular
Joined
Aug 24, 2012
Messages
67
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
 
Sort by date Sort by votes
oops... the code above has a typo

Should be
Code: 
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.
 
Upvote 0

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
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



[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"]
[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[TD="align: center"]J[/TD]
[TD="align: center"]K[/TD]
[TD="align: center"]L[/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]N[/TD]
[/TR]
[TR]
[TD="align: center"]1
[/TD]
[TD="align: center"]Store ID[/TD]
[TD="align: center"]Store Name[/TD]
[TD="align: center"]SKu[/TD]
[TD="align: center"]Category[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]Sector[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]Category[/TD]
[TD="align: center"]Result[/TD]
[TD="align: center"]Timer[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]238[/TD]
[TD="align: center"]St Albans[/TD]
[TD="align: center"]100034[/TD]
[TD="align: center"]Doors[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]AL11[/TD]
[TD="align: center"]St Albans[/TD]
[TD="align: center"]Watford[/TD]
[TD="align: center"]Hemel[/TD]
[TD="align: center"]Borehamwood[/TD]
[TD="align: center"]Barnsley[/TD]
[TD="align: center"]Doors[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]297[/TD]
[TD="align: center"]Watford[/TD]
[TD="align: center"]100034[/TD]
[TD="align: center"]Doors[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]AL23[/TD]
[TD="align: center"]Watford[/TD]
[TD="align: center"]St Albans[/TD]
[TD="align: center"]Hemel[/TD]
[TD="align: center"]Borehamwood[/TD]
[TD="align: center"]Barnsley[/TD]
[TD="align: center"]Timber[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]96[/TD]
[TD="align: center"]Hemel[/TD]
[TD="align: center"]100034[/TD]
[TD="align: center"]Doors[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]AL34[/TD]
[TD="align: center"]Luton[/TD]
[TD="align: center"]Dunstable[/TD]
[TD="align: center"]Hemel[/TD]
[TD="align: center"]St Albans[/TD]
[TD="align: center"]Stevenage[/TD]
[TD="align: center"]Doors[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]467[/TD]
[TD="align: center"]Borehamwood[/TD]
[TD="align: center"]100034[/TD]
[TD="align: center"]Doors[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]247[/TD]
[TD="align: center"]Barnsley[/TD]
[TD="align: center"]101000[/TD]
[TD="align: center"]Doors[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]296[/TD]
[TD="align: center"]Blackpool[/TD]
[TD="align: center"]200604[/TD]
[TD="align: center"]Doors[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]37[/TD]
[TD="align: center"]Bridgwater[/TD]
[TD="align: center"]101000[/TD]
[TD="align: center"]Doors[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"]77[/TD]
[TD="align: center"]Bristol[/TD]
[TD="align: center"]123456[/TD]
[TD="align: center"]Doors[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[/TR]
</tbody>[/TABLE]






****** id="cke_pastebin" style="position: absolute; top: 104px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">[TABLE="class: cms_table_grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]
A​
[/TD]
[TD="bgcolor: #DCE6F1"]
B​
[/TD]
[TD="bgcolor: #DCE6F1"]
C​
[/TD]
[TD="bgcolor: #DCE6F1"]
D​
[/TD]
[TD="bgcolor: #DCE6F1"]
E​
[/TD]
[TD="bgcolor: #DCE6F1"]
F​
[/TD]
[TD="bgcolor: #DCE6F1"]
G​
[/TD]
[TD="bgcolor: #DCE6F1"]
H​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
1​
[/TD]
[TD]StoreId
[/TD]
[TD]StoreName
[/TD]
[TD]itemno
[/TD]
[TD]Category
[/TD]
[TD][/TD]
[TD]StoreName
[/TD]
[TD]Category
[/TD]
[TD]Result
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
2​
[/TD]
[TD]
238​
[/TD]
[TD]St Albans
[/TD]
[TD]
100034​
[/TD]
[TD]Doors
[/TD]
[TD][/TD]
[TD]St Albans
[/TD]
[TD]Doors
[/TD]
[TD]
2​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
3​
[/TD]
[TD]
397​
[/TD]
[TD]Watford
[/TD]
[TD]
100034​
[/TD]
[TD]Doors
[/TD]
[TD][/TD]
[TD]Watford
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
4​
[/TD]
[TD]
238​
[/TD]
[TD]St Albans
[/TD]
[TD]
100103​
[/TD]
[TD]Timber
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
5​
[/TD]
[TD]
397​
[/TD]
[TD]Watford
[/TD]
[TD]
100103​
[/TD]
[TD]Timber
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
6​
[/TD]
[TD]
238​
[/TD]
[TD]St Albans
[/TD]
[TD]
100269​
[/TD]
[TD]Paint
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
7​
[/TD]
[TD]
238​
[/TD]
[TD]St Albans
[/TD]
[TD]
100324​
[/TD]
[TD]Woodcare
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
8​
[/TD]
[TD]
397​
[/TD]
[TD]Watford
[/TD]
[TD]
100324​
[/TD]
[TD]Woocare
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
9​
[/TD]
[TD]
238​
[/TD]
[TD]St Albans
[/TD]
[TD]
100803​
[/TD]
[TD]Doors
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
10​
[/TD]
[TD]
397​
[/TD]
[TD]Watford
[/TD]
[TD]
100803​
[/TD]
[TD]Doors
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
11​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

</body>
 
Upvote 0
I suggest a UDF (User Defined Function)

Something like this
Code: 
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question Question
Case sensitive distinct count based on multiple criteria
Replies
2
Views
271
shaowu459
shaowu459
T
  • Solved
Multiple criteria COUNTIFS with distinct values
Replies
4
Views
1K
Fluff
Fluff
C
  • Question Question
Wrong Subtotal in Pivot Table Distinct Count
Replies
1
Views
2K
Fluff
Fluff
M
  • Solved
Rolling Distinct Count by Month YTD
Replies
5
Views
233
Mengli
M
Rainbow_P
  • Question Question
Please help: Reference error in GETPIVOTDATA (pivot table in another sheet)
Replies
4
Views
1K
Rainbow_P
Rainbow_P

Forum statistics

Threads
1,223,711
Messages
6,174,025
Members
452,542
Latest member
Bricklin

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top