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

# 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

0
kel-o
1 Solution

Commented:
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

Author 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

Commented:
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)
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
``````
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.