Basically, I want to use look in one cell to get a value then in a table look in a coloum to find the same value then return a value from a different column in that row.
This is to organize groups of children participating in an event.
See the attached spreadsheet for details.
Groups example.xls
I am looking to write a formula that will return the row or column number of the cell that contains a particular text within the string. I know it can be done with script, but need it as a formula. Also, to add another level of difficulty, I'd like it to be a traditional formula, no special keystroke to activate. It will be used within a much larger formula.Ex. If G5 has the string "CURRENT DATE" in it, and I look for "DATE", the formula would return 7 for the column or 5 for the row.
View 4 Replies View RelatedIf I have a column full of data is there a way to copy every nth row to a new column? For example, I may have dates and corresponding days of the week. Can I copy the date of each tuesday (i.e. every 7th row) to a new column?
View 2 Replies View Relatedsince my previous post got a little complex I will make it easier so I can continue puzzling myself.
I have a value "10:00"
and a table:
00:00 08:00
08:01 12:00
12:01 14:00
etc
Now I sue LOOKUP("10:00",A1:B3). This will return "12:00"
But I would not want it to return this VALUE but want it to return ROW or COLUMN. I tried ROW(LOOKUP.....) but this doesn't work.
How do I look it up and then return either B, or 2 (in this example)
Once I have this I combine my 3 way lookup by combining my new found knowledge
I'm trying to return the # month in which a particular event occurred.
Example:
...1 2 3 4 5 6 7 8 91011 12 13 14 15 ....
...J .F M A M J J A S O N D J F M .....
1 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0
2 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0
3 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0
4 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0
5
.
.
.
.
I need a formula that will tell that for Item #1, Value 1 occurred in month 11, and #2 was month 8, etc. I would have thought that this is common, but I searched the postings for a long time. Maybe I'm over-complicating?
I would like to have values from every 10th row in the table.
So I want to have B2, B11, B21 & so on...
Why is lastRow always returning 1? Even after adding values to worksheet and running macro - its always returning 1 for lastRow.... Am i missing something here? Do I need to refresh worksheet or something? I just dont get it :/
.Range("B:B").End(xlUp).Row
Code:
Option Explicit
Private lastRow As Long
Private curRow As Long[code].....
In the attached example I paste in a report from another system into Sheet2 (rough data layout in example).
In column B in Sheet1 I have tried using Match and Index formulas to try and return only the Local Shipment figures from on Sheet2 for each staff member.
The issue i'm having is that the data in Sheet2 can change based on how long the report is run. There is also no way I can change the way the raw data is set out in Sheet2 as it's an external system.
Basically i'm looking for a formula that isn't locked down to certain cells and can work with data moving up and down columns at different stages. The key data never changes from column A and F in sheet2. But what does change is whether each person has one or both of Sales & Local Shipment data in the reporting. This obviously changes based on what is completed each day.
So I need a formula that matches the persons name in column A in Sheet1 with Column A in Sheet2, then looks for the first mention of 'Local Shipment' below it and returns the cloumn F value which is in the same row as 'Local Shipment'.
I have a table of data-
IDDescriptionDatePax Count
316COACH01/07/20135
323COACH3/07/201410
324COACH18/07/20141
325COACH20/08/201420
333COACH21/09/201445
345COACH22/09/20143
351COACH23/09/20143
362COACH21/10/20142
371COACH22/11/20142
394COACH23/12/20140
416COACH23/12/201418
422COACH23/12/201439
Example, the real data is about 38,000 entries. I want to build a query to return the value of Column D (pax count) based on the values of Columns A (ID) and C (Date)
This would feed into a second, ID specific table, like this-
ID 536 Bookings JULY 2013
1/07/20132/07/20133/07/20134/07/20135/07/20136/07/20137/07/2013
(value) (value) (value) (value) (value) (value) (value)
SUMPRODUCT, usually my mainstay, is for some reason letting me down, returning a value of 0 for everything irrespective.
The first two columns are an example of my data set; I would like a formula for the 3rd column (Length+1) which will give me the length at the next age (i.e the value in the cell below in most cases) unless the following age is 1 in which case I want it to return zero.
Age Length Length+1
1 22 51
2 51 78
3 78 94
4 94 0
1 31 46
2 46 69
3 69 85
4 85 96
5 96 103
6 103 0
1 19 36
2 36 52
3 52 0
I want to return the proper value from sheet 2 into column C in sheet one. I have the current formula set up as an array but I only get the value that matches the first row in Sheet 2.
HTML Column A Column B Column C
Date Number Who
3/23/2007 902-555-2596=IF($B2=Sheet2!$A$2:$A$2001,Sheet2!$B$2:$B$2001,)
3/23/2007 980-123-6621=IF($B2=Sheet2!$A$2:$A$2001,Sheet2!$B$2:$B$2001,)
3/23/2007 980-123-6621=IF($B2=Sheet2!$A$2:$A$2001,Sheet2!$B$2:$B$2001,)
3/23/2007 980-555-6621=IF($B2=Sheet2!$A$2:$A$2001,Sheet2!$B$2:$B$2001,)
3/23/2007 980-678-0352=IF($B2=Sheet2!$A$2:$A$2001,Sheet2!$B$2:$B$2001,)
3/23/2007 902-555-7958=IF($B2=Sheet2!$A$2:$A$2001,Sheet2!$B$2:$B$2001,)
3/23/2007 980-555-6621=IF($B2=Sheet2!$A$2:$A$2001,Sheet2!$B$2:$B$2001,)
3/23/2007 980-555-6621=IF($B2=Sheet2!$A$2:$A$2001,Sheet2!$B$2:$B$2001,)
3/23/2007 902-555-7958=IF($B2=Sheet2!$A$2:$A$2001,Sheet2!$B$2:$B$2001,)
3/23/2007 902-555-7958=IF($B2=Sheet2!$A$2:$A$2001,Sheet2!$B$2:$B$2001,)
~~~~~~~~~~~ Sheet 2 ~~~~~~~~~~~~~~~~~
Column A Column B
902-555-7958Big Bird
902-555-5100Daffy Duck
902-678-0352Elmo
902-555-5752Donald Duck
902-555-7420Bert
980-123-6621Ernie
902-555-2596Grover
Ok, so I have a column of data in 24hr time format over a 24hr period from noon thru midnight and back to noon again. It has been necessary to make some of the cells in the column blank, and I am having difficulty extracting the first and last non-blank entries of the column (basically I want the earliest and latest time in that specific 24hr period). The time data is in order, so for example, some of the first values in the column are 14:30, 21:10, and 22:40, while some of the last values in the column are 02:20, 05:50, and 11:00, therefore the values I require are 14:30 and 11:00.
Any help that anyone can give on this matter would be greatly appreciated, but until then I guess I will keep plodding through the help sites and playing with formula until I spontaneously combust.
I have an application that on the "Main" sheet, is to extract two numbers then search for them on my "Listpoint" sheet and finally return the text to the right of the search data (e.g. K3)
Working left to right - the user pastes upto 12 lines of code into C3-C22. Formula in E3 extracts object Nos. Formula in K3 substitutes "first" number if it is a zero (with number from A3).
Left to do - Uses data (H3) to search Listpoint sheet colums C and B for a match. then returns text from Column S.
Note Listpoint has 1000 rows to search.
Main
ABCDEFGHIJKL1 2Osn Number Prog Point Text Extracted
ObJNos Zero replaced Name of Object 1 38 10 IF POINT 0|199 ON OR POINT 8|191
ON THEN RETURN FALSE 0 1998 191 8 1998 191
Example found Text 4 20 IF POINT 0|106 OFF THEN RETURN FALSE "HOLD OFF"
[Code] ............
I have row of dates and underneath those dates I have a row of numbers. The numbers count down. For example:
[Code] .....
At the end of the row I want a cell to return the date that the row first started going negative at. In this example, it should display 10/31/2014 (for when it went to -60).
I have 2 columns:
VAL0085597220EXP0000207731
VAL0122473124EXP0000207864
VAL0169412496EXP0000207879
VAL0173317027EXP0000208330
VAL0172476750EXP0000208703
VAL0173317028EXP0000209148
VAL0047743340EXP0000209666
VAL0176241996EXP0000209668
VAL0169594337EXP0014327602
VAL0047743872EXP0014327702
VAL0158185837EXP0014329002
I am trying to match where these are located in another column
I'd basically like a formula that finds a number (eg 220) within either the first 10 rows (or with row 5 if this isn't possible) and returns the column Letter as the result. The number will only appear once and only in one column. If it matters the columns go all the way to EC.
I want to produce a spreadsheet which automatically tidies up a transferral of data from a programme called sage in to a customer service spreadsheet for my company.
I am a bit of a novice but a keen one so please be patient! I do know what I want to achieve in the long run!
The problem I have at the moment is recognising products in one column and getting it to return the value in or out of stock in another. So far I have got it to recognise one product and return the correct statement (using the "IF" function) but i need it to do it for the whole column and for a range of products.
I'm trying to find a way to test is the max value in a range, columns A-F, comes after the min.
Example: Columns A-F have the values 4,1,3,5,2 respectively. I'd like a test to see if the min, in this case Column B, comes before the max, column D, and have an output that says yes or no. So in this example yes but if the numbers were instead 4,5,1,2,3 it would return a no.
Excel user that has been manually computing a 6000 row spreadsheet. Here is sample data and what I need. Column A goes through the alphabet (A,B, C, etc.) and Column B is numbers from 1-65. I need Column C to count the number of rows for each alphabet/number pair. K, 28 has 3 rows; K, 33 has 1 row; L, 21 has 4 rows; etc.
Column A
Column B
Column C
K
28
3
K
28
3
K
28
3
[code]....
My Data
Service 1 | 100 | 1.5$
Service 1 | 200| 2.5$
Service 1 | 300| 3.5$
Service 2 | 100| 11.5$
Service 2 | 125| 21.5$
Service 2 | 300| 30$
I need to extract the value in the third column according to an exact match of the first column (the service name) and a range match of the third column.
I tried SumProduct but the problem with my formula was:
Let's assum I ask for
Service 1, 200
The formula summed up 1.5 and 3.5 and returned 5
When I wanted to return one value from this table.
I need to return the column header in a table which corresponds to the column that the value I'm querying is in. I've been trying to use the INDEX function coupled with MATCH and COLUMNS function but I'm not getting anywhere. I've found on the web a solution that uses SUMPRODUCT but I can't get that to work either. Unfortunately, I can't supply the data I'm working with as it is confidential. I know my question would be easier to answer if I could supply it.
View 2 Replies View RelatedI have two worksheets. On one worksheet will be a place to enter a five digit number. In the adjacent cell, I want to search on the second worksheet in a three-column area for that same five digit number. The five-digit numbers on the second worksheet are located in the third column and the text I want returned from the second column. I cannot change the order of the columns as the first worksheet will be an "add-on" worksheet to a widely used spreadsheet within our company (this spreadsheet already has the second worksheet formatted).
View 9 Replies View RelatedBasically; there are three main columns in the first worksheet (lets call it "Main Data"): OrderNumber, TaskName, SignOffDate with data listed as follows. The actual spreadsheet has hundreds of order numbers but i'll keep it simple and lets go with two.
OrderNumber
TaskName
SignOffDate
1
OED
01/05/2012
[Code] ........
What I am trying to do; is sort this data in a second worksheet (lets lable it "Output") so that the sign off dates for each task; for each order; are listed within 1 single row. Ie:
OrderNumber
OED
CTN
FAD6
RFS
1
01/05/2012
17/05/2012
22/05/2012
02/06/2012
2
03/05/2012
19/05/2012
27/05/2012
02/06/2012
There are many orders in the main data; and I'm not sure what to do exactly to return the sign off dates for each task for each order without creating separate worksheets for each task name; then using vlookups to find each date.
An order may have a sign off for all task names, or none at all. In addition to this; they may not always be in the same order as listed above.
I'm using Excel 2010.
I am using Excel 2003. I have attached a data file here. getting the values in Q3, R3 and S3.
Scenario:
Q1 has the number = 1. So I want the cell Q3 to return 2/11/2013 as that is the cell corresponding to the Item1 (value specified in P3) with the value 1(value specified in Q1) in the cell. Basically, I need the date corresponding to cell which has the value of Q1 for the value of P3.
Similarly, R2 must have the value 2/12/2013 and S3 must have the value 2/14/2013 returned.
I have two Columns C and E that have dollar amounts in them. I want to compare each row in those columns and then total the number in column C that are higher that column E.
So basically C6>E6, C7<E7, C8=E8,C9>E9 should return the result of 2. There are two instances where the price in C is higher than the price in E.
i have this formula in cell e28 =IF(B28<>"",LOOKUP(B28,STOCK!A1:A500,STOCK!B1:B500),"") and when its empty it returns a blank cell
but in cell e29 i have exactly the same formula =IF(B29<>"",LOOKUP(B29,STOCK!A1:A500,STOCK!B1:B500),"") but i returns #n/a
why is this and how do i stop it i want the cell to be empty unless there is a code entered in b29 in which case it will look up the relevant info for me
I am looking to return the larger value of 2 cells into a seperate cell.
View 2 Replies View RelatedI had this problem a few days ago and NVBC suggested Text to Columns, which worked initally but didn't seem to 'save' or stick... also, I'm not sure what is causing the problem to begin with. It was my understanding that Excel couldn't return a truely 'blank' value. If a cell was blank or empty, Excel still 'sees' it as Zero. WHY then, for the Love of Gummi Bears, will Excel not calculate my formula. It's a simple formula!
=A13+B13
I've tried
=SUM(A13+B13)
I've tried
=IF(ISERROR(A13+B13),"",A13+B13)
But if don't TYPE in a 0 or a number, then it just does nothing. 50 + 0 = should be 50 not blank or #VALUE!. 50 + Nothing = should be 50 not blank or #VALUE!
Attached is a small copy of what I'm trying to do. I've double checked that all number cells are formatted to numbers, not text. I tried the Data:Text to Columns trick. I've tried Tools:Options:Show Zeros. I've tried Manual and Automatic Calculation. They shouldn't have to type in 60 cells of Zero to get the simple calculation.
I'm linking one sheet in a workbook to another. The formula is simple,
='Link 1'!E1
If there is data in the cell, it replicates it which is what I'm after. However if there is no data in the cell it returns a "0", not what I want at all.
I am trying to return a cell with a simple yes or no answer and having a little difficulty.
I have entered the following formula which is returning #NAME
=IF(E4<=1.25,ORH4<=0.01,yes)
Calculation needs to be as follows:
Install Cost <1.25 and Profit atleast 0.01
The YES NO needs to be in the Invite? column