Finding Recent And Previous Dates Based On Multiple Criteria?
Apr 29, 2014
i have with noting the most recent date of a test and also the one prior to the most recent dependant on location and test type. I require most recent and previous test info to enable a comparrison to be made. All other dates are not required but need to be kept as historical data and can't be deleted.
Is there anyway i could get column E to auto populate and amend itself as more test dates and locations are added to the list?
View 1 Replies
ADVERTISEMENT
May 21, 2007
I am trying to find the top two values per group based on multiple criteria. The list I'm working with is not sorted and would be better for it to not have to be sorted as on-the-fly sorts will likely often occur from the raw data and I wouldn't want that to mess up the results I'm looking for here.
As an Example, here's what I'm trying to do:
Make Model Rating
Ford Bronco 64
Chevy Corvette 94
Dodge Intrepid 83
Chevy Chevette 34
Dodge Viper 72
Ford Escape 21
Ford Expidition 53
Chevy Impala 67
Ford Fairmont 11
Dodge Dart 33
View 9 Replies
View Related
Jun 10, 2013
I have a spreadsheet of prices (raw data), and I need to pull out both the most recent price for each item for a specific customer code for each item, as well as the second most recent price, (essentially the old price), for that customer code for each item.
I have columns for ITEM (code), PTC (Customer Code), PRICE, and EFF (Effective Date). For each item code, there are multiple prices for multiple customers, such as in the linked workbook.
I am currently using the formula, =INDEX(PRICE,MATCH(1,(F2=ITEM)*(G2=PTC),0)) to pull out the last price. This works because the last price is on top based on sorting. What I need is a formula that pulls out the next oldest price for that item/ptc combo. I tried using the SMALL function to no success.
[URL]
View 2 Replies
View Related
Jan 2, 2013
This formula allows me to find the lowest value in column U where column N contains the text "NO".
{=MIN(IF($N$2:$N$10000="NO",$U$2:$U$10000))}
I want to add another condition so that the formula only returns the lowest value in column U where (i) column N contains the text "NO" and also (ii) column F contains the text "YES".
View 11 Replies
View Related
May 12, 2012
In a folder called "C:myworkbooks" I have books like
John Smith_001.xls
John Smith_002.xls
John Smith_003.xls
Using only "John Smith" as an initial search parameter in need to find and open the most recently created book.
View 7 Replies
View Related
Dec 31, 2006
I have a very large Excel spreadsheet that is generated through an Oracle application at least once a month at work. I would like some code (either a worksheet function or a VBA) to check and report the following. If the frequency in column C is "Monthly" or "Weekly" just go to the next row. If the frequency in column C is "Annual", add 183 days to the date in column B; if it is "Semi-Annual", add 92 days to date in column B; if it is "Quarterly" , add 46 days to the date in column B; if the frequency in column C is "2-Year", add 365 days to the date in column B. Once the check has been made; I need the new total or date that was calculated checked against the date the report is being run (system clock date). If the date or total days is less than today, a new worksheet, titled "Late" needs to contain that row of data. If the date or total days is greater than today's date, just go on to the next row. I've attached an example worksheet.
View 9 Replies
View Related
Feb 27, 2013
I have to find the average of a set of data with a lot of different criteria. I think an array formula would work wonderfully but I just cannot seem to get it.
My example: I have a large set of data that I must get multiple averages from that fall within differing sets of criteria. For my example we'll just say that I have to find the average of the number column that occurs on or after Jan-12 (January 2012) and is type "p1" or one that has no date and is type "p1" and to not average numbers that are equal to 0. So logically it would look like this: If date
View 2 Replies
View Related
Feb 18, 2014
I have data, which has an oil well that has had sections of its pipe perforated.
This has all happened on different days throughout the wells life.
I need an equation that says TRUE if the Perforation is the Most Recent for that well.
But I also need it to say True at different Top and Bottom Depth intervals throughout a specific well.
(for example: the last row in this data. Says TRUE even though there are more recent dates in this well.
This needs to say true because this is the most recent perforation in the top and bottom depth interval 9526 to 9536.
The other most recent perforations happened in shallower intervals, ie. 9456 to 9480.
So I need something in the equation that will say true to the most recent start date, if that top and bottom depth interval has not already been selected as true.
I need to have a column that says True IF the start date is the newest (most recent) for each individual well.
Easy enough.. But. I also need this to say True multiple times at different dates for each well.
For example below. it says true on 7/22/2001 three times and then True again on the last row on 10/14/1992.
It has to say true there because this is the most recent perforation that Top_Depth and Bottom_Depth interval.
WELLNAME
TOP_DEPTH
BOTTOM_DEPTH
START DATE
IF most recent Perf
RA-0001
9380
9395
10/14/1992
FALSE
[Code] ......
View 9 Replies
View Related
Mar 7, 2012
I need to be able to find a value in another sheet based on 2 criteria
The criteria is a number from a column on my first sheet where the results will reside, the second criteria is based on a fiscal year (calculated based on the year of the current year).
The formula I use is as follow:
=LOOKUP(BA2,Budgets!B:B,Budgets!I:I)
and this is what I use to get the column:
=+INDEX(Budgets!B11:B200,MATCH(Budgets!B11:B200,Budgets!B11:AB200,0),3) getting #N/A here
and I use this too:
=MATCH(IF(MONTH(TODAY()) < 7,YEAR(TODAY()),YEAR(TODAY())+1),Budgets!E:Q) getting #N/A here too
If i keep it like that no problems but I will need to change the criteria 2 every new fiscal year.....
What I need is to replace the criteria 2 with something that will find the proper column wher the data is the fiscal year is listed on E11:Q11..
I have tried index and match and quite frankly, I never made it work, always getting an error.
View 9 Replies
View Related
Jul 30, 2008
I have a spreadsheet which lists letters issued, the date issued and the potcode. I need a formula that counts, on a weekly basis, the number of letters issued to postcodes in Sutton and Bexley.
I have a count sheet set up on one worksheet and the list of postcodes applicable to each area are listed on another worksheet. I have been playing around with sumif, sumproduct etc, but these don't seem to work as I am pointing the formula to a list of postcodes and not an individual postcode. For example, the formula for one week needs to tell me, the number of letters issed to Sutton between 26/07/08 - 01/08/08. This is what I tried -
=SUMPRODUCT(('MU63 NC'!$F$2:$F$10>=COUNT!B$2)*('MU63 NC'!$F$2:$F$10<=COUNT!B$3)*('MU63 NC'!$D$2:$D$10=Sutton!$A$2:$A$50))
But I get #N/A - if I change the last refernce to a specific cell instead of a range it works, but this will make the process very lengthy as there are lots of postcodes!
View 5 Replies
View Related
Jul 26, 2012
I am trying to average PT ScoreS from multiple groups based off of different criteria. Please see below tables:
KEY:
MS = Class CO = Company PLT = Platoon SQD = Squad
A/1 = Alpha Company, 1st Platoon (Example of Company and Platoon PT Score Average)
A/1/2 = Alpha Comapny, 1st Platoon, 1st Squad (Example of Company, Platoon and Squad PT Score Average)
F14 is the average of all PT Scores
A
B
C
D
E
F
1
NAME
MS
CO
PLT
SQD
PT SCORE
[Code] ....
I need to find the equations of different groups of PT Scores. I want the averages/equations to go below the different groups.
A
B
C
D
E
F
G
H
I
J
K
1
MS 1
MS 2
A
A/1
A/2
A/1/1
A/1/2
A/1/3
A/2/1
A/2/2
A/2/3
[Code] .......
View 2 Replies
View Related
Jan 20, 2010
I have an employee summary sheet, and another sheet where data will be entered by date.
On the summary sheet I want to gather only the last 3 dates that were entered.
Attached is an example.
View 10 Replies
View Related
Jul 24, 2014
I have this column, and consists of a collection of dates. I want:
1 - change the format of the dates of "03.01.2013" to "01/03/2013"
2 - arranging dates from oldest to newest and then sort them without repeating
01.03.2013
01.04.2014
01.06.2014
01.07.2014
[Code] ...........
View 7 Replies
View Related
Sep 2, 2009
What I am attempting to do is find the MIN value in Column C where values in Column A are equal.
The data would look like this
A B C D (D:D is where the "MIN" Formula will be)
Scope1 NameA $100
Scope1 NameB $145
Scope1 NameC $115 $100 (I want the min value to show up here)
- (this would trigger a break between scopes, and provide a conditional format separator)
Scope2 NameE $450
Scope2 NameG $345
Scope2 NameX $415 $345
-
So every time I put a "-" I would like the MIN formula to trigger in (Row#-1,D)
View 5 Replies
View Related
Apr 24, 2014
I have 2 columns of data in Excel which I have brought a small section of it below. As can be seen the values in the left column have a large spike (difference of more than 10) at certain points (in this case at 34). I wan to find the average of the numbers in the right column but only till the point where the large spike happens (in this case the average of the first 5 numbers). I've tried AVERAGEIF but it's not what I want. I want this to repeat for the entire column and give me the averages of each of these groups.
10
32.4
11
38.6
12.5
23
[Code] .........
View 2 Replies
View Related
Jun 15, 2006
I have a main soure data which consist of row & column information. What i want to do is search the data from the source data into my result data as per the attachment file. Example: I want to information of Jan & banana from the main source file to appear in the XXXX
Result data(criteria base on Month & type)
JanApril
BananaXXXX
Apple
Orange
View 4 Replies
View Related
Jun 16, 2014
Any quick way to extract data from a table. I need to extract a value from a column that meets criteria from two different columns. I thought I could get this to work with vlookup, but have had no success. Sample data below in table 1 and I would like to get my data into table 2.
elevation
type
grade
percent
weight
5000
5000
5000
5020
[Code] ..........
View 9 Replies
View Related
Jun 25, 2008
I've read the how to for finding the nth occurrence using index/match but the example given does not really help solve my issue. The file I've attached is a condensed version of the actual file, which has more columns but I deleted all but the necessary ones for clarity. What I am trying to accomplish:
On sheet1 there are three columns, Business, Amount, and Closing Date.
Not all the business names have a closing date and the spreadsheet is sorted alphabetically by business name, so sorting by closing date, and using the method used in the topic " find the nth occurrence in excel", is not an option.
On sheet2, I would like to see ALL the business names that have a closing date in the respective month, as opposed to just the first. Then to the right of the business names I have the sum of all the amounts in that month, but I figured out how to get that one already.
View 2 Replies
View Related
May 15, 2014
The basis of my needs is to be able to find in a list of names, a duplicate name from previous list and then grab and populate a number from that row. Here is an example
A B C D
Doe,Jane $1000 $250 $750
Smith,John $700 $100 $600
Wilson,Alan $1500 $1500 $0
I update the list, some names are added and others taken away. When I add the new list, column C of every name is $0. I want the formula to find names from the previous list and populate Column C with the value of Column C + D of the corresponding name. Also I have a separate formula that simply is D=(B-C) The result of what I would want this to look like is this...
A B C D
Doe,Jane $1200 $1000 $200
Doe,John $500 $0 $500
Smith,John $1000 $700 $300
As you can see there was a new name added to the list (Doe,John) and a name taken away from the list (Wilson,Alan). Column C is the only Column that I am wanting to auto populate. The Value in Column B is a variable that is managed by another program so you don't have to worry about that.
Keep in mind there will be multiple instances of this list and the same name will occur more than once in the spreadsheet. There are not duplicate names that are different customers though as my other program takes care of that. eg. "Doe,John" and "Doe,John #2". I only want it to grab the information from the previous list not ones previous to that list(if that makes sense) third example...
A B C D
Doe,Jane $1500 $1200 $300
Doe,John $500 $500 $0
Miller,Joe $700 $0 $700
Smith, John $1300 $1000 $300
My current method of accomplishing this is to select the previous list and print it. I then go down the new list and cross reference it with the old one keying in the values by hand. This would be a huge time saver as the list is 250 names and growing. I hope I have given enough information but undoubtedly I have not. So feel free to ask me any questions and for more details. I did not want to go to in depth for fear of my post being to long.
View 14 Replies
View Related
Jan 22, 2009
In column I, there is a list of dates - month and day only
In column J there is a list of dates, month,day, and year.
--I----------------J
1/14----------1/14/2009
1/14----------1/15/2009
1/14----------1/16/2009
1/15----------1/17/2009
in column K, I am trying to setup a Vlookup statement that states if the month and day in column I = the month and day in column J then return the value in column J.
I have the following formula, but it just returns N/A.
View 6 Replies
View Related
Oct 26, 2012
I have a column CF which has to calculate the due date based on start date in column X and end date in column Y.
This is achievable however; I have dropdown columns in CE which has below option.
Below is the requirement.
If user select "Should Pay" from drop down in column CE, then in column CF the due date has to be Start date + 7 days
If user selects "Not to Pay" from drop down in column CE, then in column CF the due date has to be N/A or Blank
If user selects "Has Time to Pay" from drop down in column CE, then in column CF the due date has to be Start date + 9 days
If user selects "Misc" from drop down in column CE, then in column CF the due date has to be (End date - Start Date)/8+7
if this can be attainable either via formula or VBA.
View 4 Replies
View Related
Jul 9, 2013
I have a list of text values in column X. I need to come up with a formula in column Y.
X
Y (RESULTS OF REQUIRED FORMULA)
Comment
1
HAT
1
First HAT in column
2
[Code]....
I can't play about with the natural order of the spreadsheet, so there's no chance I can re-sort the data into column X and (easily) identify the duplicates that way. So, it could be that the duplicated value(s) will appear in any cell within that column.
I need to identify whether the item is a duplicate in the unsorted list. Ideally, the first entry of a set of duplicates will be given 1, then the subsequent duplicates themselves given a 0 (zero). It's to subsequently do some counts on.
I guess that as long as the one of the entries in the duplicates is marked with a 1, while the others are 0 (zero), that's all that's important.
View 3 Replies
View Related
Oct 31, 2013
We want to count the numbers of days between two dates however of 2nd date is null...count till todays date
If ActivityType is "E" and DDate is null Count number of days from EDate till Today().
If ActivityType is "D", Count number of days between EDate and DDate.
Column F is the desired result, Number are asuming todays date is 10/31/2013
[Code]....
View 5 Replies
View Related
Dec 4, 2013
I need a spreadsheet to record the number of calls taken by my staff YTD, and have it laid out as follows:
Column A Column B Column C
1 Date Calls Taken Increase
2 01/01 10
3 02/01 13 3
4 03/01 19 6
-19
etc.
The "Increase" is a simple =B4-B3 etc. But, there is a problem...
Say for example a member of staff is ill one day and therefore doesn't take any calls, I end up with a 'blank' cell in column 'B'...
Column A Column B Column C
1 Date Calls Taken Increase
2 01/01 10
3 02/01 13 3
4 03/01 19 6
5 04/01 -19
6 05/01 25 25
My problem is the increase in calls from 03/01 to 05/01 YTD ISN'T 25, it's 6.
Is there a formulae I can use that works out the difference between the latest number of calls taken and the last number of calls taken (excluding the 'blank' cells)?
View 1 Replies
View Related
Mar 23, 2009
I want to populate separate tabs from data in a master table based upon multiple conditions shown in the separate tabs. Each tab is a summary of a particular engineer's shifts over multiple weeks and their results on various projects. Each summary tab has the engineer's name and the number of the week (the two conditions). Example: On Aaron's tab, I want to show a summary of Aaron's work and his results over a period of several months, as well as who is working the other shift on that day for those projects. So I need to get the following information for Aaron's tab from the master table:
Week 1, Aaron is working the morning shift and Zeb is working the afternoon shift,
Week 2, Bill is working the morning shift and Aaron is working the afternoon shift
Week 3, Aaron is working the morning shift and John is working the afternoon shift
etc etc
I also need to know how much work each engineer pairing has achieved in a particular week on a particular project. The engineer working in each shift may vary from week to week, as will their results. There is no formula for when a particular engineer will be working either morning or afternoon or whom they will be working with.
View 5 Replies
View Related
Feb 28, 2008
I'm trying to do a sumif based a range of dates but keep getting a "-" for the result.
In the past when using dates I've had to value the dates in order for it to work. I can't seem to figure out the formula this time around - any thoughts? See attached for sample - my 'broken' formula is in cell C29.
View 4 Replies
View Related
Feb 5, 2008
I have attached a small copy of the worksheet.
I need to count when the following set of 3 criteria criteria are met:
1: The Assessor Initials = DS
2: the month = Mar
3: Check 4 Ranges = columns I, K, M, O
I've tried using sumproduct (sucessful for other data) but not able to get it to work with dates.
View 9 Replies
View Related
Jul 18, 2012
I am trying to run an array formula to match two dates on two seperate sheets as close together based on another cell. Below is what I currently have on some made up values. I am entering it as an array and ideally I would like it to run down the cells changing the reference A2 depending on which cell it is then to then enter this into a macro.
The formula I am currently using is:
=INDEX(Trees!B$2:B$75,MATCH(MIN(INDEX(ABS(IF(A2=Trees!A$2:A$75,(Trees!B$2:B$75-B2))),0)),INDEX(ABS(IF(A2=Trees!A$2:A$75,(Trees!B$2:B$75-B2))),0),0))
Sheet 1 (Named: Main)
Number
Date
Formula
1
15/06/2012
[Code] ....
Sheet 2 (Named: Trees)
Number
Date
1
05/06/2012
[Code] .....
View 2 Replies
View Related
Mar 28, 2014
I have a data set which has 6 columns (and lots of rows). Every row is different but I want to aggregate them based on 4 fields and then find the average of the numerical column for the results. I basically want to Group based on 4 fields and find the average of the 5th field.
My initial approach was to introduce a column which is a combination of the 4 fields I want to group by, simply in the Excel file (=A2&B2&C2&D2) and then find duplicates of that. I have a solution for this in VBA but when importing new data sets in this method is very slow, so I want to be able to do the whole thing in VBA.
View 9 Replies
View Related
May 14, 2014
I have a data table with dates and marks by 0 and 1.
I need a formula, if a date selected from data table, to find first next or previous matched date.
At attached table you can see all detail and explanation.
match dates.xlsx
View 5 Replies
View Related
