Output the list of frequent data

Dear all,

    Here comes two problems.

   Problem 1: I have a list of strings (say, in the column A1:A100). How can
I find the "mode" (i.e. the string appearing most frequently in the list?
For example, if the list is

APPLE
APPLE
BANANA
APPLE
CREAM
CREAM
BANANA
CREAM
APPLE
DONUT

then I want the result is APPLE. It seems that the MODE function does not
suppot data type other than numbers. Is there any canned UDF for it?

    Problem 2: Following Problem 1, I want to generate a list of the 3 most
frequent data in the list. If the list is the one in the example, I want to
list to be

APPLE
CREAM
BANANA

It is because APPLE is the most frequent datum, followed by CREAM and
BANANA. Any canned UDF for it? (If two data occur equally frequently in the
list, it doesn't matter which comes first.)

    Thanks in advance!

Best Regards,
Andy Chan





0
12/25/2005 7:17:36 PM
excel 39879 articles. 2 followers. Follow

3 Replies
541 Views

Similar Articles

[PageSpeed] 28

Andy,
You can use a "CountIf" function formula in B1 and fill down.
=COUNTIF($A$1:$A$100,A1)
Then sort on Column B and you get...

APPLE 4
APPLE 4
APPLE 4
APPLE 4
CREAM 3
CREAM 3
CREAM 3
BANANA 2
BANANA 2
DONUT 1

Regards,
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware




"Andy Chan" 
<chankhandy-msnewsgroup@yahoo.com.hk> 
wrote in message news:43aef050$1@127.0.0.1
Dear all,
    Here comes two problems.

   Problem 1: I have a list of strings (say, in the column A1:A100). How can
I find the "mode" (i.e. the string appearing most frequently in the list?
For example, if the list is

APPLE
APPLE
BANANA
APPLE
CREAM
CREAM
BANANA
CREAM
APPLE
DONUT

then I want the result is APPLE. It seems that the MODE function does not
suppot data type other than numbers. Is there any canned UDF for it?

    Problem 2: Following Problem 1, I want to generate a list of the 3 most
frequent data in the list. If the list is the one in the example, I want to
list to be

APPLE
CREAM
BANANA

It is because APPLE is the most frequent datum, followed by CREAM and
BANANA. Any canned UDF for it? (If two data occur equally frequently in the
list, it doesn't matter which comes first.)

Thanks in advance!
Best Regards,
Andy Chan





0
jim.coneXXX (771)
12/25/2005 9:32:13 PM
Hi!

Probelm 1:

To find the mode of text values:

Entered as an array using the key combo of CTRL,SHIFT,ENTER:

=INDEX(A1:A100,MODE(MATCH(A1:A100,A1:A100,0)))

Problem 2:

This is a little more complicated. Use a helper column, assume column B. If 
desired, you can hide the helper column or put it in some out of sight 
location.

Enter this formula in B1 and copy down to B100:

=IF(COUNTIF(A$1:A1,A1)>1,0,COUNTIF(A$1:A$100,A1))

Now, to extract the top 3:

Also entered as an array using the key combo of CTRL,SHIFT,ENTER:

=INDEX(A1:A100,MATCH(LARGE(B$1:B$100-ROW(B$1:B$100)/10^10,ROWS($1:1)),B$1:B$100-ROW(B$1:B$100)/10^10,0))

Copy down 3 cells or as needed.

Note: you don't necessarily need the first mode formula since the top 3 
extracted will also include that result.

Biff

"Andy Chan" <chankhandy-msnewsgroup@yahoo.com.hk> wrote in message 
news:43aef050$1@127.0.0.1...
> Dear all,
>
>    Here comes two problems.
>
>   Problem 1: I have a list of strings (say, in the column A1:A100). How 
> can
> I find the "mode" (i.e. the string appearing most frequently in the list?
> For example, if the list is
>
> APPLE
> APPLE
> BANANA
> APPLE
> CREAM
> CREAM
> BANANA
> CREAM
> APPLE
> DONUT
>
> then I want the result is APPLE. It seems that the MODE function does not
> suppot data type other than numbers. Is there any canned UDF for it?
>
>    Problem 2: Following Problem 1, I want to generate a list of the 3 most
> frequent data in the list. If the list is the one in the example, I want 
> to
> list to be
>
> APPLE
> CREAM
> BANANA
>
> It is because APPLE is the most frequent datum, followed by CREAM and
> BANANA. Any canned UDF for it? (If two data occur equally frequently in 
> the
> list, it doesn't matter which comes first.)
>
>    Thanks in advance!
>
> Best Regards,
> Andy Chan
>
>
>
>
> 


0
biffinpitt (3172)
12/25/2005 9:48:41 PM
Ooops!

Slight correction:

> =INDEX(A1:A100,MATCH(LARGE(B$1:B$100-ROW(B$1:B$100)/10^10,ROWS($1:1)),B$1:B$100-ROW(B$1:B$100)/10^10,0))

Better make the row references to A1:A100 absolute before copying:

=INDEX(A$1:A$100,..........................)

Biff

"Biff" <biffinpitt@comcast.net> wrote in message 
news:uOj%236zZCGHA.2840@TK2MSFTNGP12.phx.gbl...
> Hi!
>
> Probelm 1:
>
> To find the mode of text values:
>
> Entered as an array using the key combo of CTRL,SHIFT,ENTER:
>
> =INDEX(A1:A100,MODE(MATCH(A1:A100,A1:A100,0)))
>
> Problem 2:
>
> This is a little more complicated. Use a helper column, assume column B. 
> If desired, you can hide the helper column or put it in some out of sight 
> location.
>
> Enter this formula in B1 and copy down to B100:
>
> =IF(COUNTIF(A$1:A1,A1)>1,0,COUNTIF(A$1:A$100,A1))
>
> Now, to extract the top 3:
>
> Also entered as an array using the key combo of CTRL,SHIFT,ENTER:
>
> =INDEX(A1:A100,MATCH(LARGE(B$1:B$100-ROW(B$1:B$100)/10^10,ROWS($1:1)),B$1:B$100-ROW(B$1:B$100)/10^10,0))
>
> Copy down 3 cells or as needed.
>
> Note: you don't necessarily need the first mode formula since the top 3 
> extracted will also include that result.
>
> Biff
>
> "Andy Chan" <chankhandy-msnewsgroup@yahoo.com.hk> wrote in message 
> news:43aef050$1@127.0.0.1...
>> Dear all,
>>
>>    Here comes two problems.
>>
>>   Problem 1: I have a list of strings (say, in the column A1:A100). How 
>> can
>> I find the "mode" (i.e. the string appearing most frequently in the list?
>> For example, if the list is
>>
>> APPLE
>> APPLE
>> BANANA
>> APPLE
>> CREAM
>> CREAM
>> BANANA
>> CREAM
>> APPLE
>> DONUT
>>
>> then I want the result is APPLE. It seems that the MODE function does not
>> suppot data type other than numbers. Is there any canned UDF for it?
>>
>>    Problem 2: Following Problem 1, I want to generate a list of the 3 
>> most
>> frequent data in the list. If the list is the one in the example, I want 
>> to
>> list to be
>>
>> APPLE
>> CREAM
>> BANANA
>>
>> It is because APPLE is the most frequent datum, followed by CREAM and
>> BANANA. Any canned UDF for it? (If two data occur equally frequently in 
>> the
>> list, it doesn't matter which comes first.)
>>
>>    Thanks in advance!
>>
>> Best Regards,
>> Andy Chan
>>
>>
>>
>>
>>
>
> 


0
biffinpitt (3172)
12/25/2005 10:13:34 PM
Reply:

Similar Artilces:

SQL in Excel data
Hi all, Is there a possibility/way to run an SQL query in an excel data sheet? I have quite some data like the sample below, now i would like to have the sum of spending for each person. Like it is possible in Access. A1 B1 Field1 Field2 Chuck 12,89 Mike 23,09 Jean 9,34 Chuck 30,00 Mike 3,80 Chuck 22,00 Mike 7,23 Jean 10,55 Jean 10,75 Jean 31,45 Chuck 19,99 Result Field1 SumOfField2 Chuck 84,88 Jean 62,09 Mike 34,12 Advice would be appriciated. Cheers, Ludovic Hi You could use a formula like this ...

DPM forces a Consistency Check on data
Hi all, I have a DPM 2007 SP1 server in one office ? and are trying to backup data in another office, connected by T1 (1.54Mbps) link. It works, but every time there is an issue ? DPM forces a Consistency Check on XGB( >100) of data, which takes 5-7 days to complete. can any tell abt this. thanks in advance gopal Submitted via EggHeadCafe - Software Developer Portal of Choice Useful ASP.NET Exception Engine http://www.eggheadcafe.com/tutorials/aspnet/52012346-36db-4a47-ac61-8b9c9b4ce9a0/useful-aspnet-exception.aspx Hi Gopal, Can you please eloborate on "Ev...

How to get XML data out of an XML file
I am trying to retrieve the Parameters first or second (0, 1 ,2) node from the following XML file: <?xml version="1.0" encoding="utf-8" ?> <Robot xmlns="http://tempuri.org/RobotDefaults.xsd"> <Parameters> <Name>Decker</Name> </Parameters> <Parameters> <Name>A</Name> </Parameters> <Parameters> <Name>B</Name> </Parameters> </Robot> There will be more data than just a name for each Parameters node. Here is my code: Dim node As XmlNode = xmlDocument.SelectSingleNode("/P...

Smart List problem
I recently re-installed a client (version 9.0). Everything went fine but when we tried to open Smartlist on this client we got an error message telling us that “To use Smartlist capability in this product the Smartlist tables must be built. This is one time initialization process and may take a few minutes”. I answered yes, it ran and then returned the following error “number of columns does not match table definitions”. Every other machine is working fine, is the tables it is talking about building on the client? It does not seem like that should be so. It does not matter ...

CRM 4.0 can't import "import into marketing list"
Hi, Please help In CRM 3.0, when you import leads you can select a marketing list (or create a new one) to import to. But I can not find the same thing in CRM 4.0. How can I import leads and put them under a marketing list? Thanks ...

Cloning a price list
Is there any way either through the standard user interface or through the CRM SDK to clone an entire price list? TIA Not an option under standard UI Need to use SDK. Good luck. Frank Lee Workopia, Inc. http://www.workopia.com/Links.htm San Francisco, CA "JB" wrote: > Is there any way either through the standard user interface or through > the CRM SDK to clone an entire price list? > > TIA > > ...

Erase data, preserve formula's
I have a an excel file with 12 worksheets for the financial year and an additional worksheet for yearly totals. I need to get a blank copy of this and was wondering if anyone knew a way to delete all the user inputted data while keeping the formatting and formula's intact. Any help is much appreciated. -- urbanfox ------------------------------------------------------------------------ urbanfox's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=22826 View this thread: http://www.excelforum.com/showthread.php?threadid=519004 Hi Hit F5 and select Special a...

custom address list for EXTERNAL email addresses
I know it is possible to create a custom address list in Exchange 2003 for internal use but how about for external email addresses outside of a company? Is this possible?? Any help would be appreciated. Thanks in advance. Create contacts for each of the external mail addresses and add them to the distribution list. If add a valid internet email address to the distribution list external people can also send to it. Glen "Steven Peters" <Steven Peters@discussions.microsoft.com> wrote in message news:CBF2F2AE-ABF4-491C-836C-59611375F9F1@microsoft.com... > I know it is pos...

Comparing first and last names in two lists
I have two data sources that each contain about 8000 names in seperate fields for first and last name. I can put them on seperate worksheets or append one to the other. I need to make them match and find out where they don't. Messed with consolidate but not sure if that will work for more than one column at a time. FYI data set one is from a school transportation database and data set two is from the main student database from the same school district. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages dir...

Locking cell color while allowing data changes in cell
In excel 2000, I created an attendance worksheet for my classes.(Alphabetized names down left vertical column. Dates across top of horizontal row.) I added a different color to all cells in every other row to make for easier reading of each student's name and absences. Every other row stays with a white background. My question: I wondered if it was possible to lock row colors while allowing data to change on top of them. If a new student is added to my class in alphabetical order, the alternating color pattern is often lost. It is a pain to rechange row and cell colors. Any shortcut ...

How refresh imported data automatically?
In Excel, I've imported data into a worksheet. I know I can click Data.. Refresh Data to requery the source, but I want to be able to do it in a more automated way. Is there some sort of macro I can write when opened or something? Thanks, Ron Ron, Select the cell the data starts in, select Data/Import External Data/Data Range Properties Check the box "Refresh on file open" and anything else that needs checking off... Other than that, you could record a new Macro that selects your cell, refreshes data, and assign the macro to a keystroke, or even an icon. Beege ...

List control selction and deselection
For selecting an item from a CListCtrl just call mList.SetItemState(iItem,LVIS_SELECTED | LVIS_FOCUSED, 0xFF) during Initialization For deslecting we need to insert item once again, ie mList.DeleteAllItems() mList.InsertItem() i know this is not a good method, but i didnt get any other options. if anyone know, pls let me know!!! Regards "johnWins" <johnsvakel@gmail.com> wrote in message news:2ccf2c5c-eb5c-46fd-9bd5-30d89d2048f6@v67g2000hse.googlegroups.com... > For selecting an item from a CListCtrl just call > > mList.SetItemState(iItem,LVIS_SELECTED | LV...

Category to Distribution List
Dear All: A: Outlook 2003 B: OS = XP Is there a way to create a Distribution List from a category ? I have a number of clients in special categories ( usually company name) However, there are times when I want to send BCC to several clients and would like to find a way to send that email without having to insert all the names. The category list of names can only be in the To: portion of the outgoing email. Suggestions highly appreciated. John A Hansen Two methods, both starting with the folder grouped by category, and all = the items in the category selected:=20 1) Choose Actions |...

Adding additional rows for data entry
I have a spreadsheet with five columns that I enter data to. I then have a blank row at the bottom of these columns. Below the blank row I have several formulas pertaining to each row. How do I add more data to the columns and have the formulas adjust for these new rows without highlighting rows and using the insert rows command to make room (empty rows) where I can then add the additional data to the columns. Is there a formula that would always leave one empty row even when new data is entered in the columns? Thanks Put the formulas at the top of the columns. You can even use a Freeze ...

Stop use of Distribution List
Is there anyway that you can stop, or restrict the use of a distribution list? Since you have so much detail of how you are setup and if there are specific people or groups you want to restrict from using it, I will answer your question with the same amount of detail....YES. Add more detail and we can give you a better answer. "brendanrtw" <brendanrtw@discussions.microsoft.com> wrote in message news:6CB3EB2F-F428-46AD-A41E-A1495B8CF4CD@microsoft.com... > Is there anyway that you can stop, or restrict the use of a distribution > list? On Thu, 3 Nov 2005 12:00:...

Question About Missing Data
So, this is probably really easy, but I just want to ask and see if I may be missing something here. Some data on vendor numbers changed. Let’s say IBM used to have a vendor number 12345 and now it’s vendor number is 56789. I can identify IBM as IBM, but I really want to use the number, not the name. Should I set up a table that ties the numbers together, so that Access knows 12345 = 56789? Or, should I do some kind of Update Query and change all incidences of 12345 to be 56789? Or, is there some other, method, like a ‘best practices for missing data’? Thanks! Ryan--- ...

Outlook Data Files #4
I've done some reading but I'm still confused about the use of Office Outlook Personal Folders File (.pst) versus Outlook 97-2002 Personal Folders File (.pst). I understand the basic "Office Outlook Personal Folders File (.pst) to create a new Outlook 2003 .pst file. Click Outlook 97-2002 Personal Folders File (.pst) to create a new Outlook .pst file that is compatible with earlier versions of Outlook." When we have upgraded to Outlook 2003 or set up a new PC with Outlook 2003, when adding PST's you can chosse either of the two file type options and browse to chos...

Pivot table and organizing data
This one is really making me scratch my head. Here is the story. I have a list of information which I am pulling in via a query from SQL. Data is good and it correctly comes into Excel (03 or 07). I have five columns with data: Date, Time, AccountID , Status. First two are self-explanatory; third is a 3-letter ID, forth is a status (pass/fail). Ok, now that you have an idea, here is what I need: 1. List the account IDs as a column 2. List the dates as rows 3. Place the alert into the location that corresponds to the appropriate data and account I know this 'sounds' like a strai...

Copy data from one record to a new record on a sub form
I have a form showing patient details with a sub form displaying all associated referral details for the patient. One patient can have more than one referral and I would like to be able to copy data from some of the fields on the current referral when adding a new referral for the same patient. Is this possible? Thank you for your help I am fairly new to Access code and I am having some problem following your suggestion could you please simplify. Also some of the fields on the referral records are selected via a combo box will these fields still copy? "Klatuu" wrote: > ...

Prevent Hidden Column data from being copied/pasted?
A student came up with a question that I haven't been able to figure out yet in a recent Excel class. They are hiding a column and protecting the worksheet in the correct manner. They want to allow some users to access and enter information in some cells. They do not want the users to be able to copy and paste the information from the hidden column. The question is how can this be prevented? For example, Column B is hidden. When they copy a range such as A1:C10 and paste it to another worksheet, they are getting the "hidden" data in Column B in B1:B10. Any suggestions wou...

New Named Range Created Each Time Data Imported into Excel via Macro
I have noticed that each time I import data into an Excel spreadsheet via a macro, a new named range (for the same range) is created. This does not pose a problem, but after a while, I'll have a huge number of named ranges that will never be used. Why does Excel name the range and how can I stop this? I noticed in the recorded macro, there was a line .Name = "drd_5". I commented this out to see what would happen, but it just renamed the range "ExternalData_5". The next one was "ExternalData_6", etc. Thanks, Carroll Rinehart You could refresh with differen...

Making words in a list a value!!
I am trying to create a list that will generate a value in another column once that word or phrase is chosen. Is this possible and how. Exp. In the drop down list I would chose Product, then in the price column the price automatically appears. brco1, Much better than I could ever explain the process. Here's exactly wha you need. http://www.contextures.com/xlFunctions02.html HT -- Case ----------------------------------------------------------------------- Casey's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=454 View this thread: http://www.excelforu...

selecting multiple choices from a drop down list
I have a drop down list in a cell and I want to be able to selec multiple entries from that list. Does anyone know how I go this -- Message posted from http://www.ExcelForum.com If this is a data validation list, you could use code to compile a list of selected items. For example, if the data validation is in column C, the following code will store the selected items in the same row in column D: '============================= Private Sub Worksheet_Change(ByVal Target As Range) Dim rngDV As Range Application.EnableEvents = False If Target.Count > 1 Then Exit Sub On Error Resume N...

List folders to file
Hi Does anyone have a macro that would list all the folders (with path) and subfolders to an excel sheet but not the files? Thanks Shona Shona- There's a function called CELL() that should return your filename (or any of a number of parameters) but I can't get it to work properly on my machine, so I can't make it work for you. The syntax is =CELL("filename") which ought to return the path and filename of the current file, and which you could then trim down to show just the path. Can anyone provide some guidance here? Dave, If it isn't working for you, it pro...

Combo
Hi, I am able to create a list of items with a drop down menu. I can select specific item but how do I select all item to get a query output. Your help would be much appreciated. Thanks You can't select multiple items from an Access combo box. To do that would require a listbox with it's MULTI-SELECT property set to SIMPLE or EXTENDED. See more info about list boxes here: http://www.fontstuff.com/access/acctut11.htm -- Bob Larson Access World Forums Super Moderator Utter Access VIP Tutorials at http://www.btabdevelopment.com __________________________________ If my post ...