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>
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,564
Messages
6,114,334
Members
448,567
Latest member
Kuldeep90

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