VLookup To Output Text Data Results?
Dec 24, 2013
I have a column with lots of tube station names. I have a look up table with the tube station name, and the possible lines that pass by that tube station.
One tube station may be covered by more than one tube line.
I am looking for a formula that will output every tube line that caters that one tube station.
first question, what is the best way to organise the data in my look up table.
Second, is a vlookup the best formula to achieve the output I want? i.e. Reflect how many tube lines cover each station
see table below: If the station name has only one tube line, Vlookup works fine. When the station, such as Canno Street, has two tube lines, Vlookup only picks up one tube line. If I duplicate Cannot street under the station name, the Vlookup picks only one tube line.
LOOK UP TABLE A1 - station name
B1 Tube line name
Cannon Street
District
[Code].....
Is there a formula that will output the data of the second tube line name in column D1?
View 3 Replies
ADVERTISEMENT
Oct 21, 2012
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)))
View 5 Replies
View Related
Apr 24, 2009
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?
View 9 Replies
View Related
Oct 9, 2009
I have built a model where I have various inputs and various outputs. I have managed to set up the model so that whenever i hit the 'add to portfolio' button, a new row is added putting in the details from the inputs to a new row (beginning in row 25). The problem I am having is that I cannot seem to get the output results (cells I12:I17) to be included as well. I have attached my excel file.
View 4 Replies
View Related
Jun 11, 2008
I'm trying to get certain data from an export of a phone logging report for a call center. I have exported this data into an excel spreadsheet. I would now like to retrieve the following information from the sheet.
'Agent name, Date, time of Login, time of Logout (at end of day).'
I have coloured these fields in red for easy finding. I do not require anything else from this data, however, within this sheet there are multiple agent names and can span for over a month. All names and dates are required. I have attached the current sheet, and also what it will possibly look like once complete. I would prefer the whole process to be automated if possible.
View 2 Replies
View Related
Feb 16, 2009
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.
View 14 Replies
View Related
Oct 4, 2012
Need macro to search xls files in folder/directory for common text string "see reference" and then output the file number which is located in cell A1 to new spreadsheet for each file the text "see reference" is found.
View 6 Replies
View Related
Feb 6, 2013
I have a worksheet with five columns (A, B, C, D and E)
The cells in Column B contain letters and/or numbers (without spaces) in no particular order.
The cells in Column C contain letters and/or numbers (without spaces) in no particular order.
I want to compare all characters in 1st Cell of Column B with all characters in 1st Cell of Column C, and display the matching characters in 1st Cell of Column D, and the character count of 1st Cell in Column D must be displayed in 1st Cell of Column E. note that multiple instances of the same character must not be treated as duplicates. When execution on 1st Row is finished then repeat procedure for Row 2, etc... Stop execution when first empty cell in Column B is located.
Example:
B1 = LJLM12
C1 = KY2MLK
B2 = ZCG4GM
C2 = X4GGGC
B3 = KTCBNG
C3 = GNBTBB
The script/code/formula must output the following:
D1 = LM2
E1 = 3
D2 = CG4G
E2 = 4
D3 = TBNG
E3 = 4
I am using Excel 2003. Y
View 2 Replies
View Related
May 3, 2007
I know how to do Vlookup and get the value on the +1 column as following
=VLOOKUP(50, D5:E19, 1)
What about I would like it return the value of -1 Column. Can I using some function of Offset with other functions?
View 4 Replies
View Related
Dec 3, 2009
Table 1:
I like to go to Berlin.
You like to move to Amsterdam.
They want to see Chicago.
When will the reach San Francisco.
Table 2:
Amsterdam
Chicago
San Francisco
Berlin
What I want to do:
I want my function to see if the text in Table 1 contains a value in Table 2, and if yes, output that value.
Desired Result:
Row A1:
I like to go to Berlin.
Row A2:
Berlin
Row B1:
You like to move to Amsterdam.
Row B2:
Amsterdam
View 9 Replies
View Related
Jun 27, 2013
I am trying to put all my parts with quantities on a seperate sheet called "Parts List" Every time you select a quanity for one of the parts, I want it to pop up on my parts list. This will make it easier to identify the exact parts I want and also the quantity I need. This will be much more convenient then scrolling down my parts list and trying to find the one's with quantities.
I think I need to use a vlookup or even a Macro but I don't know how to go about doing this.
View 1 Replies
View Related
Aug 20, 2014
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.
View 3 Replies
View Related
Mar 4, 2009
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)
View 11 Replies
View Related
Oct 11, 2006
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.
View 4 Replies
View Related
Nov 15, 2008
I'm trying to have it output certain symbols such as "Ó" and I can't quite figure out how to do it, i want it to basically write in the selected cell:
"Ó of " & a & "'s = " & b
where a and b are strings... my problem is i cant figure out how to have it output the sigma but keep the letters from going into symbol or windings...
View 6 Replies
View Related
Aug 18, 2006
i am currently working on a macro that will make an average number of days. my problem is that when the macro runs it works fine and does what is is supposed to but becuse some fields do not have numbers in it returns the #value! back in the output box. I need some way of getting rid of this, or changing the boxes without numbers to 0 automatically.
View 3 Replies
View Related
Apr 9, 2014
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))
View 3 Replies
View Related
Dec 10, 2013
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
View 5 Replies
View Related
May 8, 2012
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.
View 9 Replies
View Related
Dec 17, 2012
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.
View 1 Replies
View Related
Jun 26, 2014
I am working on a VBA routine that calc and verifies hash files for a series of folders and sub directories, using a command line prog called exactfile.exe (exactfile.com). It is very similar to M$'s FCIV utility, but it is actually supported on Win7/Win8. The issue I am having is that I cannot get the standard output/error/console stream to redirect to a text file. I've written the routine multiple ways, trying to figure out redirection. I was using this site
HTML Code:Â [URL]....
as a reference.
Here is my VBS approach. This code is dependent on another sub passing folder_name as a string
[Code] .....
The VBS then executes using wscript, however, since exf.exe does not return an exit code the same way FCIV.exe does, I need the results redirected to a text file. The results vary from a single line saying "No errors." or multiple lines indicating which files do not match their original hash.
Here are a couple iterations of my BAT approach, where i use the BAT to call the VBS. I really thought I would be able to use the standard redirect > here, since the executable is launched using cmd.exe, but I've not gotten the results I want.
[Code] .....
I've scoured more pages than I can count, but no approach seems to work, so far.
View 4 Replies
View Related
May 30, 2009
Need coding of command button to output text based on the contents of combobox1 and combobox2. The text should be placed in the next available cell starting in cell b3 for combobox1 and starting in cell c3 for combobox2. Each time submit is clicked the name and number associated with that name should go in the next free cell in the spreadsheet.
So for example the user choses name1 and number6 cell b3 would read name1 and cell c3 would read number6 after submit is clicked. The user then choses name58 and number12 and presses submit again cell b4 would read name58 and cell c4 would read number12.
View 4 Replies
View Related
Oct 9, 2007
I would like to output the text I perpared in " sheet 1" into a text file, "output.txt"
The imformation I like to output start from coulmn C, and column D then column E.....
The numbers of rows in each column could be different, and also there may be some single empty column in between. The totals numbers of coulmn is unknow.
the text in the output.txt should contant all the imformation in the sheet started from column C, coulmn D, column F.... (if coulmn E is empty). and the text in the output.txt file is continues with no gap in it.
To make it even more powerful for later use, The column that begin with NOT should not be output.
Example:
Excel Data-
A____B____C____D____E____F____G____H____I____J
1a....1b.....1c.....1d............Not.....1g
................2c.....2d............2f......2g
........................3d............3f......3g
........................4d
output.txt-
1c
2c
1d
2d
3d
4d
1g
2g
3g
View 4 Replies
View Related
Apr 22, 2013
I have a Sheet named Main, Juz and another called wordforword.
Currently on Main sheet I have a vlookup that displays the results from wordforword. Instead of showing the result, I would like the user to be directed to the wordforword sheet result, with the click of a hyperlink.
I do prefer formulas over macro if possible.
View 1 Replies
View Related
Sep 20, 2012
I am looking for Vlookup function, which returns multiple values without duplicates.
Please find the attachment : Vlookup Unique.xlsx‎
View 3 Replies
View Related
Jan 22, 2014
I want to set up a formula which automatically does vlookup or same sort of function on numbers given out by a link through a formula.
As a example i have attached the sheet.
View 1 Replies
View Related
Jan 8, 2009
I am making a company wide estimate sheet and having trouble with the drop down box. I have a drop down to pick the branch, but when I name the source it only says branch, even though I named the data on the branch sheet. I would like to be able to pick the branch initials and have it populate the phone and fax number under the main heading but am getting a error there.
Also for the labor is it possible to have it insert the correct labor into the formulas at the labor lines according to which branch is picked from the drop down.
View 4 Replies
View Related
Dec 8, 2009
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 Replies
View Related
Dec 18, 2009
i 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 Related
Nov 10, 2011
I have a problem with VLOOKUP function and I am looking for a workaround.
SheetA I have a column of business names and another column of the category.
SheetB has a list of credit card transactions and a column of categories - The results obtained from incorrect results from SheetA...
View 7 Replies
View Related