I've attached a quick sample, I have two customer ID's, AB1 and AB2, I want to be able to put a formula into column C that will return the latest date for each customer. So AB1 will show 01/04/2013 and AB2 will show 01/05/2013. I know I can use a MAX formula but I don't know how to connect it to the specific customer ID.
Max Date Query.xlsx
I have a spreadsheet of our Customers rankings with a column with the date that they sent their Report Cards in. I need to pull by Cust# the most current date. I tried using DMAX but I can not get the formula to work. The sheet is not sorted and can not be sorted by Cust#.
=DMAX(A$6:D$886,"Report dated",G$6:G7)
range field criteria
Title row starts at 6
A B C DCust #Marketing LocationCustomerReport dated1034ColmarScientific Systems11-Nov-021032ColmarRaymond4-Feb-031029ColmarPhiladelphia Gear6-Feb-031028ColmarPall4-Apr-031016ColmarGreene Tweed 22-Apr-031032ColmarRaymond6-May-031032ColmarRaymond15-Apr-041012ColmarATK Launch Systems, Inc.27-Apr-041032ColmarRaymond22-Oct-04
I have a "activity log" sheet which records the date in column A, the activity in column C and the name of the customer that was contacted in column D. There may be multiple entries of a customer's name in column D.
In a separate sheet, I want to find the latest date from the "activity log" sheet that a each customer was contacted and the specific action on that date. In this sheet, the client name will be in column A, and I want to record the last contact date for the client in the same row in column J and the type of contact in column K.
I need a formula to find the latest date (Column A) a given value appears in Column B.
Column A is a list of dates.
Column B can contain any of 6 values (For example: A, B, C, D, E, F).
And, to make it even more fun, the system I'm working on doesn't allow macros.
I've got a multi column sheet containing, amonst other headings, "Date", "Transaction" and then a rolling "Balance". I have multiple rows with the same date and what I'm simply trying to do is have a formula at the top of my spreadsheet that finds the very last row that has data entered so that I can then extract & display the balance as at that point.
Date / Transaction / Value / Balance
01-12-09 / Salary / +£1,000 / +£2,600
01-12-09 / Fuel / -£50 / +£2,550
01-12-09 / Gift / -£100 / +£2,450
03-12-09 / Food / -£30 / +£2,420
03-12-09 / Interest / +£2 / +£2,422
In this example I'd want to return row 5 and then do some kind of lookup to retrieve the value £2,422...
I want to create a customer ID for thousands of customers and then be able to find the customer by doing a search for the ID later. I'm even thinking of using a customer phone number for the ID. What function can I use to later find the customer?
View 10 Replies View Relatedi have several styles to handle mentioned under different rows separately.
all the styles have their various raw materials inward date mentioned under different column of their respective rows from column Q to Y
now i need to 1. extract the latest date of any particular raw material which can be in any of column from Q to y columns ACCORDING TO DIFFERENT STYLES IN A Particular row automatically that is AC
I have a sheet (attached, Excel 2003) that I need an assist on. What I want to do is search the 'All Games' worksheet for the ten latest games a specific player has played.
That by its self might not be so hard, but I want to take those 10 rows (once identified) and take the sum of column G and divide by the sum of column F for those rows only.
I already have a concatenate cell in the 'All Games' sheet for each row that I use to as a link of sorts to get data from column F and G onto the 'Spring 2009' sheet. The way I have the concatenate set up, I can manually sort by it and get the 'latest 10' values I need, but I need to get it all into a formula to place on the 'Spring 2009' sheet without the manual sort.
How to find the latest price by each sku in the list, the list contains many skus.
View 13 Replies View RelatedI have a series of rows 14000+ lines strong in an excel sheet.
The series contain caribou location reports from those caribou that have a
collar attached to them.
Along with other information, each row contains three important columns
related to this question. Collar Number, Date and LC (LC = Position Fix
quality or Signal Strength).
I need a formula that will find the best and latest signal strength for each
collar and for each day!. The formula should result in a 0 or 1. (no or yes).
There are about 30 different collars each reporting on certain days. The LC
values are 1 to 7 with 7 being the strongest.
i have 2 dilemmas
**dilemma 1**
i have a date of purchase in column A
i have a customer number in column B
now if i want to search for a date and for it to display the results from columns C,D,E and F (purchased items) i know i use HLOOKUP
but how would i look for a customer and a date at the same time.
**dilemma 2**
i want to be able to put in a customer number and excel to tell me all the dates they purchased items.
currently i use
={IFERROR(INDEX(Table1[DATE],SMALL(IF(Table1[NUM]=$L$3,ROW(Table1[NUM])-MIN(ROW(Table1[NUM]))+1),ROWS($1:1))),"error")}
and i change the ROWS($1:1) to ROWS($1:2) or ROWS($1:3) etc
to bring in that result, but i want it to bring in unique dates, as a customer might ring us back with another purchase on the same date and i dont want it to duplicate the date.
I need to create a list that has one row per paid date per customer on a new worksheet. My source data has one row per customer with the paid dates across 12 month columns. I have many rows in the source data with zero paid on all 12 months and I'd like to filter those out in the process.
This is not a simple Transpose because I want the customer number, and customer name to repeat for every instance of a paid amount.
I have attached a sample file with the current and desired layouts of the data.
I’m getting data from query. My data has fields: Item No, Customer ID & Sales rep.
I need to find out for each Item number which customer put order and who was the sales rep. (Excel 2002). see file attached.
I am looking for a formula to capture the "date" of my first sale to new customers.
A B C D E F G H Date of first sale
1 Co. Name 2/2/2012 6/5/2012 7/6/2012 10/16/2012 1/22/2013 4/17/2013
2 ABC Co. $10 $11 $5 $12 $12 2/2/2012
3 Smith Co. $19 1/22/2013
4 Brown Co. $11 $15 6/5/2012
5 AAA Co. $5 $10 7/6/2012
I have a spreadsheet that contains over 3000 customers. On this there is much data...but what I am interested in is purchase date. The problem I am having is that customers have purchased multiple times and have multiple purchase dates. I was wondering if there was a way to get rid of all but the most recent purchase dates for each customer...thus having only one line per customer as opposed to 5 lines per customer?
In other words: Jon purchased on 1/2/14; 2/4/14 & 5/8/14
Bill purchased on 2/17/14; 3/19/14 & 4/21/14
Tom purchased on 4/2/14; 5/1/14 & 6/5/14
I need to get rid of the oldest dates and keep one line item by most recent purchase date for each customer...so instead of having 300o line spreadsheet I will only have 1000 lines.
I am trying to automatically show the date of the last invoice per the customer number.
I have attached an example.
Find latest delay Position of each number which is in the game
View 9 Replies View RelatedSheet 1 is filled with names(column C) and dates(column X). On the sheet 2 I want the latest date for each names. I tried the below formulas. But it is not working.
=IF(ISBLANK(EWS!$X$2:$X$101)," ",MAX(VLOOKUP(Lookup!$D2,EWS!$E$2:$X$101,20,0)))
= MAX(VLOOKUP(Lookup!$D2,EWS!$E$2:$X$101,20,0))[code]....
I'm needing to finding the latest date, of each unique ID (and Name, Criteria).
In my attachment ('Table'), the "Newest" column with the result of "Yes" is what my calculated result should be.
Any formula here (outside of a Pivot)? Because, I then need to delete all of my "No" results from the list.
I have a data field of about 2000 rows & 30 columns.
These contain records on accidents for 56 offices.
One of the columns contains the date an individual had the accident.
There can be a number of records for each office as there are a number of employees in each office who have had accidents over the last few years.
I need to be able to run a macro or set up filters or formulae to show the row containing the most recent accidentin each office, by office. I also need to display the total number of days since the last accident occurred at the end of each row.
The offices are listed in Column B & the date of the last accident is listed in Column V.
If I have a row full of dates and I want to find the most recent date, how do I do that?
View 2 Replies View Relatedformula that can pull the latest date for a product, if there are multiple dates attached to the product?
For example, in the attached, I have a Customer and Deal Date list, for Customer A, there are 3 dates, but I only want the latest date: 2/14/2014.
I have a spreadsheet which contacts three columns of dates, the problem I have is that there are not dates in every column.
What I would like to do is to have a fourth column which shows the latest date from the three previous columns.
I need a macro that can jump to the nearest current date in column B from anywhere on the current tab. all the dates are in 10/09/2009 format, and the actual date of today might not be in that column. This can be + or -1 one day search from today. After this macro run's the nearest current date will be the active selected cell in column B.
View 9 Replies View RelatedI 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.
View 2 Replies View RelatedI have 2 spreadsheets. One is a promotion calendar that lists the dates that promotions on a certain product runs. The other is a shipment grid of shipments of that product to the customer.
I want VLookup to find the customer and the dates and then bring me back an asterisk in a separate column to show me that that certain week that product was delivered was a promotion week. The problem I have is using Vlookup to lookup 2 things at once (and if they match to the promotion calendar) and return me an asterisk.
Here is my formula now:
=IF(VLOOKUP(J2&" "&L2,'East Data'!M:AU,2,FALSE),"*",0)
J2 is the customer name
L2 is the week
"East Data" is the spreadsheet with all of the promotions and customers.
I have a spreadsheet which I am trying to use to capture customer data. What I want to happen is whenever I enter a new record, the date appears automatically in a Date column ....
View 6 Replies View RelatedLets say I have a wall of text that looks like this, all of it in 1 cell.
[Code] .....
What formula do I use to extract the latest date from this string? In this example, the ideal result will be 02/05/2014 .
I need an excel function to return the earliest and latest date for a particular/unique ID.
For example: In my sample workbook, the earliest date for all the columns with ID = 1 should return 11/1/2001 and the latest date should be 12/2/2011. If all the dates for a particular ID are the same, it should just return that date. For example, for ID = 2, all dates are 5/5/2010 so the earliest and latest dates should equal 5/5/2010. The earliest and latest dates have to be for each unique ID.
I have a table that contains names and dates. I need to find the most recent / latest date in each record. For example:
Names Dates
A 01/02/09
A 05/25/09
B 06/09/09
B 07/30/09
B 01/02/09
C 03/09/09
C 02/28/09
D 12/12/09
The results should be:
A 05/25/09
B 07/30/09
C 03/09/09
D 12/12/09