0

I am very new to Excel. I need some help in counting unique values in each of three columns (considering them only one at a time) with respect to a parent column (the first one (A) in the image). Can you please help me out out? Thanks!

Edit: If I do a pivot table it doesn't give me count of individual columns but the final column.

Source Data:

enter image description here

Result Data after transformation:

enter image description here

Source

A   B   C   D
p   d   g   k
p   e   g   k
q   d   h   k
q   f   h   o
r   d   h   o
r   e   i   m
r   f   h   m
s   d   i   n
s   d   i   o
s   f   i   o

Result

A   B   C   D
p   2   1   1
q   2   1   2
r   3   2   2
s   2   1   2
3
  • 1
    Welcome to Super User! Please note that Super User is not a script writing service. If you tell us what you have tried so far (including any scripts you are using) and where you are stuck then we can try to help with specific problems. You should also read How do I ask a good question?.
    – DavidPostill
    May 19, 2016 at 11:10
  • 1
    See my answer HERE May 19, 2016 at 14:14
  • I can't figure out what you're trying to do from your example.
    – fixer1234
    May 20, 2016 at 4:38

1 Answer 1

0

We build a list of the column A uniques and put it in column E and then perform a set of loops to count the uniques in the other columns:

Sub Macro1()
    Dim N As Long, i As Long
    Dim c As Collection, v As Variant
    Dim M As Long, j As Long, rc As Long
    Dim K As Long

    Columns("A:A").Copy Columns("E:E")
    Range("E:E").RemoveDuplicates Columns:=1, Header:=xlNo

    rc = Rows.Count
    M = Cells(rc, "A").End(xlUp).Row
    N = Cells(rc, "E").End(xlUp).Row

    For i = 1 To N
        v = Cells(i, "E").Text
            For K = 2 To 4
                Set c = Nothing
                Set c = New Collection
                For j = 1 To M
                    If v = Cells(j, "A").Text Then
                        On Error Resume Next
                            c.Add Cells(j, K).Text, CStr(Cells(j, K).Text)
                        On Error GoTo 0
                    End If
                Next j
                Cells(i, K + 4).Value = c.Count
            Next K
    Next i
End Sub

enter image description here

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .