VBA: unique list in second column based on unique value in first column

[kirankoushik]

Hello All,

I am a beginner to excel VBA coding and am trying to execute a task.
I have been struggling with a way to code in VBA the below requirement. Request your help in this regard.

I am trying to count the total of :

For every unique keyword in col C (excluding blanks), the total unique values in col E (including blanks)

From the above image:

For Key 1 in col C - unique values in Col E = 4

Thanks in Advance.

Kiran Koushik

 

[Marcelo Branco]

Maybe...

Array formula
=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(A2:A36,ROW(A2:A36)-ROW(A2),0,1)),IFERROR(MATCH(E2:E36,E2:E36,0),ROWS(E2:E36))),ROW(E2:E36)-ROW(E2)+1),1))
confirmed with Ctrl+Shift+Enter, not just Enter

M.

 

[Marcelo Branco]

EDIT

New version
=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(A2:A36,ROW(A2:A36)-ROW(A2),0,1)),IFERROR(MATCH(E2:E36,E2:E36,0),ROWS(E2:E36)+1)),ROW(E2:E36)-ROW(E2)+1),1))

M.

 

[kirankoushik]

EDIT

New version
=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(A2:A36,ROW(A2:A36)-ROW(A2),0,1)),IFERROR(MATCH(E2:E36,E2:E36,0),ROWS(E2:E36)+1)),ROW(E2:E36)-ROW(E2)+1),1))

M.

Hello Marcelo,

Thank you for helping me.

I tried the above formula and am getting an incorrect value of 8. Maybe I am not communicating the requirement correctly. Please find below image. I am looking for answer 23.

Also, if I can achieve it using VBA it would be helpful as there are other calculations in the sheet and have an active module for the excel.

Thank you,
kiran koushik

 

[kirankoushik]

Book1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Col A	Col B	Col C	Col D	Col E	Col F		Result
2	1	Irrelevant Data	Key 1	Irrelevant Data	val 1	Irrelevant Data		8
3	2	Irrelevant Data	Key 2	Irrelevant Data	val 3	Irrelevant Data
4	3	Irrelevant Data	Key 3	Irrelevant Data	val 4	Irrelevant Data
5	1	Irrelevant Data	Key 4	Irrelevant Data	val 5	Irrelevant Data
6	2	Irrelevant Data	Key 5	Irrelevant Data	val 6	Irrelevant Data
7	6	Irrelevant Data	Key 6	Irrelevant Data	val 2	Irrelevant Data
8	7	Irrelevant Data	Key 7	Irrelevant Data	val 7	Irrelevant Data
9	8	Irrelevant Data	Key 5	Irrelevant Data	val 3	Irrelevant Data
10	9	Irrelevant Data	Key 6	Irrelevant Data		Irrelevant Data
11	10	Irrelevant Data	Key 1	Irrelevant Data	val 2	Irrelevant Data
12	1	Irrelevant Data	Key 6	Irrelevant Data	val 7	Irrelevant Data
13	1	Irrelevant Data	Key 1	Irrelevant Data	val 2	Irrelevant Data
14	2	Irrelevant Data	Key 6	Irrelevant Data		Irrelevant Data
15	3	Irrelevant Data		Irrelevant Data		Irrelevant Data
16	1	Irrelevant Data		Irrelevant Data		Irrelevant Data
17	2	Irrelevant Data	Key 2	Irrelevant Data	val 3	Irrelevant Data
18	6	Irrelevant Data	Key 3	Irrelevant Data	val 4	Irrelevant Data
19	7	Irrelevant Data	Key 7	Irrelevant Data	val 5	Irrelevant Data
20	8	Irrelevant Data	Key 7	Irrelevant Data		Irrelevant Data
21	9	Irrelevant Data	Key 2	Irrelevant Data		Irrelevant Data
22	10	Irrelevant Data	Key 1	Irrelevant Data		Irrelevant Data
23	1	Irrelevant Data	Key 4	Irrelevant Data	val 2	Irrelevant Data
24	1	Irrelevant Data	Key 3	Irrelevant Data		Irrelevant Data
25	2	Irrelevant Data		Irrelevant Data		Irrelevant Data
26	3	Irrelevant Data		Irrelevant Data		Irrelevant Data
27	1	Irrelevant Data	Key 4	Irrelevant Data		Irrelevant Data
28	2	Irrelevant Data	Key 3	Irrelevant Data	val 1	Irrelevant Data
29	6	Irrelevant Data	Key 7	Irrelevant Data		Irrelevant Data
30	7	Irrelevant Data	Key 5	Irrelevant Data		Irrelevant Data
31	8	Irrelevant Data	Key 1	Irrelevant Data	val 3	Irrelevant Data
32	9	Irrelevant Data	Key 4	Irrelevant Data	val 6	Irrelevant Data
33	10	Irrelevant Data	Key 3	Irrelevant Data	val 5	Irrelevant Data
34	1	Irrelevant Data	Key 4	Irrelevant Data	val 6	Irrelevant Data
35	9	Irrelevant Data	Key 5	Irrelevant Data		Irrelevant Data
36	10	Irrelevant Data	Key 4	Irrelevant Data		Irrelevant Data
Sheet1

