Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding most common word in a list
Hi,
I have data organized in the following way: Thousands of rows, each row has from 10-100 columns. In each cell is text, either one or two words. Within each row, is there an easy way to find which cells are repeated the most? So for example: A1 = "America" B1 = "Ecuador" C1 = "Spain" D1 = "America" For row 1, I would want the macro to return "America". Note that each row is totally independent of each other row. Also, I need to run this on literally 250,000 rows with an avg of 50 entries per row...so I have to make the code as efficient as possible. Please help!!! Thanks, Phil |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding most common word in a list
in an empty cell type
=COUNTIF(1:1,"America") try this and see whether you get what you want. explanation is to find out number of times "america" comes in row 1. you can do a loop in vba for each of the rows with the variable text to find in different rows. by the by can you get 250,000 rows in a sheet? I though maximum number of rows is 65,536. General wrote in message ups.com... Hi, I have data organized in the following way: Thousands of rows, each row has from 10-100 columns. In each cell is text, either one or two words. Within each row, is there an easy way to find which cells are repeated the most? So for example: A1 = "America" B1 = "Ecuador" C1 = "Spain" D1 = "America" For row 1, I would want the macro to return "America". Note that each row is totally independent of each other row. Also, I need to run this on literally 250,000 rows with an avg of 50 entries per row...so I have to make the code as efficient as possible. Please help!!! Thanks, Phil |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding most common word in a list
Thanks for the suggestion, but I was hoping for a more elegant way to
do it. This method would require a "countif" for each cell and then additional processing after that. Any simpler ideas? Also, you are correct that it's 65,536 rows max...my data spans 4 sheets. You can understand my need for simplicty.... Thanks, Phil |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding most common word in a list
Thanks for your help thus far, but let me clear up my problem. Data
will be of the following form: A1 = "America" B1 = "Ecuador" C1 = "Spain" D1 = "America" A2 = "Gerbil" B2 = "Hamster" C3 = "Goat" D3 = "Gerbil" My point is that each row will have different words, and I do not know what the words will be in advance. So the program, for each row should find whatever word appears the most. It has to do this automatically for about 250,000 rows so this needs to be fast and not require any user inputs. Thanks in advance, Phil |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding most common word in a list
Actually, my problem can be simplified as follows:
Given a random amount of words (Anywhere from 1 to 50), find the word that appears the most. You don't know what the words will be ahead of time and it has to be quick and automated and involved no user interaction. Thanks, Phil |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding most common word in a list
I have started on your problem and have come to a stage.
please clairfy to me. do you mean you want ONLY the name which has maximum occurences in a partiuclar sheet or in all the four sheets. . (only one name in each sheet or only one name taken all thes sheets together) and natually the no. of occasions. General wrote in message oups.com... Actually, my problem can be simplified as follows: Given a random amount of words (Anywhere from 1 to 50), find the word that appears the most. You don't know what the words will be ahead of time and it has to be quick and automated and involved no user interaction. Thanks, Phil |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding most common word in a list-attention general
your requirment appears, to a non expert like me, highly customised. I have
practically COMPLETED the task. you reqire the the name which occurs maximum no. of occasions in each of the sheets. let me see whether I understood your requiremnts correctly you have names in 65536 rows in each sheet(four sheets) you dont know what the names are you need the particular name which occurs maximum no. of times It may also be that more than one name may occur same maximum number of times. this has been taken intoaccount in my sub. all your rows(65536) are filled. 1. But do you have any blank columns beyond the data. will you please tell me the the column name where the blank columns start. I need these columns to park some data temporarily and programatically. i have done it for the only one sheet (sheet1) for the present this can be easily extended to other sheets also. this can be done only after I get confirmation from you that the programme gives what your want. 2. what is your data base address e.g. A1 to BB65536. As i dont know your database I have designed a small database with few rows and columns of names (they are merely alphabets randomly selectd). Of course it would be convenient if we exchange messages through email to our perosnal email addresses. this is a home computer and not any organisational or business computer. I am NOT a professional but purely doing work on excel for my brother and friends and purely to keep myself cerebrally active.. however if you do not feel comfortable with email messages then we shall continue our messages through the newsgroup regards my eamil address General wrote in message oups.com... Actually, my problem can be simplified as follows: Given a random amount of words (Anywhere from 1 to 50), find the word that appears the most. You don't know what the words will be ahead of time and it has to be quick and automated and involved no user interaction. Thanks, Phil |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding most common word in a list-attention general
Hi, I would be happy to correspond via email, but Google cut out a
portion of your email address. my email is pakman36 and the provider is aol.com. To answer your question... I have 4 sheets filled with data. Each sheet has entries in every row from 1 to 65536. Each row has a varying number of entries (from 1 to maybe 50). For each ROW I need to find the most common word that appears in that ROW. So approximately 260,000 times I need to scan a row and find out the one word which appears the most. Some rows will have no duplicates, and some rows will be all the same word. Howver most rows will have a variety of different words. Thanks in advance for your help. I hope this clears up the problem, but if it doesn't, then please let me know. Thanks, Phil |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding most common word in a list-attention general
thank you. this needs a change to my programme. i shall try my best. give
me couple of day. I thinkit can be solved. there is two more email addresses of mine. you need the result i.e the name (most occurences) in EACH ROW. That means you need 260,000 results. am I right. you also must be needing the name and also how many occurences or do you not want this inforamtion. kind regarads. General wrote in message oups.com... Hi, I would be happy to correspond via email, but Google cut out a portion of your email address. my email is pakman36 and the provider is aol.com. To answer your question... I have 4 sheets filled with data. Each sheet has entries in every row from 1 to 65536. Each row has a varying number of entries (from 1 to maybe 50). For each ROW I need to find the most common word that appears in that ROW. So approximately 260,000 times I need to scan a row and find out the one word which appears the most. Some rows will have no duplicates, and some rows will be all the same word. Howver most rows will have a variety of different words. Thanks in advance for your help. I hope this clears up the problem, but if it doesn't, then please let me know. Thanks, Phil |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
finding common denominator | Excel Discussion (Misc queries) | |||
finding common numbers | Excel Discussion (Misc queries) | |||
need help finding most common number and so on | Excel Discussion (Misc queries) | |||
finding common data | Excel Discussion (Misc queries) | |||
finding the common names between columns | Excel Discussion (Misc queries) |