Unique count of text with multiple criterias in an array

[srkreignz]

Hi
I have a table with the following data.

Band	Salesmen	Location1	Location2	Location3
X	A	Delhi	Mumbai	Chennai
Y	B	Kolkata	Chennai
Z	C	Chennai	Mumbai
X	D	Mumbai
Y	E	Mumbai	Chennai

<tbody>
</tbody>

I would like to know what is the unique number of locations for each band. ie

X	3
Y	3
Z	2

<tbody>
</tbody>

Please help.

 

[MickG]

Try this:-
Results columns "G & H".

[COLOR="Navy"]Sub[/COLOR] MG19Sep11
[COLOR="Navy"]Dim[/COLOR] Dn          [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Rng         [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Dic         [COLOR="Navy"]As[/COLOR] Object
[COLOR="Navy"]Dim[/COLOR] Lst         [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Ac          [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] k           [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Dim[/COLOR] c           [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
Range("G:H").ClearContents
Lst = Cells("1", Columns.Count).End(xlToLeft).Column
   [COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), 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"]For[/COLOR] Ac = 2 To Lst
          [COLOR="Navy"]If[/COLOR] Dn.Offset(, Ac) <> "" [COLOR="Navy"]Then[/COLOR]
            [COLOR="Navy"]If[/COLOR] Not Dic(Dn.Value).exists(Dn.Offset(, Ac).Value) [COLOR="Navy"]Then[/COLOR]
                Dic(Dn.Value).Add (Dn.Offset(, Ac).Value), Nothing
            [COLOR="Navy"]End[/COLOR] If
          [COLOR="Navy"]End[/COLOR] If
        [COLOR="Navy"]Next[/COLOR] Ac
    
    [COLOR="Navy"]Next[/COLOR] Dn


[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] k [COLOR="Navy"]In[/COLOR] Dic.Keys
        c = c + 1
       Cells(c, "G") = k
       Cells(c, "H") = IIf(k = "Band", "Location count", Dic(k).Count)
    [COLOR="Navy"]Next[/COLOR] k
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Domenic]

Here's a formula approach, which uses a table to help in the calculation. First create a table, listing each city and corresponding unique number. So let's say that L1:M5 contains the following table...

City	Index
Chennai	1
Delhi	2
Kolkata	3
Mumbai	4

<TBODY>
</TBODY>

Then, let's assume that A2:E6 contains the data, H2 contains X, H3 contains Y, and H4 contains Z. Then try...

I2, confirmed with CONTROL+SHIFT+ENTER, and copy down:

=SUM(IF(FREQUENCY(IF($A$2:$A$6=H2,IF($C$2:$E$6<>"",SUMIF($L$2:$L$5,$C$2:$E$6,$M$2:$M$5))),IF($A$2:$A$6=H2,IF($C$2:$E$6<>"",SUMIF($L$2:$L$5,$C$2:$E$6,$M$2:$M$5))))>0,1))

Hope this helps!

 

[srkreignz]

Thanks Domenic

But is there a way to do it without a helper column?

 

[srkreignz]

Thanks Mick, But I was looking for a formula based solution.

 

[Domenic]

Thanks Domenic

But is there a way to do it without a helper column?

Before offering you another possible solution, I just thought I'd mentioned that the table can be placed in a separate sheet, and the Visible property for the sheet can be set to "xlSheetVeryHidden" so that users cannot see the sheet at all and, therefore, won't be able to unhide it. Does this help?

Otherwise, here's an alternative. First, sort your data by Band, in ascending order, as follows...

Band	Salesmen	Location 1	Location 2	Location 3
X	A	Delhi	Mumbai	Chennai
X	D	Mumbai
Y	B	Kolkata	Chennai
Y	E	Mumbai	Chennai
Z	C	Chennai	Mumbai

<TBODY>
</TBODY>

Then, let's assume that H2 contains X, H3 contains Y, and H4 contains Z. Select cell I2 and define the following name...

Ribbon > Formulas > Defined Names > Define Name

Name: DynRange

Refers to:

=INDEX('Sheet1'!$C$2:$E$6,MATCH('Sheet1'!$H2,'Sheet1'!$A$2:$A$6,0),1):INDEX('Sheet1'!$C$2:$E$6,MATCH('Sheet1'!$H2,'Sheet1'!$A$2:$A$6,1),COLUMNS('Sheet1'!$C$2:$E$6))

Click Ok

Note that the formula for the defined name contains a relative reference, so it's important that I2 is selected before creating the defined name. Then, try the following formula...

I2, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=SUM(IF(DynRange<>"",1/COUNTIF(DynRange,DynRange)))

Hope this helps!

 

[srkreignz]

@Domenic Thanks a lot it worked

 

[Domenic]

You're very welcome!

 

[Mike Szczesny]

Here is a very complex formula to capture unique items form range that contains more than row (or column).
Assume your range is A1:D6 A8: enter X. B8: insert formula to select items for Band X
B8:

=IFERROR(INDEX($B$2:$D$6,INT(SMALL(IF($B$2:$D$6<>"",IF($A$2:$A$6=$A$8,(ROW($B$2:$D$6)-ROW($B$2)+1)*10^9+COLUMN($B$2:$D$6)-COLUMN($B$2)+1)),COLUMNS($B$8:B8))/10^9),MOD(SMALL(IF($B$2:$D$6<>"",IF($A$2:$A$6=$A$8,(ROW($B$2:$D$6)-ROW($B$2)+1)*10^9+COLUMN($B$2:$D$6)-COLUMN($B$2)+1)),COLUMNS($B$8:B8)),10^9)),"") Use Cntrl-Shift-Enter copy across. This formula enter all your results first by row, then column. B9: =SUM(IF(FREQUENCY(IF($B$8:$J$8<>"",MATCH($B$8:$J$8,$B$8:$J$8,0)),TRANSPOSE(COLUMN($B$8:$J$8)-COLUMN($B$8)+1)),1)) This formula gives total number of unique items. If you need to list items first by column, then number, then use the formula in B11. A11: enter X B11: =IFERROR(INDEX($B$2:$D$6,MOD(SMALL(IF($B$2:$D$6<>"",IF($A$2:$A$6=$A$11,(COLUMN($B$2:$D$6)-COLUMN($B$2)+1)*10^9+ROW($B$2:$D$6)-rOW($B$2)+1)),COLUMNS($B$11:B11)),10^9),INT(SMALL(IF($B$2:$D$6<>"",IF($A$2:$A$6=$A$11,(COLUMN($B$2:$D$6)-COLUMN($B$2)+1)*10^9+ROW($B$2:$D$6)-ROW($B$2)+1)),COLUMNS($B$11:B11))/10^9)),"") B12: =SUM(IF(FREQUENCY(IF($B$11:$J$11<>"",MATCH($B$11:$J$11,$B$11:$J$11,0)),TRANSPOSE(COLUMN($B$11:$J$11)-COLUMN($B$11)+1)),1)) Therefore the answers for B9 and B12 would be the same. <tbody> </tbody> <tbody> </tbody> <tbody> </tbody>

<tbody>
</tbody>