Hi, I'm sure this will be an easy one for most of you. I need to extract data from an array for each time I have recorded a specific vehicle registration. I have used VLOOKUP but this only gives me one result. I need excel to display every enrty for the registration I am looking for. I have attached a sample file which should make more sence.
View 7 Repliesi put in a vlookup formula and it doesn't display any results, it just diplays the formula with the = sign in the cell. it does that with any formula i enter. i tried hitting it and everything.
View 4 Replies View RelatedI wanted to create a multiple drop down lists (using data validation) in column B (50 in all, every 3rd line) whereby, multiple, comma deliminated, results would display in each of the cells - for use elsewhere in the spreadsheet.
I found some code (as follows) which worked perfectly for me
VB:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDV As Range
Dim oldVal As String
[Code].....
As I mentioned above, I don't really understand the code and all I know is that the line about halfway down "If Target.Column = 2 Then" is defining which column (B) this will work in.
The problem I am having is that I need to protect the worksheet and the moment I "protect" the worksheet, the functionality of displaying multiple values goes away and the drop-down list reverts to only displaying one of the available values.
I have a large spreadsheet of data, with lab results by date. Sometimes, there are multiple data points for a single date.
I need to produce monthly reports that list all the results for specific tests in a given month.
So, for example:
Date SG Property Tank
1/5/12 1.015 567324 2044
3/15/12 1.002 568210 2103
3/18/12 1.025 568056 2044
3/18/12 1.036 565200 2102
4/1/12 1.019 566713 2103
I would like to make a report for March that looks like this:
Date SG Tank
3/15/12 1.002 2103
3/18/12 1.025 2044
3/18/12 1.036 2102
And the next month, make a report for April, then May... etc.
I tried to think of a way to do this using vlookup or index/match, but couldn't figure it out.I also tried using filters and then automating some kind of copy/paste, but there is an extra line between the heading and the data, so that the date column is filtered as text instead of date. The source data is not my spreadsheet, so that would be difficult to change.
I would like to do a lookup function, in which multiple results are returned.
i.e.
VLookup Result 1
Result 2
Result 3
Etc.
I know how to do it if there is only one answer but say I have a unique identifiers in which I want all the results in column B:B displayed one after another, say locations have numerous products sold/manufactured at it.
I am looking for Vlookup function, which returns multiple values without duplicates.
Please find the attachment : Vlookup Unique.xlsx‎
I am using the VLOOKUP function to pull multiple columns of information from another sheet in the same workbook based on a name. The sheet it is pulling from is a query. I am looking up product names, and in the query there may be multiple results that the function can find.
VLOOKUP only returns the first result that is found. What I am trying to do is get it to dump all the results into a drop down list in one cell. If I attach a drop down to one of the columns of information I am retrieving, then I can select which of the entrys to display, right? I think this should work I just don't know how to go about setting it up to do it.
How would I set up a VLOOKUP formula that attaches a drop down list containing all the results that the function finds?
I have searched other postings but can’t seem to find the answer.
I was wondering if anyone had a solution on how to string multiple Vlookup results as a text string so that if I Vlookup “Pear” it will return “Red, Blue, Green” in adjacent cell
i.e
A B
1 Fruit Colour
2 Pear Red
3 Orange Purple
4 Apple Green
5 Banana Yellow
6 Pear Blue
7 Pear Green
8 Banana Black
9 Orange Green
10 Apple Black
Is Vlookup the right function to go with here?
What I need is a script or formula with returns multiple results and puts them into one cell.
The Excel is used as a Project Managment Work Sheet for Resources, working on different Projects and their dedicated hours
The raw data table looks like this:
Column A: Project Name (=AllProjectsLists)
Column E: Resource Name (=MasterDataResources)
Column P: values from 0 to 8 (hours)
First I need to check per row the Resource Name. If the Resource Name is a match, than I need to check if in column P the value is bigger than 0. The result goes into a different sheet per row, one result per Resource, but the multiple results should be shown in one cell.
So the result should be something like:
Resource name "Thomas" .... Projects working on: "Project 1, Project 5, Project 13, ..." (in one cell)
Resource name "Mary" .... Projects working on: "Project 3, Project 9, Project 13, ..." (in one cell)
I tried with this one, but it only returns one vale per cell:
=SMALL(IF(E$9=MasterDataResources; ROW(MasterDataResources)-ROW($A$2)+1), ROW(1:1))
=INDEX(AllProjectsLists; SMALL(IF(E$9=MasterDataResources; ROW(MasterDataResources)-ROW($A$2)+1); ROW(1:1)))
So i have a spreadsheet that has a list of members and how many events they have attended. That is fine because i achieved this by doing a countif function on their account number. The spreadsheet has to sheets Events Attended TOTAl and List. In the list it has their name account number and what event they attended and what date. What i need is to have a function that will lookup their account number and return what event they attended but they might have been to 4 different events.
I have a total of 5 columns dedicated to Event attended so we can tell it to lookup first event and return result then have an if function in the next column to lookup event and if its returned in previous column move onto the next event attended.
I'm attempting something new, a dropdown menu using vlookup to find data . If i use the simple example below, using data from B-D on 1 worksheet, where i want to find people by county:
A B C D
Search by County Name County Town
John Sussex Brighton
Eric Yorkshire York
Jim Cheshire Manchester
Liz Sussex Hove
Sarah Avon Bristol
I've created a dropdown menu on a second worksheet,A2, by county search, to return a persons name,county and town. The problem is if i select Sussex i will get 2 names who live in different towns within Sussex. I've created a simple vlookup which works fine, but because of Sussex having 2 results, i created a second vlookup for the second result to appear in the cell underneath.
The problem is that for the results that only have 1 result, the second vlookup returns a the same result as the first result, which i dont want.
Is there a way where multiple results will be shown if they exist, but for one result to appear if only one result exists?
The 3 vlookup will be in a single cell for concatenation like :
=vlookup1&"-"&vlookup2&"-"&vlookup3
Problem the are 3 vlookups where it will return names (with format -> Fname, Given Name Middle) and probably blank returns.
I have problem with the replace since there are also spaces between the Fname,Gname and Mname sample name with spaces.
I am having trouble with some formulas displaying the results of the logical test as words.. For example.
=IF(E4>F4,100, IF(F4>E4, 200))
That works perfectly but I want "100" to be "Valid" and "200" to be "Invalid"
However...
=IF(E4>F4,Valid, IF(F4>E4, Invalid))
only results in the #NAME? error for me?
I have two worksheets in worksheet 1 I have a bunch of data and I want to be able to filter out to only include relevant results.
Eg. If I search for 'Dog' I want search column A in another sheet and find all full and partial matches then display the entire row of each of these matches.
What I am hoping to accomplish is have cells D3 & E3 display the numbers being cycled as it cycles through the loop statement. Current code is below.
Private Sub CommandButton1_Click()
Dim iRnd1 As Integer, iRnd2 As Integer, n As Variant, strJustice
Dim Counter 'tf
[Code].....
For a soccer league table I'm building, I'm trying to add a few columns that display each team's form in the last few matches. So, it will state 'W', 'D' or 'L' for a certain number of recent matches. Like this for each team in the league table: WWDLW.
In the attachment example, I've pretty much got the concept working, having used the invaluable INDEX/MATCH/LARGE functions and a serial date column. The problem is, at the moment the DDW you see at the bottom is taking the whole table into consideration. I'm trying to get it so only Arsenal's recent results are taken into account. I feel like this shouldn't be too difficult, but after numerous attempts of using different functions I cannot get it to work.
Book1.xlsx
I have two groups of lottery results. The first group has the last year results of 200 rows, and the second group displays this year results of 100 rows. I want to start comparing the first row of the second group (green group) with all the rows of the first group( blue group)
View 12 Replies View RelatedI am trying to create a search where the user types into the text box 'ItemDescription' then hits the 'ItemDescSearch' button (see below code) to pollute the list box 'lbSamDesc' with any partial matches from the specified range. Currently when I click on the button it takes about 8 seconds then no results are displayed in the List Box.
View 9 Replies View RelatedI created a user form that provides a menu that allows users to perform advanced filtering. They can also scroll through the results freely. A problem is that a subsequent advanced filter selection does not always display the top row of filtered records, depending upon what the user has chosen to display prior to selecting the next set of filtered records.
How can I make the top row of filtered records always be displayed so that users will not overlook results of advanced filter operations?
using a command button to input data back into the spreadsheet at a specific location.
Background: I am building a time clock spreadsheet, of sorts. I have a user form that provides a list of volunteer names in a combobox and then a list of activities they can perform in another combobox. Some activities have Details. (So, VolunteerName Todd can choose Activity Maintenance, which has no details; Volunteer Joe can choose Activity Teaching and then can select Details Intermediate 2.) The volunteer then clicks a SignInCommandButton.
The SignInCommendButton populates a worksheet (VolunteerLog) with the following information:
Column A: Volunteer Name
Column B: Today's Date
Column H: Exact Time In
Column E: Activity
Column F: Details, if populated
The SignInCommandButton also copies a formula from cell L2 into the appropriate row of column C (Time In) that rounds the Exact Time In to the nearest 15 minutes. The SignInCommandButton then does a Copy/Paste Special Values to remove the formula from column C and then resets the Userform.
What I need to do:
1) When the person first selects their name from VolunteerNameComboBox, I want to check 3 things:
a) whether the person's name exists in Column A of the VolunteerLog worksheet;
b) if it does, if the Date associated with that entry = Today; and
c) if it does, if the Time Out column is Blank.
If all three conditions are met, I want the ActivityComboBox to populate with the values in Column E and F, if necessary. The user will then click the SignOutCommandButton. (details in a moment)
I already have the code for if the conditions aren't met (Activity box populates and, depending on the selection in the Activity box, the Details box may appear for a selection to be made, or may remain hidden.)
2) The SignOut Button needs to enter the Time into Column I of the appropriate row found in (1) above. It will also need to copy the rounding formula I mentioned earlier, and do the Paste Special, but I have that already.
For (1), I think I have a start. My thinking is to first check if the Name selected in VolunteerNameComboBox is in the VolunteerLog. Each time it shows up, I'd like to add it to a list (range?) called rngSignedInDate. Then I'd loop through all the entries in that range checking if the Date = Today. If it does, then I'd add it to a list called rngSignedOut and loop through those results to see if Column C (Time Out) is blank.
I think I have the first loop, but am stuck on how to populate the results into rngSignedInDate. Here is the code:
Code:
Private Sub VolunteerNameComboBox_Change()
'Check if the Volunteer has signed in already
Dim strVol As String
Dim rngSignedInVol As Range
Dim rngSignedInDate As Range
Dim rngSignedOut As Range
Dim rngSignedInActivity As Range
Dim rngSignedInMatch As Range
[code]....
(2) I haven't started working on the SignOutCommandButton. The challenge for me with that is directly tied to the challenge in (1). If someone does pass all 3 tests, I need to have that specific instance defined in a way that I can have the SignOutCommandButton put the time in the appropriate row.
I have a Template which is used to import log data, calculate and displaying the results in several charts. Then all sheets are copied to a new workbook, a Save As dialog apears for the new workbook and the template will close. This is all done with VBA and it works fine.
Problem:
The Worksheets with the Charts on it refer to the data-source at the template file.
Strange because the copied worksheets have the same names as the originals.
(when Opens: This Workbook contains Links to other Workbooks...)
Question:
How can I establish that the Charts will point to their own data sources in the same Workbook rather then linking to the Template file on a Server.
I have attached a work book example of what i am trying to do.
Column D is what i originally did in terms of the formula and now i have to have a column display text dependant on what is in column A to C.
I was trying this formula =IF(b2>a2,"Start Target Missed",IF(c2="","Failed","Tracking")) However if this isnt working.
Is there anything i can do to change this?
Column A & B has a list of Supplier Part numbers and Buyer Part numbers as below.
Supplier P/N
Buyer P/N
HGFYE/12
111111
HYEYDH/14
222222
[Code] ..........
Cell D2 is an open cell that any data can be entered into as a search term. What I am trying to do is search for a Supplier P/N that have the characters "H", "G" or "E" in it, so entering "HGE" into cell D2 would display the results into columns F & G as below.
Supplier P/N
Buyer P/N
HGFYE/12
111111
HDGTEY/56
333333
I can easily do a formula for 1 character or a string of characters.
To complicate it further, if the search term has in this example has "YFF", I would like the same formula/code to workout that the result in F & G should show this time
Supplier P/N
Buyer P/N
YHDHFF/58
555555
I am using Windows 7 and Excel 2007.
I have the attached workbook. I have one sheet (materials) using Vlookup onto a second sheet (price list). If I put in a qty against 2 or more items with the same part code on the price list only the first item is displayed. It does get shown multiple times.
I need to try and get all items displayed, even if the part code is the same.
In my userform I have a list of check box's that can be selected. Currently if more than one is selected, they will appear in the spread sheet in the same line one after another with a space between them. How do I make it so they either appear with a comma appearing after each, so the next value appears on the next line below or most perferably the cell turning to a drop down list with the values?
Current code
VB:
If CheckBox1.Value = True Then ws.Cells(iRow, 2).Value = ws.Cells(iRow, 2).Value & " " & CheckBox1.Caption
If CheckBox2.Value = True Then ws.Cells(iRow, 2).Value = ws.Cells(iRow, 2).Value & " " & CheckBox2.Caption
If CheckBox3.Value = True Then ws.Cells(iRow, 2).Value = ws.Cells(iRow, 2).Value & " " & CheckBox3.Caption
If CheckBox4.Value = True Then ws.Cells(iRow, 2).Value = ws.Cells(iRow, 2).Value & " " & CheckBox4.Caption
If CheckBox5.Value = True Then ws.Cells(iRow, 2).Value = ws.Cells(iRow, 2).Value & " " & CheckBox5.Caption
If CheckBox6.Value = True Then ws.Cells(iRow, 2).Value = ws.Cells(iRow, 2).Value & " " & CheckBox6.Caption
Spreadsheet current cell appearence if all 6 are selected ' Chinnook EH101 Lynx Puma Sea King Fixed Wing'
Required appearence:
Chinnook
EH101
Lynx
Puma
Sea King
Fixed Wing
I have a macro which returns the results for
=IF(ISNA(VLOOKUP("EMPLOYEE",Z:AA,2,0)),0,VLOOKUP("EMPLOYEE",Z:AA,2,0))
As of today, the entry EMPLOYEE is sometimes written as it was EMPLOYEE, and sometimes as EMP. I have to add these two together. How do I change the code of the macro?
Before it was:
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(VLOOKUP(""[EMPLOYEE"",C[-16]:C[-14],2,0)),0,VLOOKUP(""EMPLOYEE"",C[-16]:C[-14],2,0))"
I tried "=IF(ISNA(VLOOKUP(""EMPLOYEE"",C[-16]:C[-14],2,0)),0,VLOOKUP(""EMPLOYEE"",C[-16]:C[-14],2,0))"+
"IF(ISNA(VLOOKUP(""EMP"",C[-16]:C[-14],2,0)),0,VLOOKUP(""EMP"",C[-16]:C[-14],2,0))"
as I would in Excel, but of course it doesn't work.
I have this function trying to added 4 VLOOKUP results together, but it only works for 2 rows and all others show as #NA.
=VLOOKUP(A8,MA!$A$8:$H$10,3,FALSE)+VLOOKUP(A8,PQ!$A$8:$H$10,3,FALSE)+VLOOKUP(A8,ON!$A$8:$H$19,3,FALS E)+VLOOKUP(A8,TW!$A$8:$H$22,3,FALSE)
I wan to create a table using 2 column of data.Below are the data
Type Name
A Danon
B Chris
C Hilton
A Jin
A King
B Ling
C Seng
D Aaron
B Halim
I wan to convert these data to a table which have four dimension. A, B, C, D. With Vlookup function, i can do it. But, i find difficulty when the result is overlap, when in the type A, got Danon, Jin, and King. My result only can lookup is King. Other cannot display.
Worksheet1 has data.
Worksheet2 uses VLOOKUP back to worksheet1 for data to populate cells on worksheet2.
The VLOOKUP command below returns a value of 126 to cell C6. I want to use the number result of the VLOOKUP in C6 (126) as part of a calculation for another cell. In thiscase, cell D6 is keyed in. I want to get the result of =(N(D6)-N(C6)) and put it in cell F6. To further complicate things, if C6 is blank, I want F6 to be blank.
VLOOKUP formula that returns a value of 126 from worksheet1 and puts it in cell C6 on worksheet2. It also returns a blank if there is a blank on worksheet1.
=IF(ISNA(VLOOKUP(A6,Pivot_Table!$A$3:$D$36,3,0))+(VLOOKUP(A6,Pivot_Table!$A$3:$D$36,3,0)=""),"",VLOOKUP(A6,Pivot_Table!$A$3:$D$36,3,FALSE))
Add together D3+G3+J3+M3+Q3+U3 and put the result into W3? I think the reason it wont work is because there will not always be a value in D3/G3 and J3/M3
New KS4 grades Markbook.xlsx