[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 179
  • 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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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