Counting unique values

kramasundar

New Member
Joined
Aug 2, 2010
Messages
18
Hi,

I have an excel file with two sheets S1, S2 (please see below for sample content). All sheets are in one excel file.

For each cell in sheet S1, I have to check whether there is/are an/multiple entry/entries in sheet S2. If there are entries, then I would like to count the unique types and its number of occurrences.

In the example below, in the result sheet, id 123 has 4 rows with the following data
- 2 times Type A
- 2 times Type B
- 1 time Type C
- 1 time Type D

Could you guys please help me?

Thanks and best regards,
R


S1's content is as follows:
123
124
125
126
127
128
129

<tbody>
</tbody>



S2's content is as follows:
123textType A
123textType B
123textType A
123textType B
123textType C
123textType D
124textType D
125textType D
125textType A

<tbody>
</tbody>



I expect a result like below in sheet 3 for e.g.:
123Type A2
123Type B2
123Type C1
123Type D1
124Type D1
125Type D1
125Type A1

<tbody>
</tbody>
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Matty

Well-known Member
Joined
Feb 17, 2007
Messages
3,710
Hi,

I'd do something like this...

Sheet2 (A1:D10):

IDsDescriptionTypeID|Type
123textType A123|Type A
123textType B123|Type B
123textType A123|Type A
123textType B123|Type B
123textType C123|Type C
123textType D123|Type D
124textType D124|Type D
125textType D125|Type D
125textType A125|Type A

<tbody>
</tbody>

D2 copied down:

Code:
=C2&"|"&E2

Sheet3 (A1:D8):

Unique ID|TypeIDTypeCount
123|Type A123Type A2
123|Type B123Type B2
123|Type C123Type C1
123|Type D123Type D1
124|Type D124Type D1
125|Type D125Type D1
125|Type A125Type A1

<tbody>
</tbody>

A2 copied down (array entered):

Code:
=IF(ROWS(A$2:A2)<=SUMPRODUCT(1/COUNTIF(Sheet2!D$2:D$10,Sheet2!D$2:D$10)),INDEX(Sheet2!D$2:D$10,MATCH(0,COUNTIF(A$1:A1,Sheet2!D$2:D$10),0)),"")

B2 copied down:

Code:
=LEFT(A2,FIND("|",A2)-1)

C2 copied down:

Code:
=RIGHT(A2,LEN(A2)-FIND("|",A2))

D2 copied down:

Code:
=COUNTIFS(Sheet2!A$2:A$10,B2,Sheet2!C$2:C$10,C2)

The ID|Type helper columns can be hidden if desired.

Hope this helps.

Matty
 

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
Another option:-
Data & Results start row 1
Data on sheet 1 & sheet 2, Results on sheet 3.
Code:
[COLOR="Navy"]Sub[/COLOR] MG01Mar40
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] RngA [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Dic [COLOR="Navy"]As[/COLOR] Object
[COLOR="Navy"]Dim[/COLOR] Q [COLOR="Navy"]As[/COLOR] Variant, k [COLOR="Navy"]As[/COLOR] Variant, p [COLOR="Navy"]As[/COLOR] Variant, c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]

[COLOR="Navy"]With[/COLOR] Sheets("Sheet1")
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng: .Item(Dn.Value) = Empty: [COLOR="Navy"]Next[/COLOR] Dn

[COLOR="Navy"]With[/COLOR] Sheets("Sheet2")
[COLOR="Navy"]Set[/COLOR] RngA = .Range("A1", .Range("A" & Rows.Count).End(xlUp))
 [COLOR="Navy"]End[/COLOR] With
 [COLOR="Navy"]Set[/COLOR] Dic = CreateObject("Scripting.Dictionary")
    Dic.CompareMode = 1
   [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] RngA
        [COLOR="Navy"]If[/COLOR] .exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
            [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(, 2).Value) [COLOR="Navy"]Then[/COLOR]
                Dic(Dn.Value).Add (Dn.Offset(, 2).Value), 1
        [COLOR="Navy"]Else[/COLOR]
         Q = Dic(Dn.Value).Item(Dn.Offset(, 2).Value)
            Q = Q + 1
         Dic(Dn.Value).Item(Dn.Offset(, 2).Value) = Q
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR] Dn
   [COLOR="Navy"]End[/COLOR] With
  

[COLOR="Navy"]With[/COLOR] Sheets("Sheet3")
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] k [COLOR="Navy"]In[/COLOR] Dic.Keys
        [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] p [COLOR="Navy"]In[/COLOR] Dic(k)
               c = c + 1
                .Cells(c, 1) = k
                .Cells(c, 2) = p
                .Cells(c, 3) = Dic(k).Item(p)
        [COLOR="Navy"]Next[/COLOR] p
    
    [COLOR="Navy"]Next[/COLOR] k
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 

kramasundar

New Member
Joined
Aug 2, 2010
Messages
18
Thanks Mick and Matty

I solved it using Pivot tables.

Thanks for your time and effort.

BR
R
 

Watch MrExcel Video

Forum statistics

Threads
1,109,522
Messages
5,529,329
Members
409,863
Latest member
stacy09
Top