count unique and same with reference to its value

[sanjyou]

I need to find count for unique and for same multiple value

( ram task Assigned To same type of Names e.g hari=2 times and sanju= 3 times ) result =5

( ram task Assigned to different type of Names e.g anu=1 times,raju= 1 times,shyam = 1 times )=3

please help !!!!!!!!!

 

[mumps]

It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[sanjyou]

shall i share google sheet link ...is it ok ?

 

[sanjyou]

sample5.xlsx

drive.google.com

 

[mumps]

Try:

Sub CountUnique()
    Application.ScreenUpdating = False
    Dim LastRow As Long, srcWS As Worksheet, desWS As Worksheet, arr As Variant, dic As Object, dic2 As Object
    Dim Val As String, key As Variant, rng As Range, x As Long, y As Long, fVisRow As Long, lVisRow As Long
    Set srcWS = Sheets("raw data")
    Set desWS = Sheets("summary")
    With srcWS
        LastRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        arr = .Range("A2", .Range("A" & .Rows.Count).End(xlUp)).Value
    End With
    Set dic = CreateObject("Scripting.Dictionary")
    Set dic2 = CreateObject("Scripting.Dictionary")
    For i = 1 To UBound(arr, 1)
        Val = arr(i, 1)
        If Not dic.Exists(Val) Then
            dic.Add Val, Nothing
        End If
    Next i
    For Each key In dic
        With srcWS
            .Cells(1, 1).CurrentRegion.AutoFilter 1, key
            fVisRow = Rows("2:" & LastRow).SpecialCells(xlCellTypeVisible).Row
            lVisRow = Cells(Rows.Count, "A").End(xlUp).Row
            For Each rng In .Range("B2:B" & LastRow).SpecialCells(xlCellTypeVisible)
                If WorksheetFunction.CountIf(.Range("B" & fVisRow & ":B" & lVisRow), rng) > 1 Then
                    If Not dic2.Exists(rng.Value) Then
                        dic2.Add rng.Value, Nothing
                        x = x + 1
                    Else
                        x = x + 1
                    End If
                ElseIf WorksheetFunction.CountIf(.Range("B" & fVisRow & ":B" & lVisRow), rng) = 1 Then
                    y = y + 1
                End If
            Next rng
        End With
        With desWS
            .Cells(.Rows.Count, "A").End(xlUp).Offset(1).Resize(, 3) = Array(key, x, y)
        End With
        x = 0
        y = 0
        dic2.RemoveAll
    Next key
    Application.ScreenUpdating = True
End Sub

 

[mumps]

Forgot to insert this line of code:

srcWS.Range("A1").AutoFilter

immediately below this line:

Next key

 

[sanjyou]

i want to do it with excel formula...in google sheets..so can you have me with formula that is suitable with google sheets...

 

[mumps]

I'm sorry but formulas are not my strong point.

 

[Fluff]

@sanjyou
When asking about Google Sheets, please post to the correct forum & also state that it's for Sheets. That way it saves members from wasting their offering a solution that doesn't work.
Thanks

 

[Fluff]

No idea if this works in Sheets, but try

=SUMPRODUCT((A2:A20="ram")*(COUNTIFS(B2:B20,B2:B20&"")>1))

and

=SUMPRODUCT((A2:A20="ram")*(COUNTIFS(B2:B20,B2:B20&"")=1))