Solved

Count like values in multiple columns of a table

Posted on 2011-03-16
3
169 Views
Last Modified: 2012-08-13
I am attempting to count the number of times someones name displays in a cell.

Example
 Screen shot of data
0
Comment
Question by:kel-o
3 Comments
 
LVL 50

Expert Comment

by:barry houdini
ID: 35151797
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 Comment

by:kel-o
ID: 35151972
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
 
LVL 39

Accepted Solution

by:
nutsch earned 250 total points
ID: 35152682
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

912 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now