Pumpernickel
asked on
Excel Formula
In Excel, you can use COUNTIF() function to count the duplicates.
ASKER
That will count, but how will I list just 1 of each out?
Select a blank cell adjacent to the first data of your list, and type this formula =COUNTIF($A$2:$A$9, A2) (the range $A$2:$A$9 indicates the list of data, and A2 stands the cell you want to count the frequency, you can change them as you need), then press Enter, and drag the fill handle to fill the column you need. See screenshot:
Please find attached...
Count-Unique.xlsx
Count-Unique.xlsx
First Create a Dynamic Name Range called NameList with below formula:
=OFFSET(Sheet1!$A$2, 0, 0, COUNTA(Sheet1!$A$2:$A$1001))
To get the unique values add below formula in B2: with Ctrl+Shift+Enter=IFERROR(INDEX(NameList,MATCH(0,COUNTIF($B$1:B1,NameList),0)),"")
And for Counting add below formula in C2:=COUNTIF(A:A,B2)
C1=COUNTIF($A$1:$A$9, A1)
drag the fill handle to fill the column you need.
the range $A$1:$A$9 indicates the list of data,
and A1 stands the cell you want to count the frequency.
drag the fill handle to fill the column you need.
the range $A$1:$A$9 indicates the list of data,
and A1 stands the cell you want to count the frequency.
You can create a pivot table to do that.
It will create a list of unique entries and then can put a count for each.
See attached with simple pivot table.
Simple-Pivot.xlsx
It will create a list of unique entries and then can put a count for each.
See attached with simple pivot table.
Simple-Pivot.xlsx
ASKER
I've tried doing this, I can't get it to work. See attached on what I am trying to set.
Patches.xlsx
Patches.xlsx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Please find attached...
Patches_Count.xlsx
Patches_Count.xlsx
@Shums - your list still repeats the Server names, the OP requires a list of unique entries with a count against each.
Please find attached revised....
Patches_Count.xlsx
Patches_Count.xlsx
@Rob - OP didn't ask for Pivot initially. That would have been easy.
Maybe OP was not aware of existence of Pivot Tables, hence why they asked for formula.
I always read between the lines of a question and in general interpret a request such as this as "a way in Excel to do this..." rather than restricting solutions to the specific method requested in the question.
I always read between the lines of a question and in general interpret a request such as this as "a way in Excel to do this..." rather than restricting solutions to the specific method requested in the question.
ASKER
thanks, didn't know about pivot tables till now!
Glad to be of assistance.
Pivot Tables can be very powerful when you get to know them. It's worth doing some research; creating a list of unique entries with sums or counts is just the start of it.
Pivot Tables can be very powerful when you get to know them. It's worth doing some research; creating a list of unique entries with sums or counts is just the start of it.