Multiple Date Match
Jan 7, 2007
I am trying to achieve an index match of a range of upto 5 dates matched against a worksheet table and return "approve" or "deny" based on whether any of the dates in the range is counted more than 4 times in the table.
My table is named 'yearsheet A-Z' and is arranged A2 to A31 being numbers 1 to 31, with B1 to M1 being jan to dec.
The dates to match are in a sheet named 'All Hours' and are arranged in blocks of 5 cells eg A8 through A12, B8-B12 etc.
The table sums the number of times a date appears in 'All hours' and returns 1 in the relevent cell.
In another sheet I need the Approve/Deny return, so in B5 would match the dates in A2:A6 'All Hours' against the 'yearsheet A-Z' and return approve if the dates did not already have counts of >4 in the table, and deny if the count is already 4.
I have used the following array : {=IF(MAX(INDEX('Year Sheet A - Z'!$B$2:$M$32,MATCH(DAY('All Hours'!A32:A36),'Year Sheet A - Z'!$A$2:$A$32,0),MATCH(TEXT('All Hours'!A32:A36,"mmm"),'Year Sheet A - Z'!$B$1:$M$1,0)))>4,"Deny","Accept")}
This gives me the correct result if the first date in the range should cause a deny return, but if 2nd to 5th date in the range should return deny but the first gives approve I get approve.
View 9 Replies
ADVERTISEMENT
Dec 30, 2008
I am trying to create a formula that will lookup multiple criteria within a table, one of those criteria being a date that falls within a certain date range. The purpose of the data is for billing tenants and owners in a building. I have successfully been able to use Index Match to lookup 2 criteria, one of them being a specific date match (see my attachment). However, I need to add a third criteria looks up the date that falls between a specific date range. The concept is to run a query where I enter a Unit (apartment) number into one cell, a Billing Date into another cell and the Type (Owner or Tenant) into a third cell to determine the Name of the person who fits these criteria... Criteria A Lives in Apt XX, Criteria B Who's lease start date and end date is inclusive of the Billing Date that I entered and Criteria C who matches the Type of customer, either Owner or Tenant.
View 9 Replies
View Related
Oct 12, 2012
I'm trying to create a formula that will lookup multiple criteria within a table, however the problem I am having is that one of the criteria needs to fall within a certain a date range. I've used index & match to look up the dates when they match exactly, but how to return the same value for when they fall within a date.
I have attached the spreadsheet - Date Range.xlsx! The requirement is to get the correct "pay" from the "Contingent Workers" sheet to the Timesheet sheet. So it will match the Employee ID, but also the date on the Timesheet tab will fall between/or be the exact date indicated on the contingent workers tab.
View 3 Replies
View Related
Dec 30, 2008
I am trying to create a formula that will lookup multiple criteria within a table, one of those criteria being a date that falls within a certain date range. The purpose of the data is for billing tenants and owners in a building. I have successfully been able to use Index Match to lookup 2 criteria, one of them being a specific date match (see my attachment). However, I need to add a third criteria looks up the date that falls between a specific date range. The concept is to run a query where I enter a Unit (apartment) number into one cell, a Billing Date into another cell and the Type (Owner or Tenant) into a third cell to determine the Name of the person who fits these criteria... Criteria A Lives in Apt XX, Criteria B Who's lease start date and end date is inclusive of the Billing Date that I entered and Criteria C who matches the Type of customer, either Owner or Tenant. I have attached my sample spreadsheet.
View 7 Replies
View Related
Sep 28, 2006
I'm trying to have a date selector dropdown box added on sheet as where indicated. This will have an option of last 1-10 draws which will trigger from cells a4:a13. Next, I need a submission button put on sheet as where indicated. When pressed it will give results in cells m4:n29.
Now the match part will lookup the source #'s entered from L6:L11, note these source numbers are repeated for match lookup for each date. The match should look for matching numbers derived from cells c4:j13. If a match is found it will then display the colored text as indicated in my sample, if no match is found it will display " No Match " in black text.
View 9 Replies
View Related
Jan 21, 2014
See attached file, "Rate Sample Index-Match Formula".
I need a formula to return the value at the cross section of two (2) lookup values that match. This formula will be input into column D under, "RATE" on the 1st tab, "TEST FILE".
In the 1st tab, "TEST FILE" there are a series of columns as follows;
A = Service
B = From
C = To
D = Rate
In the 2nd tab, "RATES" there is a series of rates with drivers From (green) & To (blue)
The formula needs to do the following;
1. Lookup the "From" value in column B on tab, "TEST FILE" and match to column B2:B59 on tab, "RATES" both highlighted in green
2. Then Lookup the "To" value in column C on tab, "TEST FILE" and match to row C1:BH1 on tab, "RATES" both highlighted in blue
3. Then return the value at the cross section of the match "From" (point 1 above) & "To" (point 2 above) in range C2:BH59
For Example;
The rate From SYD To CBR = 0.33. I have highlighted this in yellow on both tabs to show where the formula needs to lookup the data to return the answer.
Additionally, if we were to add service as an additional lookup match how would this work?
View 3 Replies
View Related
Jul 11, 2008
INDEX/MATCH multiple ocurence match values needed
View 9 Replies
View Related
Apr 28, 2012
I am having such a difficult time creating a macro that will reduce the 5+hours I have to spend each week manually copying & pasting all of this data. I making an IMMENSE difference in this worker bee's life!
I have a workbook with two sheets (Sheet1 & Sheet2). Sheet1 has license #'s in column A and the state that the license belongs to in column B like this:
COLUMN ACOLUMN B11111Alaska11112Alabama11113Arkansas11114Arkansas
Sheet2 has three columns. Column A has the license #'s, column B has the state that the license belongs to and Columns C shows a line-of-authority tied to that license #.
COLUMN ACOLUMN BCOLUMN
C11111AlaskaProperty11111AlaskaCasualty11112AlaskaLife11112AlaskaHealth11112
AlabamaProperty11112AlabamaCasualty11113ArkansasLife11113ArkansasHealth11114
ArkansasLife11114ArkansasHealth12345ArizonaProperty
I'm trying to write a macro that will compare the license # and state in Sheet1 to the license # and state in Sheet2. If it matches, append the contents of Column C to the corresponding row in Sheet1.
Here's the thing...Sheet2 contains the entries for all licenses in the company (so this table is HUGE). And there are multiple entries for each state license # (notice how there's two entries above for AK license # 11111 - one for the Property line and one for the Casualty line.
After my macro is run, I want Sheet1 to show all the lines-of-authority on a single line. So if I ran my macro on the above example, after it's run I would have this in Sheet1:
COLUMN ACOLUMN BCOLUMN
C11111AlaskaProperty Casualty11112AlabamaProperty Casualty11113
ArkansasLife Health11114ArkansasLife Health
View 5 Replies
View Related
Oct 7, 2006
Sheet 2 is the problem.
I need to match cells e3:h3 whenver the dates change in in cells c5:c9 and then put there result of cells b5:b9 into the corresponding cells of e5:h9.
Basically what I'm saying is that I want to keep a track of all previous pay amounts from each pay period. So when the next period changes the date it also copies the new pay amount to the corresponding date of the previous pay section.
I have included a sample.
View 9 Replies
View Related
Nov 21, 2012
I am using this formula but I don't know how to get it to populate more than one cell.
Here is the formula:
=INDEX(APPROVED!$A$3:$A$1000,MATCH($F$4&$H$1,APPROVED!$D$3:$D$1000&APPROVED!$C$3:$C$1000,0))
It has multiple matches but I can only see the first match in the cell.
View 1 Replies
View Related
Dec 2, 2013
I would like to extract Bill cost from "rule" sheet and insert it to "data" sheet using index-match functions. My problem are multiple criteria and multiple matches.
The criteria are Column: Cost type, Power, Penalty Category (The logic goes like AND function).
And I want it to extract "Bills" value from "rule" sheet.
There are multiple matches in all columns (Cost type, Power, Penalty Category) which cause wrong extraction.
How do i solve this problem with index-match function?
View 3 Replies
View Related
Oct 29, 2008
I need to find all rows that have columns that match in all 3 of the columns.
I then need to delete all but the last row in each "group" of rows.
like:
1 q w e
2 q w r
3 q w r
4 q w r
Delete rows 2+3
View 9 Replies
View Related
Dec 6, 2006
I'm trying to create a formula that takes a date in a cell (A1) and compares it with the now() function. If the date in A1 matches the now function then i want to return a 5 and set the colour to light blue (for instance), then if the date entered is a day before now the return a 4 and set the colour to green.
=IF(A1 = NOW(), 5)...... but this just returns 'FALSE' even though the date entered and the NOW() function are the same.
View 4 Replies
View Related
Jun 11, 2014
Here is what I have - Column A has dates that correspond to a given "event", Column B has the hour at which such event occurred, and Column c has the duration of the event in hours.
I want to be able to create a giant sheet where column A has all the dates of a given month, Column B has the hours of the day, and Column C has a 1 or 0 trigger indicating whether an event occurred in that hourly period.
Capture.PNG
As you can tell, some complications are as follows:
1. there can be multiple events that occur in the same date
2. there can be events in the same date that occur basically at the same hourly period
3. there are some events that go beyond a given date into the next day.
What kind of function would I need to create the 1/0 trigger?
View 3 Replies
View Related
Jan 7, 2010
is there any way that i can match the date in a data and apply a color if match?
sample: column a2: 11/5/09 $15.00
column b2: 11/5/09 house $15.00 -> then the column B2 must be filled with color because they have the same date with the column a2.
View 4 Replies
View Related
Feb 23, 2009
I am having a problem with locating a certain date. What I am trying to do is scan down a list of dates and have returned the row is is in. I have been using the MATCH function and it was fine except when there is no MATCH. In the case where there is no match I would like it to select the next cell.
I am currently using this =MATCH(Search!G5,Data!F1:F10005,0) where Search!G5 is the date I want to find Data!F1:F10005 is the set of dates.
View 3 Replies
View Related
Nov 1, 2011
I am creating a userform to enter data in to a spreadsheet. The userform is set out as follows
1.Text Box 1 (Reg)
2.Text Box2 (Token Number)
3.Option Button1
4.Option Button2
5.Command Button 1 (OK)
6.Command Button 2 (Cancel)
Option button one is set to default as True,
1.I enter information in to Text Box 1 (e.g. Test1), Option button 1 is True, click OK, the Text Box1 data + the time and date (Now() ) is entered in to the first blank row, (date = column A, Reg = column D & time = column F)
Text boxe1 is cleared of data.
Userform stays open.
part 1 I have working
2.I enter information in to Text Box 1(Test1) then Text Box 2 (1), Option button 2 is True, click OK, the data in Text Box1(Test1) is matched with the enters in column D (Test1). If this is a match then the data from Text Box2 is entered in to the same row as Test1 in the following order (token Number = column E & time = column G) Text Box 1 (Test1) is not required as it is already in column D
Text boxes1 & TextBox2 are cleared of data.
When I run the userform again all of the above is entered in to the next blank row. There will be a time delay between the first run and the second hence the need to match column D.
View 6 Replies
View Related
Jul 7, 2013
I have two sets of data, first set contains records from a timesheeting system, including:
Name Date
Where each name will be repeated mulitple times each day worked.
Second set of data relates to the invoices for the same resources, and includes:
Name Invoice No Date From Date To
And may include multiple invoices for the same resource.
What I'm trying to do is determine for each record in the timesheeting system (adding a column next to this data) what is the associated invoice number? A simple VLOOKUP returns the first match it finds, however if there are multiple invoices for the same person, I want to return the correct number based on the date fields. I don't want to count the number of invoices, I want to return the corresponding invoice number that relates to the day.
View 5 Replies
View Related
Dec 7, 2012
I need to get dates from one file to another. They can be matched on ID.
So if the ID for a record from FileA matches the ID for a record in FileB, return the date from the date from the same row in FileA to FileB. I would just sort and copy them over, but FileA has more records than FileB. All distinct IDs share the same date.
View 3 Replies
View Related
Feb 24, 2014
Everyday I receive products that could be categorised into one of the five categories A, B, C, D and E. I enter these on a sheet (SHEET 1) along with an expected date of shipment, which could be any date between now and 12 months later.
In another sheet (SHEET 2), I have months in column A and each product type for each month in column B.
What I want is to write a formula in SHEET 2 so that every time an entry is made in SHEET 1, it automatically adds 1 to the respective cell in SHEET 2. For example, I received 10 products today (5*A, 2*B, 1*C, 1*D and 1*E). As soon as i made entry in SHEET 1, I wanted respective numbers to appear in SHEET 2.
View 4 Replies
View Related
May 28, 2014
I have this great macro but it was used to copy row that matched TEXT value.
I have other similar need but I want it to look for a date value instead of text.
My problem is problebly this: If (level = "")
But cant figure out what to replace it with
[Code] .........
View 6 Replies
View Related
Jan 18, 2012
Selection Date18/01/2012ItemPPPrice70.00
View 3 Replies
View Related
May 19, 2013
I have 4 columns A B C D
A and C have dates , B and D have values
Column A has dates listed verticaly from 7-1-1954 to present
Column C has dates lited verticaly from 7-1-1954 to present but without weekends and holidays
I want to know if there is a way to match the dates and values from columns C and D to the columns A and B, with the objective of eleminating the extra cells that contain different dates?! I need to compare the data day by day and the additional weekends/holiday data makes it not line up!?
7/5/1956
2.75
7/5/1956
47.8
[Code]...
View 3 Replies
View Related
May 23, 2013
is there any formula that i can use that will provide me with the most current termination date of a specific driver
i was thinking something along the lines of
=IFERROR(INDEX(I:I,MATCH(L2,MAX(H:H,0))),"-")
this obviously doesnt work, how to get where i need to be!?!
View 7 Replies
View Related
Dec 28, 2006
B2 contains CITYNAME
A2 contains The name of the TAB the data relates too (although i cant find anyway of using this cell within a formula to read the relevant tab, maybe you know of a way)
This is the working code i currently have in cell B3, this is what i need to enhance.
=SUM(INDEX(ABCD!$1:$65536,0,MATCH(B2,ABCD!$1:$1,)))
This looks at the tab ABCD and sums all the values it finds in the column that matches the name supplied in cell B2 on Sheet1
Tab ABCD has dates within coulmn A, and Values in Column B, first row from Column B to Column Z contains the CITYNAME (coumn Z may become longer so this needs to be able to cope with that too.
What i would like to do is add into this an extra requirement, there must be a start and end date. I still need to use the match function as B2 changes using a drop down validation (containing CITYNAMES, these represent the columns within the tab ABCD
E1 Contains the STARTDATE
H1 Contains the ENDDATE
This is the closest I get, changing the start and end date does not give the expected result...
=SUM(--(ABCD!$1:$65536>=E1),--(ABCD!$1:$65536
View 9 Replies
View Related
Jul 29, 2009
I have the following tables and would like to return the red cells via formula
MinContract psuedo
contractid WHERE Table1.'MinDateShip' between
Table2.'MinDateContract' AND Table2.'MaxDateContract'
AND Table1.ID = Table2.ID
AND Table1.ReportTypeDescription = Table2.ReportTypeDescription
Table1
ABCDEF1IDReportTypeDescriptionMinDateShipMaxDateShipMinContractMaxContract2TEST1OE02/28/0505/31/05n/a7773TEST1OE11/30/0501/15/068568564TEST2OE12/31/0412/31/04123123
Table2
ABCDE12IDReportTypeDescriptionMinDateContractMaxDateContractContractID13TEST1OE02/28/0302/28/0455514TEST1OD03/01/0505/31/0577715TEST1OE03/01/0505/31/0577716TEST1OD05/31/0505/31/0699917TEST1OE12/01/0507/01/0785618TEST2OD02/28/0302/28/0498719TEST2OE03/01/0505/31/0545320TEST2OE05/31/0505/31/0645521TEST2OD11/30/0405/31/0545622TEST2OE11/30/0405/31/05123
View 9 Replies
View Related
Jun 17, 2009
I have used index/match before to match and extract data, but I can't seem to wrap my head around this one. It's probably best explained by looking at the attached workbook. Basically, I want formulas in 'John Doe!B' and 'Jane Doe!B' to look up the Name and Date values in column A on the 'Data' sheet, and then return the time value that is on the same row but in the 'Data!B' column.
I then want 'John Doe!C' and 'Jane Doe!C' to return the time that is two rows below the time shown in 'John Doe!B' - I've gone and manually entered and bolded the values in the workbook to show what I eventually want the formula to do. The data on the 'Data' tab will be extracted monthly into excel from tracking software my company uses, but there will always be a minimum of 3 rows of time for each date. As you can see in the example workbook, some dates will be omitted for some people, and while there will always be 3 minimum rows there will often be many more time entry rows for different dates as well. However, I'm only concerned with the 1st row and 3rd row for each date.
I was originally thinking a vlookup for 'John Doe!B' and then a hlookup for 'John Doe!C' that references the value in B might work, but I'm getting stuck on how to ensure the formula realizes the differences between the data and dates for John Doe and Jane Doe.
View 3 Replies
View Related
Dec 7, 2006
In my excel sheet, I have date values stored in cells from D7 ti IV7. in cells C8 to C100, I have a data validation which selects the values from cells D7 to IV7. When user selects a particular date in coumns C8 from the list, using my code, I am trying to search for the value stored in cell C8 within the range D7:IV7 using function Application.Match
While running the code by clicking on "Distribute Budgeted Efforts" command button, I am getting an error "Type Mismatch" and error is coming from code line number 27 which is "lInitial = Application.Match(lInitialdate, Range("D7:IV7"), 0) - 1"
My code is as below
Private Sub cmdEffortDistribute_Click()
Dim lCount As Single
Dim lStartDate As Date
Dim lCounter As Single
Dim lBudget As Single
Dim lInitial As String
Dim lInitialdate As String
Dim lInitialWeek As String
I am also attaching excel sheet which I am using here for reference.
View 5 Replies
View Related
Apr 26, 2007
I can't understand why this macro no longer works. The macro runs without error but never proceeds because it doesn't think the dates ever match when infact they do. Here is the part of the macro not working.
Sub Equity_Dividend()
Dim wb As Workbook
Set wb = ThisWorkbook
For Each Sh In ActiveWorkbook.Worksheets( Array("G&I", "Growth"))
For Each ce In Sh. Range("a5:a" & Sh.Range("a65536").End(xlUp).Row)
If Not IsEmpty(ce) And Not (IsError(ce.Offset(0, 50))) Then
If Date = ce.Offset(0, 50) Then
'here is the meat of the macro it never gets to ...
View 9 Replies
View Related
May 12, 2014
I am trying to look & match key values from 2 areas of one table with two areas of another table; in turn, it'd return one value based on the lookup table...
Attached worksheet : Test booklet.xlsx
View 4 Replies
View Related