Source
r/excel•
CharlieFlags

Counting names in columns to produce an auto-updating report of their frequencies?

solved

Hi there,

So i'm trying to count the number of names in a given column that would then print to another place in the sheet/workbook. I won't know the names in advance, and i would like the printed list to update as i add names. Then, next to those values, i'd like to count how many times they appear in the column. So as an example, this column:

Atlanta Falcons
Jaylon Smith
Reggie Ragland
Emmanuel Ogbah
Reggie Ragland
Emmanuel Ogbah
Darron Lee
Jaylon Smith
Jack Conklin
Braxton Miller
Jaylon Smith
Reggie Ragland
Vonn Bell
Jaylon Smith
Jayron Kearse

Would become:

Atlanta Falcons
Jaylon Smith          4
Reggie Ragland        3
Emmanuel Ogbah        2
Darron Lee            1
Jack Conklin          1
Braxton Miller        1
Vonn Bell             1
Jayron Kearse         1

Then, as i add more names to the initial list, that list of frequencies would change, add on the new names, and update. Anyone got any ideas about how to do this? I'm on Excel 2013 for Mac. Thanks in advance for your help!

Posted Feb 9, 2016, 5:11 PM UTC

731
fuzzius_navus449•

You can easily use a Pivot Table for this.

  1. Select your Range, format as Table

  2. Insert>Pivot Table, from Table1 (or whatever your table is named)

  3. Put the Names field in the Rows, it will only show unique.

  4. Put the Names field in the Values, it will now show you a count

The nice thing about this, is as you add Table rows in your data range, the Pivot Table can be updated by just clicking Refresh. No VBA or formulas required.

2
eirunning85360•

Except you'd need dynamic named ranges or to change data source every time ;)

But yes, probably the better approach.

1
fuzzius_navus449•

Possibly. Formatted tables can often resolve the dynamic range issue. For fun, create a table. Delete all rows (except the header...) then paste in new data. Most times, it'll keep all the new data in the table. New rows are often included if they're added immediately below the table (but, admittedly, not always...)

1
eirunning85360•

Open to using vba?

1
CharlieFlagsOP•

I've never used it before, but if you've got a good enough guide as to how to use it, then i'm open to anything.

1
eirunning85360•

Sweet. Should be able to get it done and walk you through using it. Give me a few min

1
CharlieFlagsOP•

Awesome, thanks!

1
eirunning85360•

No problem. Alright, couple quick questions! Where is the original list (I assumed it starts in A1, but to edit this is easy)? Where do you want the short list with counts (I just exported it to C1 for demonstration purposes)?

Here's pics: http://i.imgur.com/aq5YSP6.png

And the code:

Sub Counter()

Dim i As Long, nameCol As Long, lastRowName As Long
Dim exportCol As Long, lastRowExport As Long
Dim Found As Range

nameCol = 1
exportCol = 3
lastRowName = Cells(Rows.Count, nameCol).End(xlUp).Row

Cells(1, exportCol) = Cells(1, nameCol)

For i = 2 To lastRowName
    lastRowExport = Cells(Rows.Count, exportCol).End(xlUp).Row
    Set Found = Range(Cells(1, exportCol), Cells(lastRowExport, exportCol)).Find(Cells(i, nameCol), _
    LookIn:=xlValues, lookat:=xlWhole)
    If Found Is Nothing Then
        Cells(lastRowExport + 1, exportCol) = Cells(i, nameCol)
        Cells(lastRowExport + 1, exportCol + 1) = 1
    Else
        Cells(Found.Row, exportCol + 1) = Cells(Found.Row, exportCol + 1) + 1
    End If
Next i
    

End Sub

To do this, you need to open the VBEditor (hit ALT+F11), insert a module, copy and past this code in, and then run it. To insert a module this should help: https://www.youtube.com/watch?v=QtP5bc2FqY4

If you get this far, run it, and it works, and it's functionality you want to have all the time we can add in a button to make it a bit more user friendly! Let me know how this goes and if you have any questions! :)

1
eirunning85360•

To be fair, u/fuzzius_navus 's solution is a better idea. I would simply suggest a dynamic named range be setup, following this guide: http://chandoo.org/wp/2009/10/15/dynamic-chart-data-series/

You only need steps 1-3 :)

1
ZebZ7•

Assuming the names are in column A, put this in B2 and fill down.

=COUNTIF(A:A, A2)
1
eirunning85360•

OP said he doesn't know how long the list of names will be or has a list of the unique names

1
CharlieFlagsOP•

I thought that would work for the creation of the frequencies, but it's the creation of this new column with single occurrences of names away from the first column that's the real issue!

1
sane--3•

There may be a VBA function that will help you do this, but Google Sheets has a built-in function that makes it really easy.

Say your names from Sheet1 are in column A. In Sheet2, Column A, you can use this formula:

=UNIQUE(Sheet1!A:A)

Then in cell B1 on Sheet2, use this array formula to count the occurrences of those unique names (you'll only need to enter this in Cell B1, don't worry about filling down):

=arrayformula(countif(Sheet1!A:A,filter(Sheet2!A:A,Sheet2!A:A<>"")))

adding the =if() statement there will let you fill the formula down to the bottom of the sheet, but only populate for cells adjacent to unique names (that way you don't have to fill down each time new names are added to the list).

The =UNIQUE() and =FILTER() functions used in this solution are unique to Google Sheets, unless you recreate them in Excel with VBA.

1
CharlieFlagsOP•

This is brilliant - thanks for this! Does exactly what i want. I'm not sure about using google sheets as my main program for the project, so i'll see if i can get this VBA stuff working too, but you've been a great help!

1
sane--3•

Yeah, I have no idea how you're pulling or auto-populating data, and it may be that Excel is what you need for that portion of the task.

There are Google Sheet functions for importing data feeds into sheets, though.

1
CharlieFlagsOP•

I'm just inputting data by hand, so nothing complex - i just pasted the original spreadsheet in and it works perfectly for what i need. I've never used google sheets for any big tasks before, but i might make the switch this time - going to have a look at the pros and cons. Seems to be ideal, though.

1
sane--3•

I'm in the minority around here, but I use it for almost all projects, both work and personal. There are definitely limits compared to Excel, but it goes both ways.

1
CharlieFlagsOP•

Hey - I ended up using this method and making a separate sheet with all my info in it - I managed to work out how to do some stuff like making an overall count in addition, too. Thanks for your help (and for your advocacy of Google Sheets!), Solution Verified!

1
Clippy_Office_AsstMOD•

You have awarded one point to sane--.
^Find ^out ^more ^here.

1
sane--3•

Cool! Glad you found it helpful.

1
[deleted]•

[deleted]

1