Last Date in List

Hi

I have a table set out as follows, 

Account  Date     Text  Amount
31405535 01/01/04 nnnn  100000
31405535 12/12/03  tttt 222222
31405535 03/03/05 ttt   122456

There are 5 different accounts which could be entered under Account.
The list is a export from out bank statement, I would like a formula
to display the last date of transaction per account number. For eg in
the above list I would like the formula to return 03/03/05.

Can anyone help?

TIA
0
ethoemmes (1)
3/3/2005 2:10:04 PM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
517 Views

Similar Articles

[PageSpeed] 38

Enter your list of account numbers in a column, let's say Column F, 
starting at F2...

G2, copied down:

=LOOKUP(2,1/($A$2:$A$100=F2),$B$2:$B$100)

Hope this helps!

In article <622ba960.0503030610.3bda6084@posting.google.com>,
 ethoemmes@blueyonder.co.uk (Edgar Thoemmes) wrote:

> Hi
> 
> I have a table set out as follows, 
> 
> Account  Date     Text  Amount
> 31405535 01/01/04 nnnn  100000
> 31405535 12/12/03  tttt 222222
> 31405535 03/03/05 ttt   122456
> 
> There are 5 different accounts which could be entered under Account.
> The list is a export from out bank statement, I would like a formula
> to display the last date of transaction per account number. For eg in
> the above list I would like the formula to return 03/03/05.
> 
> Can anyone help?
> 
> TIA
0
domenic22 (716)
3/3/2005 2:31:53 PM
On 3 Mar 2005 06:10:04 -0800, ethoemmes@blueyonder.co.uk (Edgar Thoemmes)
wrote:

>Hi
>
>I have a table set out as follows, 
>
>Account  Date     Text  Amount
>31405535 01/01/04 nnnn  100000
>31405535 12/12/03  tttt 222222
>31405535 03/03/05 ttt   122456
>
>There are 5 different accounts which could be entered under Account.
>The list is a export from out bank statement, I would like a formula
>to display the last date of transaction per account number. For eg in
>the above list I would like the formula to return 03/03/05.
>
>Can anyone help?
>
>TIA

The Array formula:

=MAX((Account=31405535)*Date)

To enter an array formula, after typing or pasting in the formula, hold down
<ctrl><shift> while hitting <enter>.  Excel will place braces {...} around the
formula.


--ron
0
ronrosenfeld (3122)
3/3/2005 2:32:55 PM
An AutoFilter would work well here. With account #'s in 
col. A and dates in col. B, create a new col. 
called "Max" in col. E and copy this into E2:

=B2=MAX(($A$2:$A$100=A2)*$B$2:$B$100)

After copying, press ctrl + shift + enter, and fill the 
formula all the way down.

Now select the 5 columns and go to Data > Filter > 
AutoFilter and filer for TRUE in col. E.

HTH
Jason
Atlanta, GA

>-----Original Message-----
>Hi
>
>I have a table set out as follows, 
>
>Account  Date     Text  Amount
>31405535 01/01/04 nnnn  100000
>31405535 12/12/03  tttt 222222
>31405535 03/03/05 ttt   122456
>
>There are 5 different accounts which could be entered 
under Account.
>The list is a export from out bank statement, I would 
like a formula
>to display the last date of transaction per account 
number. For eg in
>the above list I would like the formula to return 
03/03/05.
>
>Can anyone help?
>
>TIA
>.
>
0
jasonjmorin (551)
3/3/2005 2:33:41 PM
=MAX(IF(A1:A10=31405535 ,B1:B10))

which is an array formula;a so commit with Ctrl-Shift-Enter
-- 

HTH

RP
(remove nothere from the email address if mailing direct)


"Edgar Thoemmes" <ethoemmes@blueyonder.co.uk> wrote in message
news:622ba960.0503030610.3bda6084@posting.google.com...
> Hi
>
> I have a table set out as follows,
>
> Account  Date     Text  Amount
> 31405535 01/01/04 nnnn  100000
> 31405535 12/12/03  tttt 222222
> 31405535 03/03/05 ttt   122456
>
> There are 5 different accounts which could be entered under Account.
> The list is a export from out bank statement, I would like a formula
> to display the last date of transaction per account number. For eg in
> the above list I would like the formula to return 03/03/05.
>
> Can anyone help?
>
> TIA


