Count unique, based on values in other columns

MartinL

Well-known Member
Joined
Oct 16, 2008
Messages
1,105
I have been searching onthis for a couple of day and see today that a similar question is being asked.
If only pivot tables would do this.

I have 5 columns
Country, Retail Chain, Store, Product Group, Product

For completeness, what I am trying to achieve is:
  1. analyse how many different product groups each country sell
  2. analyse how many different products each country sell
  3. analyse how many different product groups each Retail chain sell by country
  4. analyse how many different products each Retail chain sell by country
  5. analyse how many different product groups each Store sell by country by Retail Chain
  6. analyse how many different products each Store sell by country by Retail Chain

the last two shouldnt need by Retail Chain, but you can never be too sure.
If I can get any one working correctly the others should be simple.

I am not sure if the UNQn colums should show 1 for every unique entry so the PT sums them or sums them each time so the PT displays MAX (as each value will be the same)

I have a series of Pivot Tables & Charts that are run over a Table something like this


CountryRetChainStoreProdGrpProdUNQ1UNQ2UNQ3UNQ4UNQ5UNQ6
IcelandChain 1Store 1Widget 1Prod A1
IcelandChain 1Store 1Widget 1Prod A2
IcelandChain 1Store 1Widget 2Prod B1
IcelandChain 1Store 2Widget 3Prod C1
IcelandChain 2Store 3Widget 1Prod A1
IcelandChain 3Store 4Widget 2Prod B1
NetherlandsChain 1Store 5Widget 1Prod A1
NetherlandsChain 4Store 6Widget 1Prod A3
NetherlandsChain 5Store 7Widget 2Prod B1
Example solution
Q.1Q.2
CountryProdGrpProducts
Iceland34
Netherlands23
Q.3Q.4
CountryRetChainProdGrpProducts
IcelandChain 134
IcelandChain 211
IcelandChain 311
NetherlandsChain 111
NetherlandsChain 411
NetherlandsChain 511

<COLGROUP><COL style="WIDTH: 63pt; mso-width-source: userset; mso-width-alt: 3072" span=2 width=84><COL style="WIDTH: 48pt" span=5 width=64><COL style="WIDTH: 63pt; mso-width-source: userset; mso-width-alt: 3072" width=84><COL style="WIDTH: 48pt" span=3 width=64><TBODY>
</TBODY>

Can anyone help
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

Akashwani

Well-known Member
Joined
Mar 14, 2009
Messages
2,911
Hi Martin,

Based on your sample data, is this what you require?...

Excel Workbook
ABCDEFGHIJKL
1CountryRetChainStoreProdGrpProdUNQ1UNQ2UNQ3UNQ4UNQ5UNQ6
2IcelandChain 1Store 1Widget 1Prod A1
3IcelandChain 1Store 1Widget 1Prod A2
4IcelandChain 1Store 1Widget 2Prod B1
5IcelandChain 1Store 2Widget 3Prod C1
6IcelandChain 2Store 3Widget 1Prod A1
7IcelandChain 3Store 4Widget 2Prod B1
8NetherlandsChain 1Store 5Widget 1Prod A1
9NetherlandsChain 4Store 6Widget 1Prod A3
10NetherlandsChain 5Store 7Widget 2Prod B1
11
12Example Solutions
13Q.1Q.2
14CountryProdGrpProducts
15Iceland34
16Netherlands23
17Q.3Q.4
18CountryRetChainProdGrpProducts
19IcelandChain 134
20IcelandChain 211
21IcelandChain 311
22NetherlandsChain 111
23NetherlandsChain 411
24NetherlandsChain 511
25
Sheet8


The formula in B15 needs entering with ctrl shift enter NOT just enter, it can then be copied across and down.
The formula in C19 needs entering with ctrl shift enter NOT just enter, it can then be copied across and down.

I'm unsure as to what you requre in the range F2:K10 with headers UNIQ.

Take a look here for an explanation on the above formulas....

Count unique distinct values that meet multiple criteria in excel | Get Digital Help - Microsoft Excel resource

I hope that helps.

Ak
 

Watch MrExcel Video

Forum statistics

Threads
1,109,411
Messages
5,528,623
Members
409,828
Latest member
99DodgeRam

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top