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

[cr2crf]

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

 

[cyrilbrd]

Given in A1 of sheet 'cr2crf':

smith	smith	4
jones	jones	3
smith	Williams	2
smith	Barns	1
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]

I refreshed before I posted my solution, but after posting I see Cyrilbrd had a post 9 minutes before me so I removed mine.

 

[cr2crf]

Given in A1 of sheet 'cr2crf':

smith	smith	4
jones	jones	3
smith	Williams	2
smith	Barns	1
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]

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

 

[cyrilbrd]

I refreshed before I posted my solution, but after posting I see Cyrilbrd had a post 9 minutes before me so I removed mine.

No worries Bruce...