• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 181
  • Last Modified:

Count like values in multiple columns of a table

I am attempting to count the number of times someones name displays in a cell.

Example
 Screen shot of data
0
kel-o
Asked:
kel-o
1 Solution
 
barry houdiniCommented:
So what results do you want?

If you want to count a specific name in that whole range try COUNTIF, e.g.

=COUNTIF(A$2:E$100,G2)

where G2 contains a specific name

regards, barry
0
 
kel-oAuthor Commented:
Hi Barry,
   Thank you for the response. I am trying to get a count of how many times a unique name shows up in the entire table. For example, I want to have a count of how many times Stacy Snyder's name shows up in the entire table. End result - I would like to have one column show all the unique names and a column next to it count how many times that name displayed in the table.
Regards,
Kelly
0
 
nutschCommented:
Hi Kelly,

The attached code should do what you require.

Thomas

Sub Macro2()
Dim sht As Worksheet, rg As Range, i As Long

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Set rg = Range("A1").CurrentRegion.Offset(1)
Set sht = ActiveWorkbook.Sheets.Add
rg.Copy sht.[a1]

With sht
    
    For i = 1 To rg.Columns.Count
    
        If i > 1 Then .Range(.Cells(1, i), .Cells(Rows.Count, i).End(xlUp)).Cut .Cells(.Rows.Count, 1).End(xlUp).Offset(1)
              
    Next

    .Range(.Cells(1, 1), .Cells(Rows.Count, 1).End(xlUp)).RemoveDuplicates Columns:=1, Header:=xlNo

    .Range("B1:B" & .Cells(Rows.Count, 1).End(xlUp).Row).FormulaR1C1 = "=countif('" & rg.Parent.Name & "'!" & rg.Address(True, True, xlR1C1) & ",RC[-1])"

End With

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Tackle projects and never again get stuck behind a technical roadblock.
Join Now