Count unique values with multiple criteria and exclusions

AlexandraT

AlexandraT

Board Regular
Joined
Mar 23, 2015
Messages
144
Hello,

I don't usually have to actually post a question here since I can usually find the answers I need in other posts. However, I am unable to find an answer now.

Here is my sample table:

ColourArticleSizeSold
Green
Dress

<tbody>
</tbody>
2
Apr-15

<tbody>
</tbody>
Green
Jumper

<tbody>
</tbody>
2
Feb-15

<tbody>
</tbody>
Green
Jumper

<tbody>
</tbody>
1
Feb-15

<tbody>
</tbody>
Green
Pants

<tbody>
</tbody>
1
May-15

<tbody>
</tbody>
Green
Skirt

<tbody>
</tbody>
2
May-15

<tbody>
</tbody>
Green
Skirt

<tbody>
</tbody>
1
Apr-15

<tbody>
</tbody>
Green
T-shirt

<tbody>
</tbody>
1
Feb-15

<tbody>
</tbody>
GreenT-shirt2
Feb-15

<tbody>
</tbody>
Red
Jumper

<tbody>
</tbody>
1
May-15

<tbody>
</tbody>
Red
Jumper

<tbody>
</tbody>
2
Mar-15

<tbody>
</tbody>
Red
Pants

<tbody>
</tbody>
1
Apr-15

<tbody>
</tbody>
Red
Pants

<tbody>
</tbody>
2
Feb-15

<tbody>
</tbody>
Red
Shorts

<tbody>
</tbody>
2
Mar-15

<tbody>
</tbody>
RedSkirt1
May-15

<tbody>
</tbody>
RedSocks1
May-15

<tbody>
</tbody>
RedSocks2
May-15

<tbody>
</tbody>

<tbody>
</tbody>

Looks a bit silly but it should help with that I need. So what I would need is to know how many types of clothes are green but not red and vice versa. I would also like these split by month. Also, how many green only types of clothes come in both sizes and how many red only types of clothes come in both sizes. Also by month.

To illustrate what I need (without taking into account the month):

Green only2(Dress,T-shirt)
Red only2(Shorts,Socks)
Red 2 sizes1(Socks)
Green 2 sizes1(T-shirt)

<tbody>
</tbody>

My data is some thousand rows long but there are 2 colours, 2 sizes and 4 months. There are thousands of types of clothes though.

I don't have any preference how this can be done, be it by additional columns, formula, pivot table or power pivot. But for the life of me I cannot figure it out and I can't seem to find anything online (granted I might not be searching properly).

Any help would be greatly appreciated. Thanks :)
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Sort by date Sort by votes
seems ideal for a pivot table or two
 
Upvote 0
mole999 said:
seems ideal for a pivot table or two
Click to expand...

I tried with both pivot and power pivot. distinct count would seem to me be an option but I don't know how to make it count reds that are not green. The farthest I got was to get a distinct count for red but this included articles that were both red and green. This is what I used: =CALCULATE(DISTINCTCOUNT(Table1[Article]),Table1[Colour]="Red"). I also tried =CALCULATE(DISTINCTCOUNT(Table1[Article]),Table1[Colour]="Red",Table1[Colour]<>"Green") but it makes no difference not to mention I think it might not even make sense.
 
Upvote 0
I selected your data
dumped it into excel
selected the data and named it pivot

inserted a pivot and used pivot as the source

i added to the field list in the following order
sold
article
colour
size
all as row labels

i prefer to set my pivots up as
allow multiple filters
classic pivot table layout

then for values
I used count of articles

Count of Article
SoldArticleColourSizeTotal
Feb-15JumperGreen11
21
Green Total2
Jumper Total2
PantsRed21
Red Total1
Pants Total1
T-shirtGreen11
21
Green Total2
T-shirt Total2
Feb-15 Total5

<colgroup><col style="mso-width-source:userset;mso-width-alt:3264;width:77pt" width="102"> <col style="mso-width-source:userset;mso-width-alt:3712;width:87pt" width="116"> <col style="width:54pt" width="72"> <col style="mso-width-source:userset;mso-width-alt:1568;width:37pt" width="49"> <col style="mso-width-source:userset;mso-width-alt:1344;width:32pt" width="42"> </colgroup><tbody>
</tbody>
 
