software

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: www.excelforum.com/member.php...oamp;userid=31519
View this thread: www.excelforum.com/showthread...hreadid=512036
try this

1) sort your list (maybe copy to another sheet first)
2) in cell c1 and copied down
=IF(C1gt;C2,C1,0)
3) in cell d1 and copied down
=IF(C2gt;C3,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: www.excelforum.com/member.php...oamp;userid=11624
View this thread: www.excelforum.com/showthread...hreadid=512036
Duane,

Thanks for the reply. I followed you until step #5.

How, exactly, do I quot;sort columns B thru d based on column d
(descending)quot;?

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: www.excelforum.com/member.php...oamp;userid=31519
View this thread: www.excelforum.com/showthread...hreadid=512036
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: www.excelforum.com/member.php...oamp;userid=31519
View this thread: www.excelforum.com/showthread...hreadid=512036On Mon, 13 Feb 2006 20:22:29 -0600, juggo
gt; wrote:

gt;
gt;Hey,
gt;
gt;Here's my problem/question.
gt;
gt;I have a big excel file with about 6000 rows of data (mostly text).
gt;The 6,000 entries in Column B are all text and that is the data I'm
gt;interested in. Each entry contains a person's name. My goal is to
gt;figure out which name's are the most commonly listed. Yes I could just
gt;sort by that particular column and I've done that. But with 6000
gt;entries, it's still hard to tell which are listed the most.
gt;
gt;So my aim would be something like the following...
gt;
gt;If I have this in Column B (shorter version, what is listed after the
gt;'--' would be the cell contents):
gt;Row1 -- Jake Scott
gt;Row2 -- Donald Smith
gt;Row3 -- Tim Matthews
gt;Row4 -- Donald Smith
gt;Row5 -- Jake Scott
gt;Row6 -- Donald Smith
gt;
gt;For it to be able to tell me that 'Jake Scott' occurs 2 times, 'Donald
gt;Smith' occurs 3 times, and 'Tim Matthews' occurs 1 time. And then sort
gt;those numbers so I can have a list of the most common entries.
gt;
gt;Would this be possible? If so, how? If not, any other ideas outside
gt;of Excel?
gt;
gt;regards,
gt;
gt;Jon

You could use a Pivot Table, or Data/Sort Data/Subtotals

1. Insert a quot;Label Rowquot; 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 lt;2gt; 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

Hi!

The easy way:

Use an advanced filter and copy the unique entries to another column. Then
use a Countif formula.

Biff

quot;juggoquot; gt; wrote in
message ...
gt;
gt; Hey,
gt;
gt; Here's my problem/question.
gt;
gt; I have a big excel file with about 6000 rows of data (mostly text).
gt; The 6,000 entries in Column B are all text and that is the data I'm
gt; interested in. Each entry contains a person's name. My goal is to
gt; figure out which name's are the most commonly listed. Yes I could just
gt; sort by that particular column and I've done that. But with 6000
gt; entries, it's still hard to tell which are listed the most.
gt;
gt; So my aim would be something like the following...
gt;
gt; If I have this in Column B (shorter version, what is listed after the
gt; '--' would be the cell contents):
gt; Row1 -- Jake Scott
gt; Row2 -- Donald Smith
gt; Row3 -- Tim Matthews
gt; Row4 -- Donald Smith
gt; Row5 -- Jake Scott
gt; Row6 -- Donald Smith
gt;
gt; For it to be able to tell me that 'Jake Scott' occurs 2 times, 'Donald
gt; Smith' occurs 3 times, and 'Tim Matthews' occurs 1 time. And then sort
gt; those numbers so I can have a list of the most common entries.
gt;
gt; Would this be possible? If so, how? If not, any other ideas outside
gt; of Excel?
gt;
gt; regards,
gt;
gt; Jon
gt;
gt;
gt; --
gt; juggo
gt; ------------------------------------------------------------------------
gt; juggo's Profile:
gt; www.excelforum.com/member.php...oamp;userid=31519
gt; View this thread: www.excelforum.com/showthread...hreadid=512036
gt;