Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 09-11-2014, 10:13 AM
cr2crf cr2crf is offline Need a sort and index formula Windows 7 32bit Need a sort and index formula Office 2007
Novice
Need a sort and index formula
 
Join Date: Sep 2014
Posts: 1
cr2crf is on a distinguished road
Default Need a sort and index formula

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:

smith smith 4
jones jones 3
smith williams 2
smith Barns 1
jones
jones
Williams
Barns
Smith
Williams

Thanks
Reply With Quote
  #2  
Old 09-11-2014, 04:09 PM
gebobs gebobs is offline Need a sort and index formula Windows 7 64bit Need a sort and index formula Office 2010 64bit
Expert
 
Join Date: Mar 2014
Location: Atlanta
Posts: 837
gebobs has a spectacular aura aboutgebobs has a spectacular aura about
Default

Copy the list to another column with an intervening blank column. Select that column and do Data:Remove Duplicates.

To the right of the first name in the now duplicate free column, enter the formula:

=countif(a:a,c1)

Copy the equation down to the end of the list.
Reply With Quote
  #3  
Old 09-12-2014, 06:20 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Need a sort and index formula Windows 7 64bit Need a sort and index formula Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

And, of course you can use a Pivot Table
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Need help with Index formula gbaker Excel 7 02-07-2014 06:34 AM
Complex Formula using INDEX and Match needed OTPM Excel 5 05-23-2013 01:22 AM
Help with multiple match and index formula ryanwood Excel 1 09-12-2012 07:53 AM
Sort Macros boutells Excel 1 07-16-2009 11:48 AM
Sort Range boutells Excel 1 07-15-2009 03:02 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 08:34 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft