Finding Top 3 Most Common From A List?
Apr 14, 2013
I am currently trying to organise a student yearbook for my pupils at school. We have given out sheets to students in order for them to nominate their peers for various awards such as 'Funniest Student', 'Most Caring Student' etc.
I am trying to collate all of the data into an excel workbook. I have made two sheets within a workbook, which I have formatted how I want them to look like. I want to insert all the student's responses into one sheet, and somehow, the top 3 most occurring nominations are found and are inserted into the appropriate cells in the separate sheet.
I have attached a copy of the workbook. Due to data protection the student names and tutor groups have been taken out and replaced with the word "REMOVED", yet I will paste them back in at a later time. So feel free to have a play around to see what I am talking about! I have filled the table with some dummy data just so you can see what I mean.
I am not at all sure how I would go about picking out the top 3 most occurring names for each award, but once I can do that, I will be able to insert them into the other sheet. How to automatically produce the top 3 most occurring names.
View 1 Replies
ADVERTISEMENT
Dec 21, 2013
I have an Excel file in which I want to find the numbers which are repeated in several columns (B to L), but there are too many rows to find them looking one by one cell, is there a function which will allow me to find the common values which appear in these columns?
eg.:
A B C
1 3 4
2 1 7
2 4 7
4 1 1
results: 1 & 4
View 6 Replies
View Related
May 17, 2007
Got something I need to do that I have never done.
I have a column on a worksheet1:
NBC//1
ABC//1
ABC//2
ABC//3
ABC//4
CBS//1
CBS//2
I have a column on another worksheet2:
NBC
ABC
CBS
In the column next to the above on worksheet2, i want to pull the numbers in for that (the numbers are contained in ONE column):
NBC | 1
ABC | 1 2 3 4
CBS | 1 2
Im not sure how to go about this?
In the example I attached, on sheet 2 column B, that is where I want the numbers to build to.
View 9 Replies
View Related
Sep 25, 2007
I have 2 columns of data looks like this:
col-a
shark
whale
dolphin
seal
fish
col-b
dog
cat
fish
deer
moose
What I'm looking for is a formula to compare col-a to col-b and create col-c to look like
col-c
dog
cat
deer
moose
View 9 Replies
View Related
Mar 21, 2008
I work for a charity and I have to cancel the donations of people whose credit card donations have been declined in three consecutive months.
If in Column A I have a list of donor IDs whose credit cards were declined in Jan 2008, in Column B I have a list of donor IDs whose credit cards were declined in Feb 2008 and in Column C I have a list of donor IDs whose credit cards were declined in Mar 2008, is there a way of showing in a fourth column which donor IDs were common (repeated) in Columns A, B and C? I would have a title for each column in A1, B1 and C1, and also the column where the repeated donor IDs would be displayed.
View 9 Replies
View Related
Jul 25, 2012
I've found a vb script via Google that displays the most common words in a list followed by a number.
Code:
Sub HTH() Dim vArray As Variant Dim lLoop As Long
Dim rCell As Range With CreateObject("Scripting.Dictionary")
For Each rCell In Range("A1", Cells(Rows.Count, "A").End(xlUp))
[Code] .......
At the moment this results in 1 word followed by a count.
How do I alter this so it shows 2 or 3 words, ultimately showing the most common phrases?
View 1 Replies
View Related
May 29, 2005
I have a spreadsheet of part #'s, descriptions, manufacturer names, and manufacturer part #'s. (It's a list of the inventory in my warehouse). Each row contains information for just the item in that row. Row 2 references another part in my warehouse, row 3 yet another, and so on.
Many of the parts have more than one potential manufacturer and part #, (meaning that any of those manufacturer's part #'s are basically the same tool; just different brands. At one time we may get a shipment of one, at other times we may get a shipment of another). For example, a screwdriver may be listed like this:
Part # 1234 screwdriver, mfg Snap-On, part # 456, mfg Stanley, part # 789, mfg Mac Tool, part # 439.
Then further down the list, there may be another part listed like this:
Part # 9980 wrench, mfg Stanley, part #741, mfg Snap-On, part # 852, mfg Proto, part # 369.
If you can imagine that data across the cells of a spreadsheet row, notice how the mfg name 'Snap-On' was the first mfg name on the screwdriver, but it was listed as the 2nd mfg name on the wrench.
So, here's my question: I want to be able to group all of the items made by any one manufacturer together in a new list. If all of the manufacturer names were in the same column, I could simply sort the list by that column, but since I've got thousands of rows with the mfg name I'm looking for in different columns on different rows, I thought maybe a macro could search each row for the word I'm looking for, then if found, take the whole row and copy it to a new worksheet. So the end result would be, If I wanted to see all items of which Snap-On is an acceptable supplier, I could get a list of all potential Snap-On items grouped together.
I'm sorry this is so long. I may have over-worded this and it may not be too clear. I could email an example of the spreadsheet if anyone needed more info to figure out what I'm looking for and was willing to take a look at it.
View 9 Replies
View Related
Jun 25, 2008
I am attempting to take a very large list of keywords, and find the most common words and phrases within them. For example, if I had a list that said:
excel formulas
excel spreadsheet formulas
excel help
excel formulas help form
formulas for excel
I would like to come away knowing that "excel" and "formulas" are common words within the list.
Currently, I believe this can be accomplished by doing the following:
1. Break down each line into all of its possible combinations.This would mean that the line with "excel spreadsheet formulas" would return:
excel spreadsheet formulas
excel spreadsheet
spreadsheet formulas
excel
spreadsheet
formulas
2. Once the entire list is broken down into its many parts, use the pivot table feature of excel to determine how common each of the parts is within the entire data set.
So, my questions are these:
1. Do you believe this is the best way to solve my problem? If not, what would be the preferred method?
2. If this is the best method, what function or script would I use to accomplish the first step of breaking down the lines into their individual parts?
Mike Auto Merged Post Until 24 Hrs Passes;It appears I put too many characters in the title of my post. It should read: Common Words - Decomposing Text Phrases
View 3 Replies
View Related
Sep 17, 2013
I am working on a customer report template that generates our customers reports and will send them out automatically.
This issue I have now is that the system that generates the raw data for these reports only lists the Customers name in a column with an entry for each line of data, the thing is though that the system has lots of variations of the customers name, even more so if that customers has different departments.
What I need to do is from this list of customer names, I need to automatically figure out what the "common" name is or main name so to speak, and then make a variable using the correct full name, which will be used later on in the code to import correct logos, and direct the reports to the correct people.
Here is a quick example of what data we get raw from the system:
Customer Name:
John Build
Johns Buildings
Johns Ltd Building
Johns Plumbing Department
Glass Doors Ltd A Department of Johns Buildings
Johns Building Corporation
Hole In One Golf Range
This is just an example, we have thousands of clients, so the length, number of words etc can change alot. Ideally I from a list similar to that I would get a full proper result of "Johns Buildings Ltd" for example, this would then be in a variable to be used in code from then on to reference doing certain things with the reports of Johns Buildings Ltd.
You'll notice there is one name "Hole In One Golf Range" that seems to have no relation at all, this is correct, ideally I would also like to build in some error checking into the code, so that rows like that that have nothing to do with the others would get deleted.
So how would you amazing VBA gurus go about working with data like this? I'd prefer a more general answer with explainations that just straight code, as I'm sure I will have to adapt the hell out of it for it to be useful in context.
View 9 Replies
View Related
Sep 11, 2009
I would like to generate a dynamic list of the top five most common text responses in a range. For simplicity sake, let's assume that the range spans cells A1 to A20, and the list is generated in cells B1 to B5. So, essentially, I am seeking five separate formulas. One to calculate the most common text element, one to calculate the second most common text element, one to calculate the third most common text element and so forth.
I am currently working with the two formulas below but finding it incredibly difficult to merge them into a working format.
Formula for finding second most common number:
=MODE(IF(IF(A1:A20"",A1:A20)MODE(IF(A1:A20"",A1:A20)
),IF(A1:A20"",A1:A20),""))
Formula for finding most common text element:
=INDEX(A1:A20,MODE(IF(ISTEXT(A1:A20
),MATCH(A1:A20,A1:A20,0))))
View 9 Replies
View Related
Jan 23, 2006
I am trying to compare multiple column in a worksheet to find
common component in all the columns and what is unique to a particular
column only. And list the results/finding in adj column. What i am
trying to accomplish is something as below.
BEFORE
Sheet1 Sheet2 Sheet3
Column2 Column2 Column2
02-1234-12 07-1234-12 02-1234-12
04-1234-12 03-1234-12 02-1234-12
05-1234-12 02-1234-12 06-1234-34
AFTER
Common to all Unique to sheet1 Unique to Sheet2
02-1234-12 05-1234-12 07-1234-12
View 14 Replies
View Related
Oct 12, 2011
Is it possible to take the information below and make one list out of columns F, H and J based on the common information in column C? For example, group all the 18s in a list, followed by the 44s.this is excel 2007
column CColumn Fcolumn Hcolumn J
Option code
18180L12369301/123/54
18180L12369301/123/54
18180L12369301/123/54[code].....
View 2 Replies
View Related
Dec 4, 2013
I have scoured the net for the following and have not been able to find a solution. I have found variations but not something specific for the use needed here.
I have attached a file with sample data which has the following format.
Column 1 = vehicle model (in this example golf, jetta, but there will be over 100 choices)
Column 2 = vehicle package option for specified vehicle model (automatic transmission, manual transmission, automatic transmission with air conditioning, etc...)
Column 3 = vehicle colour available for vehicle package option
Please note that Column 2 values for 'golf' are different than values available for 'jetta' (in the sample data I have blocked out common values with the same colour for quick and easy identification)
Also please note that Column 3 values of colour options vary for each 'vehicle package option'.
The behaviour that would be ideal is to have 3 drop down menus. The first drop down menu will allow selection from column 1 and will show each model only one time and repeated values will not be shown. The second drop down menu will only show the options available based on the selection in the first drop down menu. Likewise, the third drop down menu will show the colour options available based on the selection in the second drop down menu.
Since my data will involve hundreds of unique values in column 1 with plenty more added over time, it seems that the format of the data and the way it is laid out in the attached sample is the easiest way to organize it. It is perhaps also the easiest way to include new data without a lot of reprogramming.
Sample Data - Dependent Drop Down Cells Question.xlsx
View 8 Replies
View Related
Apr 26, 2014
I have two lists of product data, one for buyers and one for sellers (these are listed as A, B, C). The product names are not exactly the same (Eg Playstation and playstation three should be matched), I would have thought using the FuzzyLogic add in to match these would be the way forward! I need to rank the sellers by how many of their items appear on the buyers list
View 8 Replies
View Related
Feb 11, 2013
compare some list of values in order to fiind the common values.
View 1 Replies
View Related
Oct 7, 2011
On Sheet 1 I have a list of employee names (John, Bob, Ross etc...)in column A and in column B I have a list of employee bonus points (1, 5, 3 etc...). On Sheet 2 I have the same setup but the list of employees on sheet 2 is a lot longer than the ones on sheet 1, all employee names are on sheet 2.
I need a macro that will go down to each name on the list on sheet 1, column A, and copy the employee bonus points then go to sheet 2 and find the that employees name in the list and paste the bonus points in column B. This must be done until the last name on sheet 1 is found and all points are copied to their corresponding names on sheet 2.
View 3 Replies
View Related
Sep 14, 2009
I am creating an interactive tool in Excel.
One of the Fields is "Employee Name"
I created a validation list that includes the names "Mary, Joe, Michael, and David"
When I plug in the name "Mary" I want to pick out her qualifications from a list I have created on a separate worksheet.
Passport
Mary
Joe
David
Lunch
Mary
Joe
Michael
Car
Joe
Michael
David
I want Excel to have the ability to find the name "Mary" from those three lists and reply back to me with the headers of either Lunch, Car, and/or Passport based on the lists Mary appears in. I want this to change depending on the Employee Name and I pick and the lists they appear in.
View 14 Replies
View Related
Nov 1, 2012
I have a spreadsheet with the first 17 tabs containing data and the last tab the 18th one contains a list in col a of 4,000 unique code items.
Is it possible to check if any of the codes items exist in any cell of the first seventeen tabs.
View 1 Replies
View Related
Feb 26, 2010
Excel 2002
I have a number (22.3) in a cell that is there from an Hlookup function. I am then using a vlookup to lookup the 22.3 (from the hlookup) in a column. The number is in the column, but the vlookup function cant find it. If I were to type over my hlookup formula so that I am hand-typing in "22.3" instead of it being their from the hlookup, then the vlookup works just fine. I hope that is not too confusing. I dont understand what is going on. I checked to make sure none of my cells are rounding and therefore creating a look-alike number when it really isn't and that is not the case.
View 9 Replies
View Related
Apr 26, 2014
I know how to make customs lists, but it's not getting me anywhere.
Let's say I have a lot of data containing values as follows and in the following list I sorted them from small to large:
5a
5a+
5b
5b+
5c
5c+
6a
6a+
6b
6b+
6c
6c+
7a
7a+
7b
7b+
7c
7c+
8a
and so on... In the list, the date isn't sorted from small to large, many values (if not all) occur multiple times and on occasion some values are added to the column. With that last part I mean that you can see it as a batch of 500, and if so far the batch only contains 450 values there will be (for the time being until items get added) 50 empty cells in the column as well.
You can't order numerically or alphabetically. I know I can make a custom list and then just sort according to that custom list so the biggest value would come out on top or bottom but that's not what I'm looking for either, as the data needs to be sort by another colomn (dates in chronological order).
So I'm just looking for a formule saying "return the biggest value if the order of things is as I typed above'.
View 14 Replies
View Related
May 15, 2014
The basis of my needs is to be able to find in a list of names, a duplicate name from previous list and then grab and populate a number from that row. Here is an example
A B C D
Doe,Jane $1000 $250 $750
Smith,John $700 $100 $600
Wilson,Alan $1500 $1500 $0
I update the list, some names are added and others taken away. When I add the new list, column C of every name is $0. I want the formula to find names from the previous list and populate Column C with the value of Column C + D of the corresponding name. Also I have a separate formula that simply is D=(B-C) The result of what I would want this to look like is this...
A B C D
Doe,Jane $1200 $1000 $200
Doe,John $500 $0 $500
Smith,John $1000 $700 $300
As you can see there was a new name added to the list (Doe,John) and a name taken away from the list (Wilson,Alan). Column C is the only Column that I am wanting to auto populate. The Value in Column B is a variable that is managed by another program so you don't have to worry about that.
Keep in mind there will be multiple instances of this list and the same name will occur more than once in the spreadsheet. There are not duplicate names that are different customers though as my other program takes care of that. eg. "Doe,John" and "Doe,John #2". I only want it to grab the information from the previous list not ones previous to that list(if that makes sense) third example...
A B C D
Doe,Jane $1500 $1200 $300
Doe,John $500 $500 $0
Miller,Joe $700 $0 $700
Smith, John $1300 $1000 $300
My current method of accomplishing this is to select the previous list and print it. I then go down the new list and cross reference it with the old one keying in the values by hand. This would be a huge time saver as the list is 250 names and growing. I hope I have given enough information but undoubtedly I have not. So feel free to ask me any questions and for more details. I did not want to go to in depth for fear of my post being to long.
View 14 Replies
View Related
Apr 9, 2008
I've been turning this problem over it my head for the last day and can't seem to come up with a decent solution, so I figured I'd post it here to see if anybody else had any bright ideas.
I have a data sheet with a dropdown list on it to select the month and year that determines how the rest of the data sheet populates. Basically I'm giving weekly breakdowns of data for a month at a time, with the "Week Ending" heading (which are dates - Saturdays), populating off of the month and year selected from the dropdown.
So, to give an example, if somebody selects March 2008 in the dropdown, it will populate the week ending dates of 3/1, 3/8, 3/15, 3/22, 3/29, and then pull out the specific data for each week based on those dates.
What I'd like to do is write a formula that will find the first Saturday of the month. The way I have it jury-rigged right now is a list of months with the first Saturday listed next to it and a vlookup to grab the date of the first Saturday. I envision using this report indefinitely into the future and I'd like to avoid having to keep adding/changing the manual list of Saturdays, instead preferring to have a way to determine the date.
View 9 Replies
View Related
Apr 17, 2009
I have a list of numbers in Column A (labeled "Number"), with a list of dates in Column B (labeled "Date & Time"). Every date in Column B is unique, but some numbers in Column A appear more than once.
I am trying to create a simple formula that will automatically search for the LAST time a unique number appears in Column A (ignoring all previous instances of this number), and copy the date right next to it in Column B to another location on another worksheet.
I tried fussing with several of the built-in functions, but I really don't know what I'm doing, and the built-in help documents are filled with too much mathematical/programmer technical terminology for me to understand. All the tutorials I've looked at seem to think I want to add some numbers together, but I don't want to do any calculations!
View 4 Replies
View Related
Jul 28, 2009
In column A we have words, like this:
CHAIR
EXCEPT
WORLD
STREET
OUTSIDE
PEOPLE
WINDOW
LETTER
BOOK
In column B we have another list of words:
NIGHT
DREAM
EXPECT
THOUGHT
TEDIOUS
OFFICE
SETTER
LIBRARY
BEFORE
OFFER
I need a formula in C1 which will check all the words from column B and find the ones which are anagrams of some of the words from column A, and then return those words next to them.
(Anagram is the word made up of another word's letters, without repeating or omitting any, for example anagram of SPARK is PARKS).
In the example above, we would have the words: EXCEPT next to EXPECT (in C3), OUTSIDE next to TEDIOUS (C5), and STREET next to SETTER (C7).
View 10 Replies
View Related
Mar 17, 2014
I need to find 100 or so keywords in a column, I have the list but how would I search multiple keywords as there are 10k columns and it would take forver to search for them manually?
View 4 Replies
View Related
Jul 20, 2013
Finding an average age from a list of dates. This is just a small sample:
7/9/2013
7/10/2013
7/10/2013
7/10/2013
7/11/2013
7/11/2013
7/14/2013
7/14/2013
7/19/2013
7/19/2013
I tried using =AVERAGE(DAY(A1:A10)) CTRL + SHIFT + ENTER, but that just doesn't seem correct.
View 9 Replies
View Related
Jun 6, 2007
I have a large keyword phrase list.
In a sheet called "AllKWs".
What I would like to be able to do is find all Country and town names in that list and delete the names only in the phrase, not the whole row.
Several points though.
Can the macro (As I will assign a text button to it).
Only find and delete these words if they are either at the start or end of a phrase?
4 example phrases; ....
View 9 Replies
View Related
Jan 20, 2009
I need to setup a macro that will search for a keyword in a list with about 21000 rows. When the macro finds the keyword it cuts the row out and moves it to another sheet. I'm having a few problems
1. I don't know how to make the function search until it can't find the keyword anymore, then move on to the next word
2. I can't get it to select what it finds it finds the cell but I don't know how to select that cell once its found.
Sub Search()
'
' Search Macro
'
Dim keyword As String
Dim x, y, z As Long
For x = 1 To 53
Sheets("Test1").Select
Range("A" & x).Select
keyword = Selection.........
View 9 Replies
View Related
Oct 6, 2008
Attached is a sample file that has a Data entry sheet in which the user can paste in data. They can test & paste anywhere from 1 to 50 compounds at a time and run the list through 1 to 55 Assays. I want to generate a sheet (Plate & Assay Info) that would first display the list of compounds that were tested as well as the list of Assays that they were run through.
View 13 Replies
View Related
Oct 2, 2008
I have been using the SMALL function to find 10 smallest values in a list, however, some of the values are the same, is there a way of only having the 10 smallest unique values within a list?
View 2 Replies
View Related
