Excel 2003 :: Returning The Date In Column For Matching Values
Apr 4, 2014
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.
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'.
to return the name (drange) of a person who was visited by "SH" (qrange), more than 30 days ago (trange).
There are faults in my formula, and if I'm honest I don't entirely understand it !
I need to return all of the names of people who were visited by "SH" more than 30 days ago. So I need the next value, and the next which is also over 30, by copying the formula down to the next cell and the next. My problem is that I get the first value (which is correct) and then the next ones are blank.
To make matters worse, the first value I get is only correct if i DON'T enter it as an array. If I do enter it as an array, I get the first row of the spreadsheet.
I am looking to create a formula to add together the two highest values of a list of 10 numbers. I know you can use something like large(a1,a10,1)+large(a1,a10,2) if numbers are in an array but my problem is numbers arent they are in random positions along a row....
I have in my 'Daily' sheet I9 which needs a formula to look at the date in C9 and the scan range 'Weeks' F6:AN41 and find the cell with the matching date and then whatever text is in Row 2 of the column the date was in will be returned.
I have an Excel spreadsheet (XP - 2007) listing Job Nos. in the first column, with several columns of Station assignments and dates. Both planned dates and actual dates are included, adjacent to each other. The dates are not necessarily in a straight ascending or descending order. Separate arrays exist for: Plan Nametags, Actual Nametags, Plan Dates, Actual Dates.
Example:
Job No.Sta1 PlanSta1 ActSta2 PlanSta2 ActSta3 PlanSta3 Act1A10001-Feb-101-Feb-106-Feb-106-Feb-101-Mar-101A100116-Feb-1016-Feb-1019-Feb-1022-Feb-105-Mar-101A100225-Feb-1025-Feb-102-Mar-104-Mar-1010-Mar-10@ @ I need to capture two pieces of information for each Job No. on a daily basis: 1- The 'Planned' Station for the build, based on a match of the 'Plan' date fields to a pre-populated 'Report Date'. I've been able to do this (using INDEX-MATCH function). 2- The 'Actual' Station location for the build, based on the Maximum 'Actual' date entry in the row for each build. (In the example above, Job# 1A1001 would have an 'Actual' location of 'Sta2 Act'...) I need to figure out how to accomplish step (2) above. I've made several attempts with INDEX-MATCH and LOOKUP functions, without success.
I have a worksheet with a data sheet that has the values for a number of portfolios over a time period (one month)then I have a corresponding sheet for each portfolio (in the attached example I have included only portfolio 1). What I am trying to do is to make a formula in the sheet Portfolio 1 that matches the date in the Portfolio 1 sheet with the data sheet and by matching the date returning the value for the Portfolio 1 for the date ie date 2008-06-01 should return for portfolio 1 (from the data sheet) 500.
I have tried with varying Index and matching formulas and I do believe that this is the way to go.
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"
I have five columns of data: A & C are time measures; B & D are values associated with each time measure; and E is a matching value column that I want to populate. I am trying to match times and place in col E the Val 2 that matches times 1 and 2. Though many matches lie on the same row, there are many that don't (the real data uses several thousand rows). For example, Val 1 in cell A5 (100) is associated with cell C7 to find a matching value of 4.5 in cell E5. Note, occasionally, there will be replicate values for Val 2 (like cells C9 & C10). I need to match the one further down the list.
Excel 2010: I'm working with a sheet where I would like to get accurate results on the make and model of cars sold in a given week. The workbook contains several worksheets each representing weekly sales numbers for different vehicles.
The Make-Models tab has the lists used to create the drop-down selections.
The Sales tab allows one to create an ad hoc report on the total number of vehicles sold, by Make and Model. Cell B2 is named 'SelectedModel' and used in the formula found in column E, Total Sold.
Problem: When I select Honda, for example in B2 of the Sales worksheet then select Accord in cell B3 of the same. The results returned in E2, E3 and E4 are 2, 3 and 4, respectively, instead of 8, 3 and 7.
2 Honda Accords were sold on Monday and 6 on Tuesday of the same week. My formula is only returning the first matched value of 2 whereas I would like it to return 2+6, 8 for week 1. Same goes for other weeks.
Select any other make and model and notice the problem follows. I believe the error is due to incorrect usage of the formula or incorrect formula altogether.
see attachment below for easier comprehension of what I need to be done. Note that I have around 20 subjects (2 in example) and about 15000 values per subject (5 per subject shown) Basically, I want matching rows to be aligned (see grey) and non-matching rows to be deleted, throughout the 20 or so subjects.
We have arrival dates and departure dates for guests and would like to figure out the number of meals needed on a particular date.
Named ranges: NumGuests _ArrivalDate _DepartureDate _CheckInTime _CheckOutTime Need number of breakfasts Number of Lunches Number of Dinners for a given day of the week.
So if a guest is here say Jan 25 arriving after 9am, staying 2 nights they will have Lunch and Dinner on the 25th, BLD on the 26th and BL on the 27th. Since by default most arrive after 9 (miss breakfast), we can eliminate the checkin time for the breakfast calc and make the first one for the next day.
There might be other guests staying through that time period. The ******* would like to know how many meals they will need to prepare for the week.
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-
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
I have this nagging problem with my current data. So let i have two "sites" which contain a date column. Some of the dates matches. So i want to match this date in excel and not manually go cell by cell checking for a match. how can i do this with excel?
a better illustration of my question. So basically i want to match concord 4/1/1999 with the other concord of the same date.
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.
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 have to analyze our abandoned calls for the year. So far there are close to 2000 abandoned calls and I need analyze them according to the month and the time they were received.
I have two worksheets in my workbook the first worksheet is named DROPPED, this is were all the data is. The second worksheet is called DROPPED CALLS SUMMARY, this is where all the analysis happens.
On the DROPPED CALLS SUMMARY Worksheet I need to have the Sum of Column I IF ColumnA is equal to Jan-13(or whatever month) and Column B is equal to 8:00 AM (or whatever time). I'm using Excel 2003 so I cannot use the SUMIFS Formula and I can't find a formula to work with.
It will perform the sub-totalling for the column that has the current active cell
When I select Column I
It does..... -for each blue cell it finds it provides a total of all the white cells bellow it -for each yellow cell it find it provides a total of all the blue cells bellow it until it reaches a yellow cell
It works backwards, so not exactly as I've just described, but that isn't the problem
Problem is - UK Excel 2010 - results are as expected US Excel 2010 - returns zero values for totals
We've possibly narrowed the problem down to when it looks at cell properties, more specifically -- If Cells(rowX, 1).Interior.ColorIndex = 20 Then
How to get results in blue and yellow cells when you select a cell in column K then execute the CreateTotals macro.
In sheet2 I have a column "C" called addresses and in that column I have actual cell addresses such as $J$740, $H$756, etc., all referring to cell locations in Sheet1.
In sheet1 in column "B" are names.
I would like to be able to return the names from sheet1 column "B" to column "G" of sheet2 that belong to the cell address from column "C" in sheet2.
Is there an Excel formula or vb script that will do this?
I tried "=INDIRECT("sheet1!"&C2)" but all that did was return was is written in the cell address (for example $J$740) but what I want is the corresponding name in column B.