Upvote 0
Try this:-
Results Start "E1"
Code: 
[COLOR=navy]Sub[/COLOR] MG17Jun09
[COLOR=navy]Dim[/COLOR] Dn [COLOR=navy]As[/COLOR] Range, k [COLOR=navy]As[/COLOR] Variant, p [COLOR=navy]As[/COLOR] Variant, c [COLOR=navy]As[/COLOR] [COLOR=navy]Long[/COLOR]
[COLOR=navy]Dim[/COLOR] Str [COLOR=navy]As[/COLOR] [COLOR=navy]String,[/COLOR] Rng [COLOR=navy]As[/COLOR] Range, Dic [COLOR=navy]As[/COLOR] Object
[COLOR=navy]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
 [COLOR=navy]Set[/COLOR] Dic = CreateObject("Scripting.Dictionary")
    Dic.CompareMode = 1
    [COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Rng
            [COLOR=navy]If[/COLOR] Not Dic.exists(Dn.Value) [COLOR=navy]Then[/COLOR]
                [COLOR=navy]Set[/COLOR] Dic(Dn.Value) = CreateObject("Scripting.Dictionary")
            [COLOR=navy]End[/COLOR] If
            [COLOR=navy]If[/COLOR] Not Dic(Dn.Value).exists(Dn.Offset(, 1).Value) [COLOR=navy]Then[/COLOR]
                Dic(Dn.Value).Add (Dn.Offset(, 1).Value), Dn.Offset(, 2)
            [COLOR=navy]Else[/COLOR]
                Dic(Dn.Value).Item(Dn.Offset(, 1).Value) = Dic(Dn.Value).Item(Dn.Offset(, 1).Value) & ", " & Dn.Offset(, 2)
            [COLOR=navy]End[/COLOR] If
    [COLOR=navy]Next[/COLOR] Dn
Range("F1").Resize(, 3).Value = Array("Colour", "Type", "Sizes")
  c = 1
    [COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] k [COLOR=navy]In[/COLOR] Dic.Keys
        For Each p In Dic(k)
               c = c + 1
               Cells(c, "F") = k
               Cells(c, "G") = p
              Cells(c, "H") = "Sizes :- " & Dic(k).Item(p)
            [COLOR=navy]Next[/COLOR] p
    [COLOR=navy]Next[/COLOR] k
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]
Regards Mick
 
Upvote 0
only t-shirt is the one that is green without being red. both jumper and pants are green but there are also red jumpers and pants on the list
 
Upvote 0
MickG said:
Try this:-
Results Start "E1"
Code: 
[COLOR=navy]Sub[/COLOR] MG17Jun09
[COLOR=navy]Dim[/COLOR] Dn [COLOR=navy]As[/COLOR] Range, k [COLOR=navy]As[/COLOR] Variant, p [COLOR=navy]As[/COLOR] Variant, c [COLOR=navy]As[/COLOR] [COLOR=navy]Long[/COLOR]
[COLOR=navy]Dim[/COLOR] Str [COLOR=navy]As[/COLOR] [COLOR=navy]String,[/COLOR] Rng [COLOR=navy]As[/COLOR] Range, Dic [COLOR=navy]As[/COLOR] Object
[COLOR=navy]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
 [COLOR=navy]Set[/COLOR] Dic = CreateObject("Scripting.Dictionary")
    Dic.CompareMode = 1
    [COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Rng
            [COLOR=navy]If[/COLOR] Not Dic.exists(Dn.Value) [COLOR=navy]Then[/COLOR]
                [COLOR=navy]Set[/COLOR] Dic(Dn.Value) = CreateObject("Scripting.Dictionary")
            [COLOR=navy]End[/COLOR] If
            [COLOR=navy]If[/COLOR] Not Dic(Dn.Value).exists(Dn.Offset(, 1).Value) [COLOR=navy]Then[/COLOR]
                Dic(Dn.Value).Add (Dn.Offset(, 1).Value), Dn.Offset(, 2)
            [COLOR=navy]Else[/COLOR]
                Dic(Dn.Value).Item(Dn.Offset(, 1).Value) = Dic(Dn.Value).Item(Dn.Offset(, 1).Value) & ", " & Dn.Offset(, 2)
            [COLOR=navy]End[/COLOR] If
    [COLOR=navy]Next[/COLOR] Dn
Range("F1").Resize(, 3).Value = Array("Colour", "Type", "Sizes")
  c = 1
    [COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] k [COLOR=navy]In[/COLOR] Dic.Keys
        For Each p In Dic(k)
               c = c + 1
               Cells(c, "F") = k
               Cells(c, "G") = p
              Cells(c, "H") = "Sizes :- " & Dic(k).Item(p)
            [COLOR=navy]Next[/COLOR] p
    [COLOR=navy]Next[/COLOR] k
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]
Regards Mick
Click to expand...

