Count Distinct Values for 2 criteria in Excel using VBA

[jpb7887]

Hello

I am new in VBA and I am looking to build a macro to count the number of stores a responsible (let's say K) sells to, for each brand.
In this example, K sells to 1 store the brand 1 and 1 store the brand 2. (so a total of 3 sales but to 2 stores only).
Any help would be really appreciated
Thanks

Responsible K K K B B B B B S S S S B B B B B B <colgroup><col></colgroup><tbody> </tbody>

Store Name TWCM Unley HealthSave Stirling HealthSave Stirling Pharmacy 360 Black Rock Mulqueeny Midnight Pharmacy Alfred Healthsmart Ramsay CBD Scott Dibben SP Pitt St PL George St PL George St TW Chatswood Chase CWH Chatswood PL Wynard PL World Square PL World Square SP Pitt Street SP Pitt Street <colgroup><col></colgroup><tbody> </tbody>

Brand 1 2 2 1 1 1 1 1 1 2 2 2 1 1 1 1 1 2 <colgroup><col></colgroup><tbody> </tbody>

<tbody>
</tbody>

 

[alansidman]

Perhaps a Pivot Table as shown below.

Excel 2016 (Windows) 32 bit

	H​	I​	J​
1​	Sum of Brand	Column Labels
2​	Row Labels	1​	2​
3​	B	10​	2​
4​	Alfred Healthsmart	1​
5​	CWH Chatswood	1​
6​	Mulqueeny Midnight Pharmacy	1​
7​	Pharmacy 360 Black Rock	1​
8​	PL World Square	2​
9​	PL Wynard	1​
10​	Ramsay CBD	1​
11​	Scott Dibben	1​
12​	SP Pitt Street	1​	2​
13​	K	1​	4​
14​	HealthSave Stirling		4​
15​	TWCM Unley	1​
16​	S	1​	6​
17​	PL George St		4​
18​	SP Pitt St	1​
19​	TW Chatswood Chase		2​
20​	Grand Total	12​	12​

Sheet: Sheet1

 

[jpb7887]

Thank you, I know it is doable in "simple excel"but it has to be done with VBA as it is for my manager who wants to "press the button" and refresh the data every month

 

[alansidman]

Use the Macro Recorder and then post your code and we will modify as necessary.