Need a formula to sort/index/count - help!

cr2crf

New Member
Joined
Jun 19, 2013
Messages
29
I have a spreadsheet with names in column a. Many of the names are duplicates. What I am trying to do is find a way to sort them so that the names can be listed once with a corresponding number to the cell to the right telling me how many times the name is used.

Example:

Column A:
smith
jones
smith
smith
jones
jones
Williams
Barns
Smith
Williams

I want columns B and C to look like this:
smith 4
jones 3
williams 2
barns 1

Thanks
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

cyrilbrd

Well-known Member
Joined
Feb 2, 2012
Messages
4,071
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
Given in A1 of sheet 'cr2crf':
smithsmith4
jonesjones3
smithWilliams2
smithBarns1
jones
jones
Williams
Barns
smith
Williams

<tbody>
</tbody>

name range cr2cr =ROW('cr2crf '!$A$1:$A$1500)-ROW('cr2crf '!$A$1)+1 change range to fit your model
formula in B1 is =IFERROR(INDEX($A$1:$A$390,SMALL(IF(FREQUENCY(IF($A$1:$A$390<>"",MATCH("~"&$A$1:$A$390,$A$1:$A$390&"",0)),cr2cr),cr2cr),ROWS($B$1:B1))),"") Ctrl + Shift + Enter not just enter on a PC or Command + Return on a MAC.
formula in C1 is =IF(B1="","",COUNTIF($A$1:$A$1500,B1)) copied down till needed.

Would that be close to what you required?
 

skywriter

Well-known Member
Joined
Feb 15, 2014
Messages
1,642
I refreshed before I posted my solution, but after posting I see Cyrilbrd had a post 9 minutes before me so I removed mine.
 
Last edited:

cr2crf

New Member
Joined
Jun 19, 2013
Messages
29
Given in A1 of sheet 'cr2crf':
smithsmith4
jonesjones3
smithWilliams2
smithBarns1
jones
jones
Williams
Barns
smith
Williams

<tbody>
</tbody>

name range cr2cr =ROW('cr2crf '!$A$1:$A$1500)-ROW('cr2crf '!$A$1)+1 change range to fit your model
formula in B1 is =IFERROR(INDEX($A$1:$A$390,SMALL(IF(FREQUENCY(IF($A$1:$A$390<>"",MATCH("~"&$A$1:$A$390,$A$1:$A$390&"",0)),cr2cr),cr2cr),ROWS($B$1:B1))),"") Ctrl + Shift + Enter not just enter on a PC or Command + Return on a MAC.
formula in C1 is =IF(B1="","",COUNTIF($A$1:$A$1500,B1)) copied down till needed.

Would that be close to what you required?

I appreciate your work but Im new to this and don't quite know "range range cr2crf"
 

cyrilbrd

Well-known Member
Joined
Feb 2, 2012
Messages
4,071
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
I appreciate your work but Im new to this and don't quite know "range range cr2crf"
No worries.
Name Ranges are explained here: click on this link.
If you have difficulties just let us know.
In a nutshell:

  1. Choose Insert>Name>Define
  2. Type a name for the range, e.g. cr2cr instead of Namelist as seen in the picture

  3. In the Refers To box, enter an Offset formula that defines the range size, based on the number of items in the column, e.g.:
    =ROW('cr2crf '!$A$1:$A$1500)-ROW('cr2crf '!$A$1)+1 instead of =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1) as seen above...
  4. Click OK
 

Watch MrExcel Video

Forum statistics

Threads
1,108,955
Messages
5,525,882
Members
409,669
Latest member
JDCupps

This Week's Hot Topics

Top