Counting unique values

[kramasundar]

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:
[TABLE="width: 87"]
<tbody>[TR]
[TD="align: right"]123[/TD]
[/TR]
[TR]
[TD="align: right"]124[/TD]
[/TR]
[TR]
[TD="align: right"]125[/TD]
[/TR]
[TR]
[TD="align: right"]126[/TD]
[/TR]
[TR]
[TD="align: right"]127[/TD]
[/TR]
[TR]
[TD="align: right"]128[/TD]
[/TR]
[TR]
[TD="align: right"]129[/TD]
[/TR]
</tbody>[/TABLE]



S2's content is as follows:
[TABLE="width: 261"]
<tbody>[TR]
[TD="align: right"]123[/TD]
[TD="width: 87"]text[/TD]
[TD="width: 87"]Type A[/TD]
[/TR]
[TR]
[TD="align: right"]123[/TD]
[TD]text[/TD]
[TD]Type B[/TD]
[/TR]
[TR]
[TD="align: right"]123[/TD]
[TD]text[/TD]
[TD]Type A[/TD]
[/TR]
[TR]
[TD="align: right"]123[/TD]
[TD]text[/TD]
[TD]Type B[/TD]
[/TR]
[TR]
[TD="align: right"]123[/TD]
[TD]text[/TD]
[TD]Type C[/TD]
[/TR]
[TR]
[TD="align: right"]123[/TD]
[TD]text[/TD]
[TD]Type D[/TD]
[/TR]
[TR]
[TD="align: right"]124[/TD]
[TD]text[/TD]
[TD]Type D[/TD]
[/TR]
[TR]
[TD="align: right"]125[/TD]
[TD]text[/TD]
[TD]Type D[/TD]
[/TR]
[TR]
[TD="align: right"]125[/TD]
[TD]text[/TD]
[TD]Type A[/TD]
[/TR]
</tbody>[/TABLE]



I expect a result like below in sheet 3 for e.g.:
[TABLE="width: 261"]
<tbody>[TR]
[TD="align: right"]123[/TD]
[TD="width: 87"]Type A[/TD]
[TD="width: 87, align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]123[/TD]
[TD]Type B[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]123[/TD]
[TD]Type C[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]123[/TD]
[TD]Type D[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]124[/TD]
[TD]Type D[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]125[/TD]
[TD]Type D[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]125[/TD]
[TD]Type A[/TD]
[TD="align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]

 

[Matty]

Hi,

I'd do something like this...

Sheet2 (A1:D10):

[TABLE="class: tableizer-table"]
<tbody>[TR="class: tableizer-firstrow"]
[TH]IDs[/TH]
[TH]Description[/TH]
[TH]Type[/TH]
[TH]ID|Type[/TH]
[/TR]
[TR]
[TD]123[/TD]
[TD]text[/TD]
[TD]Type A[/TD]
[TD]123|Type A[/TD]
[/TR]
[TR]
[TD]123[/TD]
[TD]text[/TD]
[TD]Type B[/TD]
[TD]123|Type B[/TD]
[/TR]
[TR]
[TD]123[/TD]
[TD]text[/TD]
[TD]Type A[/TD]
[TD]123|Type A[/TD]
[/TR]
[TR]
[TD]123[/TD]
[TD]text[/TD]
[TD]Type B[/TD]
[TD]123|Type B[/TD]
[/TR]
[TR]
[TD]123[/TD]
[TD]text[/TD]
[TD]Type C[/TD]
[TD]123|Type C[/TD]
[/TR]
[TR]
[TD]123[/TD]
[TD]text[/TD]
[TD]Type D[/TD]
[TD]123|Type D[/TD]
[/TR]
[TR]
[TD]124[/TD]
[TD]text[/TD]
[TD]Type D[/TD]
[TD]124|Type D[/TD]
[/TR]
[TR]
[TD]125[/TD]
[TD]text[/TD]
[TD]Type D[/TD]
[TD]125|Type D[/TD]
[/TR]
[TR]
[TD]125[/TD]
[TD]text[/TD]
[TD]Type A[/TD]
[TD]125|Type A[/TD]
[/TR]
</tbody>[/TABLE]

D2 copied down:

=C2&"|"&E2

Sheet3 (A1:D8):

[TABLE="class: tableizer-table"]
<tbody>[TR="class: tableizer-firstrow"]
[TH]Unique ID|Type[/TH]
[TH]ID[/TH]
[TH]Type[/TH]
[TH]Count[/TH]
[/TR]
[TR]
[TD]123|Type A[/TD]
[TD]123[/TD]
[TD]Type A[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]123|Type B[/TD]
[TD]123[/TD]
[TD]Type B[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]123|Type C[/TD]
[TD]123[/TD]
[TD]Type C[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]123|Type D[/TD]
[TD]123[/TD]
[TD]Type D[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]124|Type D[/TD]
[TD]124[/TD]
[TD]Type D[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]125|Type D[/TD]
[TD]125[/TD]
[TD]Type D[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]125|Type A[/TD]
[TD]125[/TD]
[TD]Type A[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]

A2 copied down (array entered):

=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:

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

C2 copied down:

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

D2 copied down:

=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]

Another option:-
Data & Results start row 1
Data on sheet 1 & sheet 2, Results on sheet 3.

[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]

Thanks Mick and Matty

I solved it using Pivot tables.

Thanks for your time and effort.

BR
R

 

[MickG]

You're welcome