0
bob.phillips1 (6510)
3/3/2005 2:35:27 PM
Reply:

Similar Artilces:

Find the last, 2nd last and 3rd last data in a column
I have data listed in several colums and need to create a formula to detail the last (most recent) 2nd last and 3rd last piece of data in the column. I have used the following formula to display the last but cant edit this or create another formula to get the 2nd and 3rd last pieces of data. =LOOKUP(2,1/(A:A<>0),A:A) Thanks in advance Jamie If there are no empty cells in the range: last cell: =INDEX(A:A,COUNTA(A:A)) penultimate: =INDEX(A:A,COUNTA(A:A)-1) semi-penultimate: =INDEX(A:A,COUNTA(A:A)-2) best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme r...

how to work with food products that have best befor dates
How to controll end of validity time for produt? How to handle parties of received produkts an validity date for party? I don't found parties in RMS If by "parties" you are referring to lots, I don't think RMS supports this, where you can assign item to a lot, then the expiration date to that lot....I don't think you have much of an option, unless you want to consider manually setting on and off the "block sales of this item" flag (not very practical).... "Martins" wrote: > How to controll end of validity time for produt? > How to handle...

save by & date, time
sir, can or not we get this info (history) using excel/VBA if can, how? tq Try this small macro: Sub dural() s = ThisWorkbook.BuiltinDocumentProperties("last save time") MsgBox s End Sub -- Gary''s Student - gsnu201001 "nordiyu" wrote: > sir, > can or not we get this info (history) using excel/VBA > if can, how? > > tq ...

How can I display worksheet summary page ie title/size/last updat.
I'm running MSExcel 2003. I am often working with large workbooks with mutiple worksheets. It would be v helpful if I could see/sort and print off a summary that shows worksheet properties similar to Explorer eg: workbook :xxxx ---------------- worksheet last update size names 1/2/04 64k companies: 5/2/03 893k etc... any ideas? Jon I wrote this sometime ago and it works as a workbook_open event (Needs to be placed in the ThisWorkbook code module). It could be adapted to work otherwise though. It adds a sheet and lists all the built-in and c...

Comparing Date Ranges in one Table to Date Ranges in another Table
I am trying to compare a sheet with claims (with from and through dates) to another sheet with amounts applicable to a different and potentially overlapping set of date ranges. In Sheet 1, each record consists of a claim ID, a member ID, and the from and thru dates. In Sheet 2, there is a Member ID, from and thru dates (more than one set of dates per member), and an applicable dollar amount associated with that date range. What I am trying to do is to show the dollar amount in Sheet1 for each claim that matches with the date ranges shown in Sheet 2. Although my ultimate goal ...

Need to merge two lists(worksheets) into one
I have a list of names with addresses. I have a separate list of names with Phone numbers. These two lists are in separate workbooks. I can copy the work sheet of one into the the same workbook of the other, but I want to find a function, create a formula or write a VBA macro that will search for the phone number of a person in one and copy it to a new field called "phone" next to the address field of that person in the other work sheet. The two lists may or may not have common data on them. For example John Doe is on both lists. He has a phone number on one and an address on...

Exchange license does not appear in Server license list
I have just recently migrated from exchange 2000 to 2003 except this time echange is not running on a DC. But on the DC under license there is not the drop down to add exchange license-exchange is running on a member server within my domain-how do I get the drop down to show exchange 2003 or just exchange? On Fri, 17 Feb 2006 16:13:28 -0800, Gordon <Gordon@discussions.microsoft.com> wrote: >I have just recently migrated from exchange 2000 to 2003 except this time >echange is not running on a DC. But on the DC under license there is not the >drop down to add exchange lice...

to have a start date and end date for assigning periods
When assigning start date, you should also be able to assign an end date, therefore allowing overlap if need be. This would also require that the software be driving by period and not by date. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/B...

