Return Top N List & Return Corresponding Data For N
Oct 4, 2009
I am trying to find the largest value, second largest, and third largest value for a column of numbers then return a content value for the adjacent column that contains the name for which each. The problem I am running into is when the second highest has the same value as the highest and also when the third value is zero (n=meaning there are only non-zero numbers for the column). I am using these formulas currently whic work when the numbers are not the same and I have atleast 3 non-zero numbers but I need to modify in order to account for the problem stated above...
I am trying to create a data entry sheet to enter quotes on. When a quote is received, I click on my "Add quote" button and a userform appears. Data is entered into the userform (frmEntryForm) and returned back to the next available row.
I also need to be able to:
Edit a row by double-clicking it. When a row is double-clicked, data from that row is passed back to the userform, edited and returned back to the same row (to prevent duplicates).Validate that all fields are complete within the userform where relevant (i.e. if the work is not complete or in progress then the "Invoice Number" and "Actual Cost" fields are disabled and blanked to prevent entry (I think this is almost sorted judging by my tests)
I have attached my sheet : 2014 Gatwick Quote Log (Macro Enabled).xlsm
I have a multiple column spreadsheet (Call it- "Money") whereby I need the data identified based on a list of account numbers and return this data to a new sheet.
In "Money" I have:
IE; columns B, C respectively have cust #A100 & 20.00 columns E, F respectively have cust #B100 & 40.00 columns G,H respectively have cust #C100 & 60.00 Etc.
(above for illustration-there are 100 lines of data in these columns with varied account numbers and respective dollars)
So what I have now is a new sheet I have named "Control". I have listed all my account numbers like A100,B100 etc. in column A. These are the account numbers for ident purposes.
I need the data entered in "Money" identified by those columns B,E,G with respective amounts from C,F,H and based on the list I have in "Control" whereby in "Control" if A100 is listed in column A then the figure to be returned in column B is all the data bits found in all columns C,F,H from the entire sheet "Money". Tough to explain but ie below....
I've attached a sample excel for what I'm looking to do. Basically, based off a search box(drop down), I'd like to automatically populate a list of non-blank ingredients in another list.
I have a table which has approx 20 rows and 6 columns. On each row only 1 column will have a value in it, how can i return the value of the cell which is populated?
Need a way to return a list of non-zero without doing sorting the column? For example, I have a list of number running down column A and I want column C to have the number running down starting from the top (in the same order as column A) but leaving out all zeroes. I've attached an example spreadsheet.
IF the first letter (i.e. left most letter) in any of the cells in range F7:F17 is "R", return its content to cell L3 and put a line break between each (i.e. create a list within a single cell).
I've read line breaks can be put in by using &CHAR(10)& but can't get a full formula to work.
The Table Array contains a list of 12 months, and there will be a match to the Lookup Value in A7 each month. I would like C7 to reflect the last completed match in the list (it will be updated each month). So, when June is the last completed month, C7 will return the matching amount from the Table Array for June; then when July is completed, I would like C7 to reflect the matching amount for July.
I finished mty project which shows how to return the value of the first non-blank cell in an array. That's great, but I now need to find the next value, and so on. In the example, I'd need to get "BBB" to come out of the formula.
My list has other blanks inserted throughout the array, so it would more closely resemble:
Input date on left column and the cell on right will automatically look up this date from the list located in other spreadsheet and return multiple corresponding in sequence order? Once another date is entered on left column, it will do the same function again on right column?
I need to return all the unique matches from a set of data.
For example, when a product is selected, I need to list all the possible venues it is sold from. I will use this venue list to populate my drop-down listbox, as opposed to having a 100 venues in the list, I just wish to have the applicable ones.
Example of data set I need to lookup in: prod 1 venue1 prod 2 venue 2 prod 1 venue 3 prod 1 venue 10 prod 2 venue 6
I would like to use a formula if I can (not a macro), to list that prod 1 is only sold in venue 1,3, 10.
It would be great if one could use a vlookup type of formula and it could return a list of each occurence of the look-up'ed cell.....
In Excel 2007, I need to be able to return a value (definition of something) based on what is selected in 2 other cells.
(See Attached) If in cell C2 I select "competency 1" and in Cell C3 I select "Expert", I need to be able to return the definition for someone that is an Expert in Competency 1 in cell D2.
Then, if in cell C5 I select "competency 2" and in Cell C6 I select "Leading", I need to be able to return the definition for someone that is Leading in Competency 2 in cell D5. And I'll be doing this about 10 times on each sheet.
All the tables with info are in another worksheet. I can do a vlookup if I'm just using one thing to match, but how do I match 2 things to return the definition?
I am looking for a formula to extract numbers from a list in descending order. As the list may have more than one entry of the same number the formula i need should only show one of that particular number.
I have a table with the following headers: Customers, Location, bill number, date of bill, number of days you have untill you must pay the bill, the rest are not important
I need to return a list of the bills from the last six months, in which the customer has been granted with days until he must pay the bill(there are some with no granted days).
The table headers are translated, they are not so long.
This will (if I change the row reference at the end) return all Tom C's (If I change B2 to "Tom C") in sequential order but only the Tom C's and not the other Tom's (Tom B and Tom A). I have tried replacing the $B$2 with "*"&$B$2&"*" but that doesn't seem to work.
I am trying to search a range for all of the nonblank cells, when I find one, return the column header and the value in that column. Vlookup won't work on this. It will only return a specified column.
Employee performance
ID area one | two | three
12947 23 | | 19 12948 32 | | 20 12948 |17 | 52
What I am trying to do is create a communication sheet. Employee ID 12947, your performance in area one= ______, in area two = ______ and so on.
I have 2 columns of data in a report, the column on the right J has a text entry that can possibly occur 1 or 2 times in the list. If it occurs twice I want to find the second occurance and return what is in column A.
I'm making a spreadsheet where I want the user to select an item in A1 from a List made using Data Validation. When the person makes the selection in A1 I want the next 5 columns to automatically display specific attributes that belong to A1. However, I'd like the user to then be able to change each of these items (in B1-F1) from their own individual Validation Lists. I'm using this to mix and match mythical monsters. So in A1 I have well known monsters like Jaws, Godzilla and the Lochness Monster. When you select one of these creatures, that creature's specific attributes show up in the next 5 columns -- things like Appearance, Motive, MO, Hideout, and Special Features. I then want to be able to change Appearance, Motive, MO, Hideout, and Special Features individually from their own Validation List. I basically just want to mix and match between the various creatures.
I've gotten it only partially working by doing the following: At the bottom of the page (for simplicity, I have everything in 1 sheet but I intend to move the "data" table for the Lists to their own sheet) I have a table where in the row for each creature I have the different attributes. A1 (my "creature name") was made using Data Validation from the A column. For the next column I made a Data Validation List for the items. Once that pulldown was made, I then went to the B1 cell and used VLOOKUP to autofill the cell when the person makes their "A1" selection. I repeated this for cells C1-F1. This works ok the first time you make a selection. But whenever you change the "default" value for B2 using the validation drop down list, the Vlookup formula is replaced by the actual text value. This results in the cell not changing when the person next changes the A1 value.
I am trying to create an if function that returns a drop down list (which i have already created in another cell) to the cell if a certain condition is met. When i try, the if function only returns the option that has been selected in the drop down lists cell, instead of the entire drop down list. Is there a way to get the If function to return a working drop down box?
I have a file with multiple sales for each product, each month it can change depending on sales and i want to get the total of each product. As the range is not defined this is where i am stuck. I want the total for each product based on the product name or cell reference.
Logic: Look through range and sum total each product equal to "A2" for example.
I have a list of students that have sat a number of exams on different dates. Can I pick out from the list the last exam for each person. I can sort by id number and then by date but how do I then show the record showing the latest date for each person?
Example Mary 1/2/2012 john 2/3/2012 john 1/3/2012 mary 1/1/2012 mary 4/4/ 2012
I want to select the record showing the latest date for each person. the result would be mary 4/4/2012 John 2/3/2012
I have created a drop down list of various material sizes what I am needing to do is to select a material size from the drop down list and a corresponding value is input into the cell. So on say sheet two (Data Sheet) I have Cell A1 25x25 with Cell B1 100, Cell A2 30x30 with Cell B2 120 and so on. On sheet 1 Cell A1 has my drop down list being the material size ie 25x25, so what I need to happen is if I select 25x25 then Cell B1 is 100 or if I select 30x30 then cell B1 is 120.
