Don't suppose we have any Excel experts (or dabblers) here - I have a column of data in a spreadsheet (actually a list of user names) and I would like to find out how many times each username appears in the list of data. Any easy way to achieve this? I know you can apply a filter and sort by each username in turn and it gives you a list of records with a total, but there are like 1200 usernames in this list so that seems very long and protracted....

A very manual way ..I'm sure there's a better way, but you can use countif 1:1 Assuming usernames are in in COL A and start on row 2. I'm a new column, in a cell enter =COUNTIF(A:A, A2) drag down to apply

you can create a formula (UNIQUE) or with a Notepad++ plugin. Have to look it up tomorrow.

COUNTIF was returning zero - until I realized in Director output the username has a leading space attached to it. Now I got to go sanitize the data 😞

I would probably use a pivot table but several ideas are shown here https://support.microsoft.com/en-us/office/count-how-often-a-value-occurs-aa1f3067-05c9-44e4-b141-f75bb9bb89bd

I would also use COUNTIF for that. Copy the column with the names and use the "Remove Duplicates" function to get all unique names. If column A contains all the data and column B contains the unique names, use this formula in column C to count the occurrences of the unique name in column A =COUNTIF($A$1:$A$1200;B1) Then drag it down to count the remaining usernames

Export as CSV, use PowerShell 😄

Open the file in PowerBI, way easier there, I think that it’s already done if you select that column and then use the option “count (distinct)”

pivot table is quick, that being said Excel's pivot table creation is a bit more obtuse than Google Sheets believe it or not. I used to use Sheets pivot tables auto suggestions quite a bit and that's the one place I like it better than excel

I've used CHatGPT recently to do a few excel formulas ...