# Thread: Counting unique values Thanks: 0 Likes: 0

1. ## Counting unique values

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

Thanks and best regards,
R

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

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

I expect a result like below in sheet 3 for e.g.:
 123 Type A 2 123 Type B 2 123 Type C 1 123 Type D 1 124 Type D 1 125 Type D 1 125 Type A 1

2. ## Re: Counting unique values

Hi,

I'd do something like this...

Sheet2 (A1:D10):

IDs Description Type ID|Type
123 text Type A 123|Type A
123 text Type B 123|Type B
123 text Type A 123|Type A
123 text Type B 123|Type B
123 text Type C 123|Type C
123 text Type D 123|Type D
124 text Type D 124|Type D
125 text Type D 125|Type D
125 text Type A 125|Type A

D2 copied down:

Code:
`=C2&"|"&E2`
Sheet3 (A1:D8):

Unique ID|Type ID Type Count
123|Type A 123 Type A 2
123|Type B 123 Type B 2
123|Type C 123 Type C 1
123|Type D 123 Type D 1
124|Type D 124 Type D 1
125|Type D 125 Type D 1
125|Type A 125 Type A 1

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

3. ## Re: Counting unique values

Another option:-
Data & Results start row 1
Data on sheet 1 & sheet 2, Results on sheet 3.
Code:
```Sub MG01Mar40
Dim Rng As Range, Dn As Range, n As Long
Dim RngA As Range
Dim Dic As Object
Dim Q As Variant, k As Variant, p As Variant, c As Long

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

With Sheets("Sheet2")
Set RngA = .Range("A1", .Range("A" & Rows.Count).End(xlUp))
End With
Set Dic = CreateObject("Scripting.Dictionary")
Dic.CompareMode = 1
For Each Dn In RngA
If .exists(Dn.Value) Then
If Not Dic.exists(Dn.Value) Then
Set Dic(Dn.Value) = CreateObject("Scripting.Dictionary")
End If

If Not Dic(Dn.Value).exists(Dn.Offset(, 2).Value) Then
Else
Q = Dic(Dn.Value).Item(Dn.Offset(, 2).Value)
Q = Q + 1
Dic(Dn.Value).Item(Dn.Offset(, 2).Value) = Q
End If
End If
Next Dn
End With

With Sheets("Sheet3")
For Each k In Dic.Keys
For Each p In Dic(k)
c = c + 1
.Cells(c, 1) = k
.Cells(c, 2) = p
.Cells(c, 3) = Dic(k).Item(p)
Next p

Next k
End With
End Sub```
Regards Mick

4. ## Re: Counting unique values

Thanks Mick and Matty

I solved it using Pivot tables.

Thanks for your time and effort.

BR
R

5. ## Re: Counting unique values

You're welcome