Don't suppose we have any Excel experts (or dabble...
# _general
j
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....
j
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
b
you can create a formula (UNIQUE) or with a Notepad++ plugin. Have to look it up tomorrow.
j
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 😞
r
✔️ 1
j
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
s
Export as CSV, use PowerShell 😄
👍🏻 1
d
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)”
l
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
I've used CHatGPT recently to do a few excel formulas ...