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]

What array formula did you try? I think the performance with 5K rows would be OK using an array formula to count unique occurrences of numbers like this:
=SUM(IF(FREQUENCY(....),1))
Ctrl+Shift+Enter

M.

 

[Marcelo Branco]

Something like this


[Table="class: grid"][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][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Store id​

[/td][td]

Store Name​

[/td][td]

no​

[/td][td][/td][td]

Criteria​

[/td][td]

Result​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

238​

[/td][td]

St Albans​

[/td][td]

100034​

[/td][td][/td][td]

St Albans​

[/td][td]

5​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

397​

[/td][td]

Watford​

[/td][td]

100034​

[/td][td][/td][td]

Watford​

[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

238​

[/td][td]

St Albans​

[/td][td]

100103​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

397​

[/td][td]

Watford​

[/td][td]

100103​

[/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][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

7​

[/td][td]

238​

[/td][td]

St Albans​

[/td][td]

100324​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

8​

[/td][td]

397​

[/td][td]

Watford​

[/td][td]

100324​

[/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][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

10​

[/td][td]

397​

[/td][td]

Watford​

[/td][td]

100803​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

11​

[/td][td]

444​

[/td][td]

xxx​

[/td][td]

100034​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

12​

[/td][td]

555​

[/td][td]

yyy​

[/td][td]

100034​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

13​

[/td][td]

444​

[/td][td]

xxx​

[/td][td]

100103​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

14​

[/td][td]

555​

[/td][td]

yyy​

[/td][td]

100103​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

15​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

Criteria in E2:E3

Array Formula in F2
=SUM(IF(FREQUENCY(IF(B2:B30000<>"",IF(ISNUMBER(MATCH(B2:B30000,E2:E3,0)),C2:C30000)),C2:C30000),1))
Ctrl+Shift+Enter

Observe i used ranges with almost 30K rows

M.

 

[Ravin]

I probably havent been clear enough in my original thread

so the output file looks like this


AL11 - St Albans - Watford - Watford North - St Albans North - Harrow

So for each sector I get 5 stores

Each will have a count of item no

AL11 - St Albans - Watford - Watford North - St Albans North - Harrow
5000 4000 4500 5000 3000

what I need is a distinct count across the 5 stores

so I am left with AL11 - 5287

the file with the sectors is 600k of records

the file with the store id, store name and item no combinations is about 100k lines

I have tried the example array, and it seems to hang

its almost as though I need to get the individual counts, and then perform a distinct on the 5 count results

R

 

[SpillerBD]

Just ensure you've used the "add this data to the Data Model" option when creating your Pivot Table. That will then allow you to use the Distinct Count option for the Field Value Setting.
You Grand Total will be the Distinct Count for all Filtered Rows. Your example only has the 2 stores. With Hundreds of Stores, filtering may be more difficult even if you take advantage of Slicers.
I have used a separate Table to Connect to to provide an additional filter option.

 

[Marcelo Branco]

Sorry, I'm not fully understanding what you need. Actually with 600K rows a complex formula should not be a good solution.

Maybe if you better describe your data, criteria and desired results, someone can help you with a macro (Power Query?).

Good luck!

M.

 

[Ravin]

No thanks Marcelo your solution works, I think its the volume of data that is the issue

With one sector and your formula I get the desired result

it maybe I need to use a macro, at the current rate it will take quite a few hours to calculate

R

 

[Fazza]

A pivot table should be quick, even with 600,000 records.

Give the data a simple defined name, like TheData. Save the file.
ALT-D-P to start the pivot table wizard, choosing the external data option at the first step. Follow the wizard to the end choosing the option to edit in MS Query.
Somehow get just the unique records. There are many ways. Such as via the SQL button editing the text to

SELECT DISTINCT [Store ID], [Store name], [item no]
FROM TheData

Exit MS Query & finish the pivot table.

regards

 

[Ravin]

Hi Fazza

The problem is I have 2 files, one with the combinations

store id, store name, and item number

thats 550k of combinations

which I can pivot from

the place I want my data is another sheet

it is laid out

Sector - store 1, store 2, store 3, store 4, store 5 - distinct item count

and this has 10k sectors, with different store combinations

so to populate this sheet I need to go to the pivot for each sector and define the 5 stores to get the distinct result

but with 10k of sectors this would take too long

so I ruled out the pivot

unless there is a process I am missing where I can link the two

R

 

[Fazza]

i'm not crystal clear

it sounds like - the sample data is usable and it is just clarity on the result that is needed
and the result sounds like a cross tab?

which I'm thinking can come straight from the pivot table. that is, make a pivot table and there is your resultset. if you need it on another sheet, then copy & paste the values.

Which makes me think an alternative approach is forget the pivot table and make a query instead. it can give a cross tab result.

though I'm not clear on exactly what is wanted. now I see sectors and I'm getting less clear on what is what

I think best to mock up some sample data inputs (maybe what you've given already is OK?) and the corresponding output

maybe, if it is simple to explain, can you explain any issues with the distinct count pivot table solution I proposed in post #8 ?