alexkim
asked on
Sorting multiple rows and columns, and count duplicates in Excel 2013
Hi Experts,
I'm working in Excel spreadsheet exported from a Sharepoint survey solution. The spreadsheet contains names of the people from the survey in a table.
There are multiple duplicates in various cells within this table.
I am trying to do the following:
1. Sort the names in Alphabetical order
2. Count the number of names
3. A tally of Names with number of duplicates (if possible)
Is there a tool or formula that I can use to achieve this? A sample of input data and also what I would like to achieve as an output is attached.
Thanks in advance!
input.jpg
output.jpg
I'm working in Excel spreadsheet exported from a Sharepoint survey solution. The spreadsheet contains names of the people from the survey in a table.
There are multiple duplicates in various cells within this table.
I am trying to do the following:
1. Sort the names in Alphabetical order
2. Count the number of names
3. A tally of Names with number of duplicates (if possible)
Is there a tool or formula that I can use to achieve this? A sample of input data and also what I would like to achieve as an output is attached.
Thanks in advance!
input.jpg
output.jpg
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you Subodh, this is the exact solution I was looking for - you have just made my day!!
You're welcome Alex! Glad to help.
1) Copy and paste your responses into a single column. Make sure that data has a column header, such as "Name"
2) Create a PivotTable based on that, with Name as a Row field, and Count of Name as a Value field
3) Sort the PivotTable on the Name column