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

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,627
Messages
5,838,444
Members
430,548
Latest member
hh_dh2001

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
Top