exporting the send and receive date of messages
I am trying to export the send and receive date of messages to excel or access to do an analysis of time between the receipt of an e-mail and the response. Any suggestions. The export function does not give the choice of the send or received dates. ...

Crosstab query for multiple dates
I have a table with following fields: PatientName;DateOfVisit;Weight I need to create a crosstab query with PatientName as Raw Heading, DateOfVisit as Column Heading and Weight as Value. This approach is not working for me as too many dates exist. I need to convert dates into some kind of VisitNumber or Occurance, etc. Say, if a patient had 5 different DateOfVisit, I would like to transform these Dates into Visit1, Visit2, etc. I will appreciate any help in this matter. Leonid ...

Date Format #27
Dear friends, Everytime I type the Date of 10 Dec. 2005 as 10/12/2005, it converted to 12/10/2005 and it refused to be modified when I try to adjust it from FormatCells. -- Regards, ------------------------------------------------- Mahmoud Metwally Ali Jaicorp IT Manager Office Tel. : (202) 3471495/361 Mob. : (2010) 5195169 Hi Mahmoud Sounds like you have to change your regional settings. Go start|Settings(?)|Control Panel, select regional settings, select Date and time, and change your date fromat to dd/mm/yyyy "Mahmoud Metwaly" wrote: > Dear friends, >...

Difference between today's date and a past date
Hi, I have a list which contains a column that is of the "Date and Time" type. It contains dates for, say, new hires. I want to calculate the differences between today's date and these hire dates. I found that the function TODAY would give me today's date, however, TODAY can't be used in a calculated column. Any suggestions? Thanks. Richard ...

Querying results for two recent dates
I have a table "DETAILS" in which fields are Name Id TestDate Grade (all fields can be duplicated) Now I want a query which should return me 'Name' 'Id' ' TestDate' 'Grade in 2nd Last Test' 'Grade in Latest Test' It should be noted that every person appears the test many times and i just want the recent two results. The query should return names of only those people who have appeared in either or both of the last two tests I was trying to do this by running a query on a query but the results were ...

How long will my capital last
Hi If I had a capital sum of say �100,000 and I was taking an annua income of 5% and I was getting 3.5% interest annually - Can you help m demonstrate how long my capital last? Thank you Dust -- Message posted from http://www.ExcelForum.com Dusty, =3DNPER(3.5%,-5000,100000,0) If, by 5%, you mean 5% of the original investment (=A35.000 each year), = your money will last for approximately 35 years at 3.5% interest. See Excel Help for more info on NPER(). Regards, Anders Silven "Durhamr" <Durhamr.yajun@excelforum-nospam.com> skrev i meddelandet = news:Durhamr.yajun@excelf...

Question about transaction dates.
Hello, i dont know if this happen just here in Mexico; our credit cards use a cut date and a limit date (to pay); so altough we have the original transaction date, we don't pay that date (we pay some days later based in the cut and the limit date) I can not see in money in the credit accounts, any parameter about cut and limt date. I know that i can set the correct date, but is better to have the original transaction dates. Also here is very comun to buy any article and to pay it in 6 or 12 months without interest; in this case i use the recurring option, and i can delay the transaction...

