Find Matching Document Value And Max Date Using Function Based On Certain Parameter
Sep 22, 2009
I have the following info in a table:
name | doc date | doc value
a | 2009/01/01 | IN111
b | 2009/02/04 | IN222
c | 2009/02/05 | IN333
a | 2009/01/05 | CN111
d | 2009/03/01 | IN444
b | 2009/03/01 | CN222
a | 2009/04/01 | IN555
Firstly, I need to find the LAST DOCUMENT DATE for "a" where #doc_value starts with IN*** (invoice). Manually, I can see that it would be "2009/04/01", but my spreadsheet runs 6000+ entries. I need a function to do this.
Secondly, I need to find the corresponding #doc_value for that date (in this case, IN555).
View 9 Replies
ADVERTISEMENT
Feb 19, 2009
In a row on the spreadsheet I have a range of dates from 19/09/2008 to 19/06/2008 mainly with a weekly interval.
I want to incorporate into the macro a find function so that it can determine the first occurrence of a specific month and return the row number.
View 9 Replies
View Related
Dec 8, 2009
I am trying to return a TRUE or FALSE based on a date in a cell.
for example:
if cell A1 = 07/11/2009
I want A2 to show TRUE or FALSE if A1 is 14 days or more behind todays date.
I have tried stuff like:
=IF(A1=TODAY()-14,"True","False")
but it just always says false. EDIT: I have just noticed that if I change the date in A1 to exactly 14 days behind todays, it returns TRUE. So, it does work. Do I need to add a GREATER THAN in there?
View 6 Replies
View Related
Jun 7, 2009
I have the following dataset:
[Date] [Category] [Currency] [ExchangeRate] [.....], etc.
1-3-09 A USD 0,8
1-6-09 A EUR 1
1-7-09 A USD 0,7
1-8-09 B USD 0,9
1-9-09 B USD <formula>
I'd like to have the value of <formula> looked up in older records. Currency and category should match and it should pick the exchange rate with the maximum date.
Which formula and what syntax should I use to have this done?
I use Excel 2007.
View 10 Replies
View Related
Aug 5, 2014
I am trying to use some vba match function code to return the column number of the matching date. The date will be stored in a date variable.
Every time I run this code I normally get a match error even though the date is in the worksheet and the variable matches that date.
See below:
[Code] .....
View 5 Replies
View Related
Dec 12, 2012
i want to search OK7V0T in all cells and return the cell reference beside matching data row
serial 1serial 2serial 3
box-4OK4KCUOK4KI0 OK4LLS
box-4OK60ZDOK62AP OK62QU
box-4OK6TA5OK6TBP OK6TFW
View 4 Replies
View Related
Jan 8, 2009
is there anyones know what's the meaning of 1 in this formula
MATCH(1,('Data Issues'!$A2=Sheet1!$A$2:$A$68)*('Data Issues'!B2=Sheet1!$B$2:$B$68),0)
View 2 Replies
View Related
Aug 3, 2009
I have a formula that hides rows within a range for a sheet.
View 2 Replies
View Related
Feb 25, 2010
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.
View 10 Replies
View Related
Feb 15, 2010
I would like to write a macro that automatically hides columns of data
based on the value of a cell (I2) with a picklist. Cell I2's picklist is
monthly values (formatted as Jan-10 though Dec-10 but real values are
1/1/2010 through 12/1/2010). I have a range that contains work week end date
values (1/8/2010 to 12/31/2010) in L6:BK6. I would like to have the macro
hide columns that are less than date value chosen in I2.....
View 3 Replies
View Related
Jul 31, 2013
I though I could do this with a nested IF statement but it is too cunfusing for me. What I am trying to accomplish is this:
Experiment
Is Steward
EU ID
Location
Data Quality
GE
Entry Order
[Code] ........
I want to have a screen pop-up asking me what my limit < would be for column "ESTCNT" so if I put in 25 or any other number that it would highlight all the rows that are less than 25, then look at the row above and below and if it matches the same number (that is in the cell "Range" of the highlighted column) in column "Range" then copy that row to a new sheet. Meaning all tha rows that match the "Range" would be in the same new sheet.
The rows might be different lengths and that there will not always be a number in cell "ESTCNT". Column headers will always be the same but might not be in the same column each time. And if it is not to hard once it is completed to find column "SPPLOT" in the new sheet created and asking what I want to autofil the column with.
View 2 Replies
View Related
Jul 25, 2014
I am looking for a formula that returns the latest Sale date for a each model of car. Below is sample data which I am trying to use the formula. I tried with below formula, but not successful.
=MAX(INDEX($A$1:$C$40,MATCH(A2,$A$1:$C$40,0),3),1)
MakeModelSale Date
AudiA4 11-Jan-14
AudiA4 quattro 12-Jan-14
AudiA4 quattro3-Jan-14
AudiA5 Cabriolet 14-Jan-14
AudiA5 Cabriolet q15-Jan-14
AudiA4 16-Jan-14
AudiA4 quattro17-Jan-14
AudiA4 quattro18-Jan-14
AudiA6 quattro19-Jan-14
AudiA4 10-Jan-14
AudiA4 quattro09-Jan-14
AudiA4 quattro10-Jan-14
AudiA4 11-Jan-14
AudiA4 quattro11-Jan-14
AudiA4 quattro11-Jan-14
AudiA8L 11-Jan-14
AudiA8L 11-Jan-14
View 3 Replies
View Related
Feb 22, 2009
I have created a spreadsheet which creates an average of feedback for trainers in a training company. The form adds up the feedback score into column L of the summary sheet and I have created a summary sheet which I want you use to calculate the average for each trainer.
I have cobbled together an array formula which creates the overallaverage for each trainer based on the named ranges entered via the form.
It looks something like this:
View 10 Replies
View Related
Mar 9, 2007
I am comparing two series of data, a daily series and a weekly series, to make calculations. The "F" column is daily data and the "I" is weekly.
The formula I am using is:
=IF(AND( SUM(I58:I62)<F62,I63>F63),B64,0)
The data I need to use in the "F" column in place of "F62" is always on a Friday.
I had taken "F62" which is the preceding working day's data. In fact I need to take the preceding Fridays data in column "F" as I need to compare the week to week trends. Due to bank holidays I cannot count back form F62 to get the data. There is always end of week data recorded on Fridays even though it may be a bank holiday.
I have the dates in the "H" column. Can a date reference be used in the formula to find the Friday data in column "F"? Or is there another solution to this problem?
View 9 Replies
View Related
Apr 3, 2014
I have a workbook that is pulling data for every hour of the day from an internal website. The macro is built to pull data 24 times (each hour)
ex.(http://url/[""2014/04/01""]/starthour0/endhour1/)
(http://url/[""2014/04/01""]/starthour1/endhour2/)
(http://url/[""2014/04/01""]/starthour2/endhour3/) and so on.
What I am trying to do is set up a parameter that will reference a certain cell (Master!K5) which will contain the date I need to pull. I want to be able to have that cell referenced automatically and input the date for each URL in the macro.
View 2 Replies
View Related
Nov 7, 2002
I am attempting to use MSQuery to extract records from an Oracle transaction table by passing user defined Date/Time field parameters.
So if the date range for the query is for Fiscal Period 10 of this year, my parameters would be:
[StartDate] = #2002/10/1#
[EndDate] = #2002/11/1#
Daily transaction activity is in the early morning hours ending at 6:00AM so my Criteria is:
Between [StartDate] + .25 and [EndDate] + .25
Note: The +.25 represents 1/4day = 6hrs (6:00AM)
If I use the hardcoded Start/End Dates, my query works fine, but if I try and
pass the dates as parameters I get "ORA-00932 inconsistent datatypes" error. And I can't seem to pass the Date/Time combination successfully....
If anyone has encountered a solution for how to pass a Date/Time Paremeter into MSQuery I would love to hear how you did it.
I'm starting to pull my few remaining hairs out!
[ This Message was edited by: Tuner on 2002-11-07 12:23 ]
[ This Message was edited by: Tuner on 2002-11-07 12:28 ]
[ This Message was edited by: Tuner on 2002-11-12 12:51 ]
View 6 Replies
View Related
Mar 28, 2014
I'm trying to combine the hours that a employee worked on a single date, with one of multiple time periods that exist for that employee.
I have two sets of data.
Set 1 (hours)
Employee number, date, hours
12345, 1-2-2014, 6
12345, 1-3-2014, 8
12345, 1-10-2014, 8
Set 2 (periods)
Employee number, start date, end date
12345, 1-1-2014, 4-1-2014
12345, 6-1-2014, 1-2-2014
What I'd like to do is to add the start and end date of Set 2 to Set 1 for every row in Set 1
In above example the result should be like this.
12345, 1-2-2014, 6, 1-1-2014, 4-1-2014
12345, 1-2-2014, 8, 1-1-2014, 4-1-2014
12345, 1-10-2014, 8, 6-1-2014, 1-2-2014
View 13 Replies
View Related
Feb 22, 2013
Is it possible to see why this code returns a date that is almost correct? Searching for 01.01.2005 I get 01.11.2005 as result. I would like the code to only show the correct date, and if it doesn't exist it should end up showing "nothing".
Code:
Set fstdate = wsOBX.Range("B1:B" & lr1).Find(what:=startdate, _
LookIn:=xlFormulas, lookat:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
View 3 Replies
View Related
Jun 11, 2014
I have a workbook with 25+ worksheets, all identical formats that each represent a different client. Each client is assigned a project team. I want to be able to sum up the worksheets by project team, but have flexiblity in doing so when I add new clients etc. So a couple of thoughts.
1. Is there a formula that I can create that compares the worksheet name to a list of names assigned to a particular client?
2. Can I reference a cell in each worksheet that identifies that sheet as a client that is to be included in the team summary?
Example of worksheet sequence
Worksheet - Summary Sheet - All Clients
Worksheet - Client 1 Team Alpha
Worksheet - Client 2 Team Beta
Worksheet - Client 3 Team Alpha
Worksheet - Client 4 Team Beta
Worksheet - Client 5 Team Alpha
Worksheet - Client 6 Team Beta
I want to create summary worksheets (identical to the individual client sheets) for each team.
View 1 Replies
View Related
Oct 1, 2006
I have the following
Private Sub Workbook_Open()
Worksheets("Sheet1"). Range("L5").Value = Date
End Sub
When i try and run the code it returns an error saying: "Compile Error, Cant Find Project Or Library" and it highlights the word "Date"
View 7 Replies
View Related
Apr 25, 2014
I'm in Excel 2010, and the cell with the date I want to work from is H22.
I'm trying to get the difference of the (date+12 months)-TODAY() to appear in months and days.
Here's the latest thing I tried (that doesn't work):
=IF(DATEDIF(H22,TODAY(),"y")>=1,DATEDIF(H22,TODAY(),"y")&" yrs, "&DATEDIF(H22,TODAY(),"ym")&" mths,
"&DATEDIF(H22,TODAY(),"md")&" days",IF(DATEDIF(H22,TODAY(),"ym")>=1,DATEDIF(H22,TODAY(),"ym")&" mths, "&DATEDIF(H22,TODAY(),"md")&" days",DATEDIF(H22,TODAY(),"md")&" days"))
I should also probably note that the date in H22 is the result of another function.
=EDATE(G22,12)
View 5 Replies
View Related
Dec 18, 2012
I currently have a code that searches a folder, opens a spreadsheet, and then copies over the "Status" tab to the Master document.
New spreadsheets will be created a couple times a week, so I would like the code to be able to search this folder for the most recently modified document and then open THAT document.
So, if my folder has..
Report 10May11
Report 05May11
Report 17May11
Then, I would want a code to search in this folder for the most recently updated document (in this case "Report 17May11.xlsx) and then go through the step I have above where it copies over the various tab.
Basically, this code would I guess replace the example code I have below.
Sub WorkbookOpen()
....
End Sub
View 8 Replies
View Related
May 26, 2013
[URL]
how to find the max date of a value in column A. From there I need to link up the price associated with that date. VBA is preferred. The information I'm working with is structured like this in this image:
MaxDate.jpg
What I want to do using VBA, is to look at the values in column A and for each of them, find the max date or most recent date. Once that is determined, on a new sheet, I want to write the value found in column A in sheet2/ColumnA and the date in column B, and the price in column C. In a nutshell, I'm looking for an effective date for pricing. The example on the bottom is basically what I'd like to extract with code onto another sheet.
View 6 Replies
View Related
Apr 17, 2014
I've got a column with multiple dates and a lookup sheet with the weeks (start date/end date). What I'm trying to do is :
1) check date
2) look it up in the lookup sheet (where it falls between start data/end date)
3) once i find where it falls, return the corresponding week name/number
I've seen a solution using the Median function but I'm not here it would work in my case. link here
I've attached a workbook : Date_Sample.xlsx
View 12 Replies
View Related
Mar 20, 2012
I have a table of shipments made to my company broken down by style, color, and delivery date. I need to find the most recent delivery date for each style/color combination.
This is my table
ABCStyleColorDelivery Date1ABCRed1/1/20122ABC
Blue2/15/20123123Red3/12/20124123Blue1/30/20125ABC
Red2/27/20126ABCRed3/1/20127123Blue3/20/2012
and my desired results are as follows:
StyleColorMost Recent DeliveryABCRed3/1/2012ABCBlue2/15/2012123Red3/12/2012123Blue3/20/2012
The style and color columns are already filled in, I just need a formula for the date column.
View 1 Replies
View Related
Jul 7, 2006
I am trying to find the starting effective month for a workyear. The criteria for the selected month is that the data BEFORE the effective month is all zeros. I manage to get the result if the data AFTER the effective month is ALL non- zeroes. If there is any zero, the data fetch will be inaccurate due to the COUNTIF formula. Is there any other way or formula that will enable me to get the result. File attached for testing and reference
View 6 Replies
View Related
Jan 25, 2010
Ok so let me set this up. I have 2 columns: .....
View 14 Replies
View Related
May 14, 2012
I have one excel sheet. In the first column there are various Project Codes and in the first row there are various date. Cells in the matrix contains 0 or 1 value. If value is equals to 1 that means the project was on hold during this months and if the cell value is null of 0 (Zero) it means the project was active. For a project in a particular row there are several times a project went on hold and now I want a date range showing the project's hold duration. If project went on hold more than one time the result should also give different range for each time when project went on hold.
follow the link to view the excel file.
http://www.excelforum.com/attachment...3&d=1336994800
[IMG]http://www.mrexcel.com/forum/data:image/png;base64,
iVBORw0KGgoAAAANSUhEUgAABAAAAAMACAIAAAA12IJaAAAgAElEQVR4nOy9e3gT153/P99fd7vd77fxV+3udtO06fr3PL
[code].....
View 8 Replies
View Related
Sep 6, 2007
First one needs to look through a single row and find where the first cell with a value (text) in it, then finds the date that that cell is under.
Second one needs to look through the same row to find the last cell that has a value (text), then finds the date again that that cell is under.
Example workbook is attached
View 2 Replies
View Related
May 20, 2014
I'm working on a workbook that will track staffing patterns.
The workbook has three worksheets: Sheet1 "RCS", Sheet2 "HCT' and Sheet3 "Hidden". I've attached the workbook to this thread. The password for the form is "j".
On Sheet3 "Hidden" I have two tables that are set up to collect the SUM of columns on Sheets1 "RCS" and Sheet2 "HCT". I'm finding the SUM of each range by way of the background color. I've set up the following formulas and when the "data collection tables" are in the same worksheets as the original information, the formula's work perfectly:
The following functions are pulling data from Sheet1 "RCS" and placing them into a table in Sheet2 "Hidden"
[Code] ........
The following functions are pulling data from Sheet1 "RCS" and placing them into a table in Sheet2 "Hidden"
[Code] .......
I have two more functions that aren't working due to the fact that the source values are percentages and NOT plain numbers. The above functions work great for SUM but not for percentages. EXAMPLE--Let's say, 3 sub percentages it gives me the SUM of the 3 percentages (i.e. 85% + 100% + 100% = 285% instead of giving me 95%.
[Code] ........
How might I use the following functions to find the average of the source fields instead of the SUM?
View 7 Replies
View Related
