Finding Distinct Names and Counting associated numbers with them

[fahadferoz]

Hi

Please have a look at the following spreadsheet for details. The requirements are mentioned there as well.

Thank you for your help!

 

[Monty]

Hello fahadferoz

You can use Advance filter for this does not require any macro.

Please let me know any challenges ...Happy to help you.

 

[fahadferoz]

I am sorry but I have not used advanced filter. Could you please apply it in attached spreadsheet.

Thanks for your understanding!

 

[Monty]

May be this to try with macro

Sub UniqueValues()
Dim ws As Worksheet
Dim uniqueRng As Range
Dim myCol As Long

myCol = 2
Set ws = ThisWorkbook.Worksheets("Sheet1")

Set uniqueRng = GetUniqueValues(ws, myCol)

End Sub


Function GetUniqueValues(ws As Worksheet, col As Long) As Range
Dim firstRow As Long

With ws
    .Columns(col).RemoveDuplicates Columns:=Array(1), Header:=xlNo

    firstRow = 1
    If IsEmpty(.Cells(1, col)) Then firstRow = .Cells(1, col).End(xlDown).Row

    Set GetUniqueValues = Range(.Cells(firstRow, col), .Cells(.Rows.Count, col).End(xlUp))
End With

End Function

 

[shrivallabha]

You can also try to use Pivot Table. Instead of "SUM" which will come as default, choose "COUNT". See attached file for your reference (had to delete few rows as it was too big for upload).

If you've not used this feature before then you can find several resources online to learn it like below.
http://www.excel-easy.com/data-analysis/pivot-tables.html

 

[rahulshewale1]

hii,

please find the attached solution using pivot tabel .it is very easy and fast with out using long formula

thanks
rahul shewale

 

[fahadferoz]

hii,

please find the attached solution using pivot tabel .it is very easy and fast with out using long formula

thanks
rahul shewale

Thanks Rahul!

 

[fahadferoz]

You can also try to use Pivot Table. Instead of "SUM" which will come as default, choose "COUNT". See attached file for your reference (had to delete few rows as it was too big for upload).

Thanks a LOTT!!

 

[fahadferoz]

Thanks a LOT!! And I liked it.....

 

[fahadferoz]

May be this to try with macro

Sub UniqueValues()
Dim ws As Worksheet
Dim uniqueRng As Range
Dim myCol As Long

myCol = 2
Set ws = ThisWorkbook.Worksheets("Sheet1")

Set uniqueRng = GetUniqueValues(ws, myCol)

End Sub


Function GetUniqueValues(ws As Worksheet, col As Long) As Range
Dim firstRow As Long

With ws
    .Columns(col).RemoveDuplicates Columns:=Array(1), Header:=xlNo

    firstRow = 1
    If IsEmpty(.Cells(1, col)) Then firstRow = .Cells(1, col).End(xlDown).Row

    Set GetUniqueValues = Range(.Cells(firstRow, col), .Cells(.Rows.Count, col).End(xlUp))
End With

End Function

No Monty, it does not work. I tried it in Macro. It does not do it. You can try it yourself.