While I have no idea how a macro usually works, I believe this one returns the sizes for every article. I need just a headcount of all red article types that are not also green and viceversa.
 
Upvote 0
This is the result that the code Produces.
Code: 
[COLOR="RoyalBlue"][B]Row No [/B][/COLOR] [COLOR="RoyalBlue"][B]Col(F) [/B][/COLOR] [COLOR="RoyalBlue"][B]Col(G)  [/B][/COLOR] [COLOR="RoyalBlue"][B]Col(H)           [/B][/COLOR]
1.      Colour  Type     Sizes            
2.      Green   Dress    Sizes :- 2       
3.      Green   Jumper   Sizes :- 2, 1    
4.      Green   Pants    Sizes :- 1       
5.      Green   Skirt    Sizes :- 2, 1    
6.      Green   T-shirt  Sizes :- 1, 2    
7.      Red     Jumper   Sizes :- 1, 2    
8.      Red     Pants    Sizes :- 1, 2    
9.      Red     Shorts   Sizes :- 2       
10.     Red     Skirt    Sizes :- 1       
11.     Red     Socks    Sizes :- 1, 2, 3
Regards Mick
 
Upvote 0
MickG said:
This is the result that the code Produces.
Code: 
[COLOR=RoyalBlue][B]Row No [/B][/COLOR] [COLOR=RoyalBlue][B]Col(F) [/B][/COLOR] [COLOR=RoyalBlue][B]Col(G)  [/B][/COLOR] [COLOR=RoyalBlue][B]Col(H)           [/B][/COLOR]
1.      Colour  Type     Sizes            
2.      Green   Dress    Sizes :- 2       
3.      Green   Jumper   Sizes :- 2, 1    
4.      Green   Pants    Sizes :- 1       
5.      Green   Skirt    Sizes :- 2, 1    
6.      Green   T-shirt  Sizes :- 1, 2    
7.      Red     Jumper   Sizes :- 1, 2    
8.      Red     Pants    Sizes :- 1, 2    
9.      Red     Shorts   Sizes :- 2       
10.     Red     Skirt    Sizes :- 1       
11.     Red     Socks    Sizes :- 1, 2, 3
Regards Mick
Click to expand...

Hi Mick,

I got that part. But the macro shows what sizes there are for each colour and article. I need to know how many types of articles are red and not green, how many are green and not red and how many green articles have both sizes and how many red articles have both sizes. the macro lists all articles and their sizes though. it doesn't actually count anything. Thsi is what I need:

Green only2(Dress,T-shirt)
Red only2(Shorts,Socks)
Red 2 sizes1(Socks)
Green 2 sizes1(T-shirt)

Thanks,
Alex

<tbody>
</tbody>
 
Upvote 0
AlexandraT said:
only t-shirt is the one that is green without being red. both jumper and pants are green but there are also red jumpers and pants on the list
Click to expand...

did you mean this which is easily changed by using filters

Count of Article
SoldArticleColourSizeTotal
Feb-15JumperGreen11
21
Green Total2
Jumper Total2
T-shirtGreen11
21
Green Total2
T-shirt Total2
Feb-15 Total4
Apr-15DressGreen21
Green Total1
Dress Total1
SkirtGreen11
Green Total1
Skirt Total1
Apr-15 Total2
May-15PantsGreen11
Green Total1
Pants Total1
SkirtGreen21
Green Total1
Skirt Total1
May-15 Total2
Grand Total8

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question
Help creating multiple lines
Replies
0
Views
219
Excelnewie
E
Z
  • Solved
Drop down list using unique but with changed number of rows
Replies
5
Views
113
Fluff
Fluff
B
  • Question
How to add specific values in a table?
Replies
5
Views
73
Peter_SSs
Peter_SSs
W
  • Solved
Validate month based on contract dates & Calculate number of weeks in that month to calculate hours booked per month
2
Replies
15
Views
1K
Wishmaster40
W
K
  • Question
Populate New or Dropped Data
Replies
3
Views
107
kumara_faith
K

Forum statistics

Threads
1,214,583
Messages
6,120,378
Members
448,955
Latest member
BatCoder

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