Cell Formulas
Range	Formula
H2	H2	=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(A2:A36,ROW(A2:A36)-ROW(A2),0,1)),IFERROR(MATCH(E2:E36,E2:E36,0),ROWS(E2:E36)+1)),ROW(E2:E36)-ROW(E2)+1),1))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Marcelo Branco]

Hello Marcelo,

Thank you for helping me.

I tried the above formula and am getting an incorrect value of 8. Maybe I am not communicating the requirement correctly. Please find below image. I am looking for answer 23.

Also, if I can achieve it using VBA it would be helpful as there are other calculations in the sheet and have an active module for the excel.

Thank you,
kiran koushik

I thought you wanted the total filtering by Key 1, Key 2 ... etc

Try this

Pasta1
	H	I
1	Keys	Unique Count
2	Key 1	4
3	Key 2	2
4	Key 3	4
5	Key 4	4
6	Key 5	3
7	Key 6	3
8	Key 7	3
9	Total	23
Plan8

Cell Formulas
Range	Formula
I2:I8	I2	=SUM(IF(FREQUENCY(IF(C$2:C$36=H2,IFERROR(MATCH(E$2:E$36,E$2:E$36,0),ROWS(E$2:E$36)+1)),ROW(E$2:E$36)-ROW(E$2)+1),1))
I9	I9	=SUM(I2:I8)
Press CTRL+SHIFT+ENTER to enter array formulas.

M.

 

[kirankoushik]

I thought you wanted the total filtering by Key 1, Key 2 ... etc

Try this

Pasta1
	H	I
1	Keys	Unique Count
2	Key 1	4
3	Key 2	2
4	Key 3	4
5	Key 4	4
6	Key 5	3
7	Key 6	3
8	Key 7	3
9	Total	23
Plan8

Cell Formulas
Range	Formula
I2:I8	I2	=SUM(IF(FREQUENCY(IF(C$2:C$36=H2,IFERROR(MATCH(E$2:E$36,E$2:E$36,0),ROWS(E$2:E$36)+1)),ROW(E$2:E$36)-ROW(E$2)+1),1))
I9	I9	=SUM(I2:I8)
Press CTRL+SHIFT+ENTER to enter array formulas.

M.

Yes, Actually I wanted the total by filtering Key 1, Key 2,.. Key 7 .

i.e. Answer 23 in single cell

But the initial formula is giving answer 8.

Is there any way to incorporate the same in existing VBA module ?

 

[Marcelo Branco]

Yes, Actually I wanted the total by filtering Key 1, Key 2,.. Key 7 .

i.e. Answer 23 in single cell

But the initial formula is giving answer 8.

Is there any way to incorporate the same in existing VBA module ?

A solution using an Array Formula
=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(A2:A36,ROW(A2:A36)-ROW(A2),0,1)),IF(C2:C36<>"",MATCH(C2:C36&"|"&E2:E36,C2:C36&"|"&E2:E36,0))),ROW(E2:E36)-ROW(E2)+1),1))
Ctrl+Shift+Enter


VBA

Sub aTest()
    Dim rRngVisible, rRow As Range, lTotal As Long
    Dim dic As Object
    
    Set dic = CreateObject("Scripting.Dictionary")
    dic.CompareMode = vbTextCompare
    
    Set rRngVisible = Range("A2:F36").SpecialCells(xlCellTypeVisible)
    For Each rRow In rRngVisible.Rows
        If rRow.Cells(1, 3) <> "" Then
            dic(rRow.Cells(1, 3).Value & "|" & rRow.Cells(1, 5).Value) = ""
        End If
    Next rRow
    lTotal = dic.Count
    MsgBox lTotal 'Just for check
End Sub

M.

 

[kirankoushik]

A solution using an Array Formula
=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(A2:A36,ROW(A2:A36)-ROW(A2),0,1)),IF(C2:C36<>"",MATCH(C2:C36&"|"&E2:E36,C2:C36&"|"&E2:E36,0))),ROW(E2:E36)-ROW(E2)+1),1))
Ctrl+Shift+Enter


VBA

Sub aTest()
    Dim rRngVisible, rRow As Range, lTotal As Long
    Dim dic As Object
   
    Set dic = CreateObject("Scripting.Dictionary")
    dic.CompareMode = vbTextCompare
   
    Set rRngVisible = Range("A2:F36").SpecialCells(xlCellTypeVisible)
    For Each rRow In rRngVisible.Rows
        If rRow.Cells(1, 3) <> "" Then
            dic(rRow.Cells(1, 3).Value & "|" & rRow.Cells(1, 5).Value) = ""
        End If
    Next rRow
    lTotal = dic.Count
    MsgBox lTotal 'Just for check
End Sub

M.

Thanks a lot Marcelo for your time.

Both formula and VBA code work perfectly.

 

[Marcelo Branco]

You're welcome. Glad to help.

M.