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

Excel 2013 :: Finding Common Value Repeated In Each Column?

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

Build List From Common Cells

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

List Non Common Values Between 2 Columns

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

Finding Common (repeated) Numbers In Columns Of Numbers

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

Find / Count Most Common Phrases In List

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

Find Common Words In Cells And Create New List

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

Find/Count Most Common Words & Phrases In List

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

Evaluating List Of Similar Names And Assigning Common To Variable?

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

Generating A List Of The Five Most Common Responses (Text-Based Mode)

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

Compare Multiple Column Of Data And List Out Common And Unique Component In Adj Columns

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

Excel 2007 :: Make One List Out Of Columns Based On Common Information In Column C?

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

Dependent Dropdown List - 3 Levels - Remove Common Entries From Selection - Show Relevant

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

Finding List Which Has Most Similar Entries To Reference List

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

Compare List Of Values In Order To Find The Common Values

Feb 11, 2013

compare some list of values in order to fiind the common values.

View 1 Replies View Related

Finding Similar Value From One List In Another List?

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

Finding Names Within A List

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

Finding From A List Within Spreadsheet

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

Vlookup Not Finding Value In List

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

Finding The Biggest Value In Custom List?

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

Finding And Inserting Value From Previous List?

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

Finding Closest Date In A List

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

Finding The LAST Time Something Appears In A List?

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

Finding Anagrams In A Word List

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

Finding List Of Keywords In Column?

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

Finding Average Age From List Of Dates?

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

Finding Data From A List And Deleting

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

Finding Keywords On A Large List

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

Finding & Displaying Unique Data Within A List

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

Finding 10 Smallest Unique Values In A List

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







Copyrights 2005-15 www.BigResource.com, All rights reserved