Count dynamic range

[Abhijeet]

Hi
I have data in excel i want auto update count the name column data.I have mention in attach file expected data.please tell me how to do this

 

[Sathish KV]

Hi,

Just use a pivot table!

 

[Khalid NGO]

Hi Abhijeet,

Not sure about your requirement.
COUNTIF Formula is okay in your sheet.

 

[Faseeh]

You want to Update Name or Count?

 

[Abhijeet]

I want count of in Name column mention but range is dynamic update automatic when in put Name any in column then that name & count update in Table in particular column.

 

[Somendra Misra]

Hi Try below code:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim lr As Long, lr1 As Long
lr = Range("A1048576").End(xlUp).Row
Application.ScreenUpdating = False
lr1 = Range("D1048576").End(xlUp).Row

If Not Intersect(Target, Range("A:A")) Is Nothing Then
    Range("D2:D" & lr1).Clear
    Range("A2:A" & lr).Copy Range("C2")
    lr1 = Range("C1048576").End(xlUp).Row
    Range("$C$2:$C$" & lr1).RemoveDuplicates Columns:=1, Header:=xlNo
    lr1 = Range("C1048576").End(xlUp).Row
    For i = 2 To lr1
        Range("D" & i).Value = Application.WorksheetFunction.CountIf(Range("A2:A" & lr), Range("C" & i))
    Next i
End If

Application.ScreenUpdating = True

   
End Sub

Regards,

 

[Abhijeet]

Can u please upload the file i not able to run this macro

 

[Somendra Misra]

Hi,

See the file.

Regards,

 

[Abhijeet]

Thanks Somendra Misra its working fine

 

[Hui]

Abhijet

I added a small piece of code to Somendra's code which will sort the output by Name, The Code also has instructions on how to sort it by Count

See attached:

 

[Abhijeet]

Thanks Hui i need this sort data and u give me this macro.Thanks again both of u