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.
I'm trying to return a distinct list of rows that filter based on the latest date and largest quantity for each distinct AccountID and ProductID combination.
I tried some variant of the =max function, but I need two filters.
The purpose of this is to create a data set of all company accounts with the most recent number of products used to upload to a database.
The simplified and original data set is as follows:
I am trying to use a function to search for a particluar value (e.g., ssn) in one column and return the "InDate" in another column. I thought this would be easy, but I'm having considerable trouble. When I do manage to get a value returned, it's the wrong value. I need to return the most recent "InDate" for the ssn that I search for. The button on the " Lookup" sheet is to sort the InDate column descending so that the Excel function will start with the most recent date when it searches. I've tried Lookup and and just about every other similar function with no luck. I originally wanted to search for a ssn and return the information for the last 5-10 most recent visits, but gave up on that long ago.
In the attached workbook - the stock Inventory is increased, every second day, by the value shown in cells of column A. Column B displays the date of the update. I'm looking for a Formula (might be an Array Formula) that will return the latest date before the stock turns to be greater than the value in cell D3. I managed to solve it, in cell F3, but with the help column C.
I have actual date , then I have names of customer and I have product reference. And what I want to find out is the latest price COLUMN D for which this specific product was sold to specific customer (Data in rows E,F,G,H are from another tab). So basically I want is to find out that latest price of product x27 sold to customer A on 15.7.2013 was 70 because on 12.7.2013 we have sold it to customer A for 70.
I would like a lookup that takes multiple criteria and that is not an array formula! Unfortunately I decided to use array formulae and my spreadsheet went to over 45mb!! Not good. I've searched the forum for an answer to my questions but couldn't find any! I've attached a spreadsheet as an example. The examples I am using have {Sum(IF)} formulae in it (array) and I would like to change those to others that will not increase the file size so much and will not take too long to calculate.
Basically, I would like a lookup that will return me the Amount Paid and Full Cost based on the person's name and the date. the data and the results table are both on separate sheets. It would be nice to bring that file's size back down to less than 4mb!!
I am working on a large data file (leasing file), that has many duplicates. The names on the file are duplicated due to the various variable costs associated with leasing. I need to remove the duplicates names based on the latest contract end date.
I am developing Spreadsheet that you will enter a list of parts that you need. it will have many columns but only 4 are vital to the check. this will be generated for each project.
With each part i want to then look up in another file that is a list of all the possible parts you can have with there cost. this file will be standard for everything and it will be updated from time to time.
now not all the criteria to look up will be exact matches.
here is an example
code temp/pres rating diameter material GLV 4500 15 16Mo3
in the other file we will have multipu entries for GLV with different values.
For Code i need it to match (there are lots of codes even including variations like GLV.) For Temp/pres rating i need it to be equal or higher to the value For diameter i need a match for material i need a match.
so as you can see from the list above there are 2 options. so then i want it to return the cheapest.
that is step one.
step two is to repeat it for multiple list of parts.
I need a formula to do a partial text match on column B to find all rows that contain "825-CL-A", then sum column C for all applicable rows with the latest date. In this example the result should be "4.25 + 6.50 = 10.75". I'm using Excel 2003 for this project.
A B C 7/1/2012 0:00825-CL-A-41091-REG4.00 7/1/2012 0:00825-CL-A-41091-REG6.25 7/1/2013 0:00825-CL-A-41456-REG4.25 7/1/2013 0:00825-CL-A-41456-REG6.50 1/1/2014 0:00825-CL-A-41640-REG4.25 1/1/2014 0:00825-CL-A-41640-REG6.50 3/1/2014 0:00825-CL-E-41699-REG3.00 3/1/2014 0:00825-CL-E-41699-REG4.00
I am having rows of data, that i will be updating from time to time. I want excel to move the latest updated rows, in any column if updated, to move to the top, to easiy know that i updated those records. It should be that when i updated more rows than one, then the first updated cell would be in lower, in order, than the latest updated cells. I do not want any cumbersome vba. I want in formula or in conditional formatting. The row no may be total not limited to some rows.
Because, you naturally would have updated the 200 th record and would have saved. It saved as it is, so when you next opens it it is there, but how can i know that that is the last row of data i edited.
I'm setting up a spreadsheet to manage recurring tasks that fall within a given date range.
I have a named variable (theDate) which pulls the list of matching tasks that fall on a single day, but some fall on multiple days, so I need to somehow extract the next valid date the task will come due.
I've been playing with formulas until my brain got scrambled. I suspect I may not have got enough sleep over Christmas and there's something staring me in the face I'll kick myself over.
Column Headings are: Description (Col B), [various notes C:E], M, T, W, T, F, S, S, (Cols F:L, marked with x when relevant), StartDate (M), EndDate (N) and my nemesis, NextDate.
That formula should check that 'theDate' falls within the range, if so, lookup whether the appropriate weekday is null or not, and if it's not, then the current date comes back. If it is, then I need it to find the next weekday (by that I don't mean M-F, I mean any of the 7) when it will fall and return that date. I do have the weekday number in F2 if needed, and I'm using a weekday return type of 2 (Mon=1).
'theDate' will usually be in the future, but not always. It needs to not fall over if it's past.
I'd like to do this via formula ideally, since the SOE I'm on keeps losing my Analysis Toolpak, but if I have to code, I have to code.
I'm on excel 2010 and I have a small group excel files I open everyday. Most of the files are static in name and location. I've got a macro created to open those files, which works fine with workbooks.open and the file path.
There are two report files I want to incorporate into my macro of workbooks to open. The files are created weekly and the files names have the following format: "Report Name (YYYY-MM-DD).xlsm". I don't want to use the file's last modified date because older files may get edited after the more recent ones are created. The files are also not always created on the same day, so the solution needs to be flexible enough to not refer to a specific day of the week or anything.
Macro open an excel file based on the latest date found in filename.
How do I return an offset value within a named range using a formula? For example, my range "RngTest" is from C3:J43. If a value of "Product123" is found within the range, (lets say its found in cells C3; E5 and E7), I need the accumulative values in the cells 'below' (in cells D3; F5 and F7) added ( SUM) and returned to cell L3. Therefore if Cells D3; F5; and F7 have the respective values of 3; 4 and 5, the value of cell L3 should be 12. Also, do I need to express "RngTest" or "C3:J43" or does it matter?
[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 have the attached spreadsheet and I need to find the date in the table that is less than the specified date in E3 and is also the animal specified in cell D3. So the formula should bring back the result 18/03/2013 (line 4) as that is the closest date before the given date and it is also a dog.
I know I probably need an array formula and some max ifs, I have tried a few combos but I can't get it going. I don't want to be able to do any sorting of the table I kind of want to keep that how it is.
Second Tab Account Name Account Number Account Ship Location Number
On the first sheet each account name and number only appears once.
On the second sheet there may be multiple entries for each Account Name and Number.
On the first tab for each row I want to compare the Account Number column to the Account Number column on the second tab. Where I have a match I need to count how many unique Account Ship Location Numbers correspond.
I have an items list on page 2. On page 1 I want to create a drop down menu in C2 that references the list on page 2. Then when I choose an item, I want the additional information in cells D2 and E2 to come in automatically. The list on page 2 will be expanding daily, and when I expand it I can simply insert a new row so I correctly alphabatize it instead of constantly having to resort it.
With that said.... 1. How do you put in the drop down box that references information on a different sheet?
2. What is the best way to reference the information in my ever expanding situation: Index/Match, Vlookup, Lookup, or something I havent mentioned?
Any way to construct a formula in excel that will look at a reference in one column and find the latest date from the data in an adjacent column for that specific reference?
Below is an exctract from a much larger sheet of the columns in question.
The result in the last column should be 21/05/2014 for anything with D.O.001 in the second column and 15/05/2014 for anything with D.O.002.
Date Decision agreed Disposal Order Latest Decision date for D.O.
I'm trying to create a formula in cell f13 of my attached spreadsheet "Sample 1" that will search the 2nd attached spreadsheet "Sample 2" and return the correct serial number based on both the matching PO # (located in cell E10 on Sample Sheet 1 and in Column 5 on Sample Sheet 2) and Product # (cell A13 on my Sample Sheet 1). My current formula is not returning the correct result and I'm not sure why.
I am trying to use a nested INDEX and MATCH array formula to return the value in column C when matching column A and column B, but with a few more criteria.
The range containing all the data
A B C
1 Cat 1 January 1, 2014 John
[Code] ..........
I am looking for the array formula to return the name of the person in column C who is in Cat 1 after the date in column B.
For example; C7 should return "John" because B7 requests "January 15, 2014", which is after the value in B1 C8 should return "John" because B8 requests "February 15, 2014", which is after the value in B1 C9 should return "Andrew" because B9 requests "August 15, 2014", which is after the value in B4
This brings back "John" as desired in C7, but when copying down the table into C8 and C9 both C8 and C9 return Andrew.
I guess this is due to my ">=" condition in the Match formula and it is returning "Andrew" because "Andrew" is also after the date requested, but I cannot for the life of me work out how to get it to work.
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 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.....
I have a table with different values on different dates. To get the newest value on the lastest date I'm using this formula; =INDEX(F5:K5;MATCH(9,99999999999999E+160;F5:K5)).
However, how I can get the second latest value? In this example I want a formula to to return the value previous price;
Latest observation Latest observation date Previous price previous price change date 01.08.2012 02.08.2012 03.08.2012 04.08.2012 05.08.2012 06.08.2012
I am in need of a vlookup formula that will return a value that corresponds to the latest day in a month. Example:
Column A had dates in format 1/3/2012 ect.... Column B is a $ amount Column C is a date in format Oct-12 ect...
I want column D to be a formula that match up column C's month in column A and return the $ in column B that corresponds to the latest day in the month. IN the example above it would returna value for Oct 31 (if there was one), Oct 30 next and then Oct 29 ect...
So if column A had October dates of: 10/1, 10/4, 10/17 and thats it in Oct then it would show the 10/17 $ in column B.
I'm working on a project with tables. I'm not sure if I can use a function or I have to vba. I want to retrieve the lastest 5 entries for a specific column (column AA), but not blank ones. Then I want to retrieve latest 5 entries for column AC. On a seperate sheet I want the entries to be seen in order and I want it to change as new data is entered into the table. In the end I'll have the latest 5 entries for Internal (Column AA) and then after that the latest 5 entreis for External (Column AC).
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.
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).
I have a list of students that have sat a number of exams on different dates. Can I pick out from the list the last exam for each person. I can sort by id number and then by date but how do I then show the record showing the latest date for each person?
Example Mary 1/2/2012 john 2/3/2012 john 1/3/2012 mary 1/1/2012 mary 4/4/ 2012
I want to select the record showing the latest date for each person. the result would be mary 4/4/2012 John 2/3/2012
