Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Finding the most common cell duplicate (text)
Hey, Here's my problem/question. I have a big excel file with about 6000 rows of data (mostly text). The 6,000 entries in Column B are all text and that is the data I'm interested in. Each entry contains a person's name. My goal is to figure out which name's are the most commonly listed. Yes I could just sort by that particular column and I've done that. But with 6000+ entries, it's still hard to tell which are listed the most. So my aim would be something like the following... If I have this in Column B (shorter version, what is listed after the '--' would be the cell contents): Row1 -- Jake Scott Row2 -- Donald Smith Row3 -- Tim Matthews Row4 -- Donald Smith Row5 -- Jake Scott Row6 -- Donald Smith For it to be able to tell me that 'Jake Scott' occurs 2 times, 'Donald Smith' occurs 3 times, and 'Tim Matthews' occurs 1 time. And then sort those numbers so I can have a list of the most common entries. Would this be possible? If so, how? If not, any other ideas outside of Excel? regards, Jon -- juggo ------------------------------------------------------------------------ juggo's Profile: http://www.excelforum.com/member.php...o&userid=31519 View this thread: http://www.excelforum.com/showthread...hreadid=512036 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Finding the most common cell duplicate (text)
try this 1) sort your list (maybe copy to another sheet first) 2) in cell c1 and copied down =IF(C1C2,C1,0) 3) in cell d1 and copied down =IF(C2C3,C2,0) 4) copy columns c and d and paste special as values 5) sort columns B thru d based on column d (descending) to get a list of names and occurances - any duplicate names will have a zero in column d -- duane ------------------------------------------------------------------------ duane's Profile: http://www.excelforum.com/member.php...o&userid=11624 View this thread: http://www.excelforum.com/showthread...hreadid=512036 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Finding the most common cell duplicate (text)
Duane, Thanks for the reply. I followed you until step #5. How, exactly, do I "sort columns B thru d based on column d (descending)"? I went to the Data Sort dialogue box, but I don't see anything about sorting a column 'through' or 'based on' anything. regards, Jon -- juggo ------------------------------------------------------------------------ juggo's Profile: http://www.excelforum.com/member.php...o&userid=31519 View this thread: http://www.excelforum.com/showthread...hreadid=512036 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Finding the most common cell duplicate (text)
Nevermind, I think I found a way to do it through the 'Subtotals' function. Not the prettiest output, but it works. Thanks anyway. -- juggo ------------------------------------------------------------------------ juggo's Profile: http://www.excelforum.com/member.php...o&userid=31519 View this thread: http://www.excelforum.com/showthread...hreadid=512036 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Finding the most common cell duplicate (text)
On Mon, 13 Feb 2006 20:22:29 -0600, juggo
wrote: Hey, Here's my problem/question. I have a big excel file with about 6000 rows of data (mostly text). The 6,000 entries in Column B are all text and that is the data I'm interested in. Each entry contains a person's name. My goal is to figure out which name's are the most commonly listed. Yes I could just sort by that particular column and I've done that. But with 6000+ entries, it's still hard to tell which are listed the most. So my aim would be something like the following... If I have this in Column B (shorter version, what is listed after the '--' would be the cell contents): Row1 -- Jake Scott Row2 -- Donald Smith Row3 -- Tim Matthews Row4 -- Donald Smith Row5 -- Jake Scott Row6 -- Donald Smith For it to be able to tell me that 'Jake Scott' occurs 2 times, 'Donald Smith' occurs 3 times, and 'Tim Matthews' occurs 1 time. And then sort those numbers so I can have a list of the most common entries. Would this be possible? If so, how? If not, any other ideas outside of Excel? regards, Jon You could use a Pivot Table, or Data/Sort Data/Subtotals 1. Insert a "Label Row" at Row 1 2. B1: Names C1: Frequency C2: =COUNTIF(rng,B2) (where rng is a named reference or an absolute reference to your list of names in B2:Bn) 3. Fill down from C2:Cn 4. Data/Sort Descending on Frequency 5. Data/Subtotals At each change in Names Use Function Count Add Subtotal to Frequency Click on the <2 to collapse to just the Count of Names. ------------------ For a Pivot Table, no need for the second column. Just use the Pivot Table wizard; drag Names to Rows and Names to Data area. It should give you a count and, if necessary, you should be able to sort it. --ron |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Finding the most common cell duplicate (text)
Hi!
The easy way: Use an advanced filter and copy the unique entries to another column. Then use a Countif formula. Biff "juggo" wrote in message ... Hey, Here's my problem/question. I have a big excel file with about 6000 rows of data (mostly text). The 6,000 entries in Column B are all text and that is the data I'm interested in. Each entry contains a person's name. My goal is to figure out which name's are the most commonly listed. Yes I could just sort by that particular column and I've done that. But with 6000+ entries, it's still hard to tell which are listed the most. So my aim would be something like the following... If I have this in Column B (shorter version, what is listed after the '--' would be the cell contents): Row1 -- Jake Scott Row2 -- Donald Smith Row3 -- Tim Matthews Row4 -- Donald Smith Row5 -- Jake Scott Row6 -- Donald Smith For it to be able to tell me that 'Jake Scott' occurs 2 times, 'Donald Smith' occurs 3 times, and 'Tim Matthews' occurs 1 time. And then sort those numbers so I can have a list of the most common entries. Would this be possible? If so, how? If not, any other ideas outside of Excel? regards, Jon -- juggo ------------------------------------------------------------------------ juggo's Profile: http://www.excelforum.com/member.php...o&userid=31519 View this thread: http://www.excelforum.com/showthread...hreadid=512036 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I set text to top of cell next to wrap text in Excel? | New Users to Excel | |||
Wrap text ceases to function in Excel if cell exceeds 9 lines | Excel Worksheet Functions | |||
Formulas dealing with text data | Excel Worksheet Functions | |||
Finding Specific Text in a Text String | Excel Worksheet Functions | |||
Finding Duplicate text strings with a single column | Excel Worksheet Functions |