Multi-select listbox question
I have a multi-select listbox and wish to obtain data from the last clicked record in the listbox (you'll notice that if you make multiple selections then only the last-clicked record has a dotted line around it). What, if any, listbox property identifies this record as last-clicked? Thanks Dave -- Posted via a free Usenet account from http://www.teranews.com ...

Extract date only from Windows serial date
I want to compare a user-specified date, such as 3/3/2010, with the date associated with a tracked change. The problem is ActiveDocument.Revisions(1).Date also returns the time of day. I have come up completely empty on the means of referencing the date portion only of the tracked change. Sure hope someone can educate me, as I'm sure I've got to be missing something incredibly obvious. Hi dedawson, Sub Test() MsgBox Format(ActiveDocument.Revisions(1).Date, "DD/MM/YYYY") End Sub -- Cheers macropod [Microsoft MVP - Word] "dedawson" <d...

why is my last digit converted to zero?
I'm using Excel 2003 and when I enter a 16-digit number or cut and paste a 16-digit number, the last digit is converted to zero. This does not happen with 15 digits or fewer. Excel's numeric precision is 15 decimal digits. If you need to calculate with the number, you probably won't miss anything. If the number is a key, such as a creditcard number, add an apostroph (single quote, " `") before it; it will not show, but it will cause the number to be treated as text. Another way is to format the cell as text before entering the number -- Kind Regards, Niek Otten ...

Need help in searching last occurrence of string in text file
Hi , I am not good in file handling in excel macro..i need some help from u. I have one huge text file and i want to search last occurrence of string in that file and then cut the data after that searched line till end of file and paste it into a new text file. Any help appreciated. thanks in advance. How huge is "huge" (in Megs)? -- Rick (MVP - Excel) <sameer2211@gmail.com> wrote in message news:8cb08938-cf0e-4925-95bb-725f72df9f8d@25g2000prz.googlegroups.com... > Hi , > > I am not good in file handling in excel macro..i need some help from > u. > I hav...

2 Que's regarding dates (matching and subtracting) #2
Thanks for the help! Okay, I got que. #1 all figured out! #2 however, is still a little sticky. This is exactly what I'm trying to accomplish: The end-user enters a date in cell a2, validated to format DD/MM/YYYY. I want to look up that date and match it to a corresponding "table" on sheet3. On Sheet3, currently, I have the date, in M (September) format, in column B, and a value (%) in column C. Essentially, I want to input date ranges in this "table", so for column B2, it would be September 16th, 2004, to October 15th 2004 (instead of just September), but I do...

Export removes dates?
This is something that's been bugging me for a while. When exporting messages from Outlook to a csv or xls message dates are not included. Is there any way around this? B LOL, after browsing past posts regarding this issue I'm not expecting a response. It would appear exporting message date fields is impossible. B "Battleax" <unavailable@thistime.net> wrote in message news:ddSdnTIvH_7M2Z3d4p2dnA@magma.ca... > This is something that's been bugging me for a while. When exporting > messages from Outlook to a csv or xls message dates are not included. > I...

Last logged on by
Hello! Using exchange 2003. When I look under Adminstrative groups -> Server -> "name" -> First storage group -> mailbox store -> mailboxes you can se all the mailboxes that you have and last logged on by. Under last logged on by I can se that one person (me) have logged on on to diffrent acconts, even if I havent don it. WHAT is this? Best regards Micke Micke: Did you use your account as the service account for anti-virus or spam? Also, IIRC, even if you view someone's Outlook Calendar, it would mark the mailbox as being logged on by you. Regards...

First and Last Problem
For some reason First and Last are giving the wrong data in a Totals query. I assume that somehow the data is not ordered correctly in the main table, but it does show correctly in the datasheet view. For example if I group the sample database below by Name and Year, First of Location should be B, unfortunately it shows up in the query as A. Name Year Stint Location Ed 1999 1 B Ed 1999 2 A Ed 2000 1 A Desired Result Name Year Stint Location Ed 1999 1 B (But shows up as A) Ed 2000 1 A Any ideas from someone who has seen thi...

Extract last name from Last, First
I know this has to be a common question, but I can't find the answer... Using VBA, I need to extract the last name from the format: Last, First For example: the cell contents extract to a variable the value of JONES, AMY JONES ADAMS, HARRY ADAMS Your help would really be appreciated! I don't know how you are using it, but this shows how to get it in a variable. Sub dk() Dim lName As String lName = Left(ActiveCell, InStr(ActiveCell, ",") - 1) MsgBox lName End Sub ...

change date formatting
I'm using mail merge in publisher and accessing data in access. However the data in access in showing one date format (english NZ) my regional settings are showing as (english NZ) but when the data is mailmerged into publisher it changes to a US format 10/22/05 instead of 22/10/05. I can't figure out why. If I do the same mailmerge in Office it keeps the correct format which leads me to believe its a setting in publisher but I can't seem to figure it out. HELP PLEASE!!! If you have the dates already in place in Access, changing the field to text might be the solution. Don...