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?
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:
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.
I have a formula in sheet code to get a total of the following columns. The formula in these columns is not working. I'm not getting any error. The code is working fine without a require output.
Is there a function that will output the column letter? For example there's one I know of: =COLUMN(), which outputs column number, but not the letter. And if not, can a formula be written to output it without converting the spreadsheet to R1C1 style or using the lookup function that refers to a separate table within the spreadsheet?
I'm having where basically I've got the below scenario on a spreadsheet:
Start Date - 14/03/2013 (Cell A2)
I want to look up this date against a 52 week structure to see where it is in our "working calendar year". I've got a table which has the following fields:
This table follows the same format for all 52 weeks.
Now in this example "Start Date" falls between the "Week Start" and "Week End" date so I want to output Week 1 in cell B2. However I could have a "Start Date" of 29/03/2013 that falls in week 3 of the working calendar.
Attached is my sample data Sample Data_Combine Sheets.xlsm.
What the code in the attached sheet does is that it combines the data in Sheets Set1 and Set2 into the worksheet named "Combine".
Both input sheets share same columns. Code I use is as below:
VB: Sub BuildPivotData() Dim wrsht As Variant Dim i As Integer
Sheets("Combine").Range("A2:AI500").Clear wrsht = [{"Set1", "Set2"}] For i = 1 To UBound(wrsht) Sheets(wrsht(i)).[a2].CurrentRegion.Offset(1).Copy Sheets("Combine").Range("A65536").End(xlUp)(2) Next i End Sub
I want to amend the output in such a way that when the macro is run , in the "Combine" worksheet an additional column is added at the start(col A) with header as "Category".
The category should be nothing but the input Sheet name i.e Set1 or Set2.
I need a 'simple' vba that outputs all the dates in a range that correspond to a particular ID in a column as well as that same ID in a column beside the dates.
So basically using the data in columns A to B as input, to get columns E and F as output.
I want to get value from reference one column and return value by corresponding row from another column. This can be done by vlookup column. But it find value from 1st column and return value from given relative column. That mean find from Left column and return value from right side given column number.
But I want find from Given column number and return value from given left column. For Further detail see the Attached file.
In the first vlookup it picks up a value. I need to do the same vlookup function but it should also pick up another value in column 9 and should add value in column 7 and 9 and return it. Employee id numbers are in column E in the first page.My formula checks for the employee id in Su sheet and retrieves the value from the seventh column. I need to retrieve also from 9 th column and add them together.
I have a table as below (you can also refer to the attachment) and need to do the vlookup with EE Code if the left 2 characters of "EE Code" = 91 to 95.
EE Codedeptif left 2 characters of EE code= 91 to 95, vlookup dept code 921600012345678 vlookup arraryA12345623456700 39914123 test785637039914123 38214125 test915580057889056 40964197 test932660038214125 77014155 test913580039914123 345699940964197 945800077014155 953790300123432 915800012345678 045699957889056 585637039914123
use the VLOOKUP function when the column I need to search is not column A?
I have a Workbook with two worksheets in it and in Sheet 1 I have 10 columns of various text and numbers. In cell A1 of sheet 2 I will fill with a certain code that appears in column E in Sheet 1. The result I would be looking for should come from Column A in Sheet 1. I thought that the following would work copied into B1 of Sheet 2 but it doesn't appear to - =VLOOKUP($A1,'Sheet 1'!E$1:$E$481,1,FALSE).
Currently, I have a standard vlookup forumla that looks like =Vlookup(B2,Sheet2!A:G,5,false)
However, the data on sheet two is ever expanding--each month, specifically. And I need to return the most recent (so, rightmost filled) column. Right now, the column is 5, but next month it will be 6 and so on and so forth.
I'm trying to create a program for punting with social club at work. In column B has five people's name and column C has their weekly prize money. In column D has the same five names as in column B but only for total win next to them in column E. Each week, we will add same five names in column B (same column) with the prize money in column C. Now, in column E is where it collect total sum for each person from column B. Is it possible to add dollars in Column C with vlookup for the same name in column B.
I want to merge two tables with keeping the data in the two tables in two worksheets, so my idea is to create a drop list in one of the two worksheets and depending on what the user will chose the datas will appear. for informations, the datas are all Vlookup in the same workzone but for each table the lookup will be done in differents columns, so how to make the column for the vlookup changement and depend on what I choose??
clarifications: it's like the choice on the droplist will be a link cell to the column of the research.
ABCD E F 1Scenario A Scenario BScenario C 2Studio1520 31Bed1520 42Bed150 5 6 7Scenario A Scenario B Scenario C 8Studio5% 10% 15% 91Bed10% 15% 20% 102Bed15% 20% 25%
RIght now my formula For Cell D2 is
=IF(C2=0,(B2*-$D$8),0)
I need the formula to Match what is in Column A and Also what is in Row 1 to what is in the table (C7:F10)
I have attached a sample. The formula seems to be correct, as I have the same formula in another spreadsheet and it works fine. As you can see, in A9 I have placed a VLOOKUP formula, but the formula wording stays in A9 when I hit ENTER instead of inputting the answer. The purpose of the formula is to look up B9 on Sheet2 and input the correct customer code (in column B on Sheet2).
As you can see the letters repeat, and the dates are in order. Dates may repeat or be skipped.
I want to know how I retrieve the last date in the list that corresponds to a certain letter. For example, the last date that corresponds to "A" is 10-Jun-12. Similarly for "B", it is 11-Jun-12.
VLOOKUP will retrieve the first day.
In this case corresponding to "A": =VLOOKUP("A",A2:B12,2,FALSE)
Is there any way I can retrieve the last day with a formula?
I have a huge list of data where in column A there are customers names. Column B i can see products they are renting and column C-Z are uinits that they are renting on a given date. (lets say c is August 1st, D is August 2nd and so on). What I want is that if i a new tab in A1 enter a given date and A2 a given model, I want in A3 a formula which would provide me with Totals of that product on specified date by all customers.
Customer name Product Aug 1 Aug 2 Aug 3
A Ice 2 6 4
[Code] .......
So lets say If I ente August 2 and as product Blow, I would like to know how many products of Blow are rented on that day. In this case 3 .......
I am having 2 workbook. I want to vlokkup / extract the relevant column values if Lookup_value DOES NOT MATCH.
Ex: Workbook1 A1=PPP-00099 Workbook2 C1=PPP-00088 E1=786 In F1 (of Workbook2), answer would be 786 since C1 DOES NOT MATCH with A1. If matched, answer required="" (null).
I have a 27R x 19C table (1st row is headings) that I want to sort. The column that I want to sort on (column O) contains a formula that multiplies two values, each of which is obtained by a VLOOKUP formula. The formula in the cell is: VLOOKUP(E3,StrategicImpactTable,2,0) * VLOOKUP(L3,StatuteRegulatoryImpactTable,2,0). Named ranges StrategicImpactTable and StatuteRegulatoryImpactTable each contain two columns, the second of which is a numeric value associated with the text in the first column.
On the first row VLOOKUP(E3,StrategicImpactTable,2,0) evaluates to 3, VLOOKUP(L3,StatuteRegulatoryImpactTable,2,0) evaluates to 2, and the product results in a numeric value of 6. The 26 values in column O are 6, 6, 4, 2, 2, 4, 4, 6, 4, 4, 4, 4, 9, 9, 9, 3, 3, 9, 9, 9, 4, 9, 9, 9, 9, 9.
When I sort the table descending on column O, the table does not sort properly, with no discernable order. Why?
Sheet1 ABC1S.NoCountryCode2E8481234567CroatiaE8483E8351234568SloveniaE835472123456900Ireland725JM123457000LuxembourgJM Excel tables to the web >> Excel Jeanie HTML 4