# Date of last transaction

1. ## Date of last transaction

I'm dealing with 2 spread sheets. The first, "Transactions", lists transactions by date, but they are grouped by series. So the 7 series dates are ascending but when you continue to the 8 series it starts at the first date of transaction and ascend to the latest. The second spread sheet, "Sheet1" is an inventory and lists date of last transaction, part #, Part name, sorted ascending by part #. I need a function that will reference the Part # in sheet1 and yeild the date of the last transaction made with that part #. I have not been able to figure it out. It would be much simpler if it were in Pascal. haha.

2. ## Re: Need formula to Yield the date of the last transaction of a particular part #

Generally, this will get last entry of particular part

=Lookup(2,1/(Sheet1!A1:A100=A1),Sheet1!B1:B100)

where Sheet1!A1:A100 holds parts to match with A1 of active sheet and Sheet1!B1:B100 holds dates.

3. ## Re: Need formula to Yield the date of the last transaction of a particular part #

this is yielding a five digit code. The way I've thought about doing it is finding the max date of the group of dates that will match the part #. But I have not been able to find a code that works. When I took your function, I translated it to =LOOKUP(2, 1/Transaction!D:D=Sheet1!K2), Transactions!C:C) but for some reason it doesn't work. perhaps the number that it yields is an Excel Date code but I haven't converted the dates in the date column to the Excel date code.

4. ## Re: Need formula to Yield the date of the last transaction of a particular part #

Can't use whole column references with that function in XL2003

Maybe, also post a sample spreadsheet showing your intentions.

5. ## Re: Need formula to Yield the date of the last transaction of a particular part #

perhaps the number that it yields is an Excel Date code but I haven't converted the dates in the date column to the Excel date code.
Dates in XL are integers, today (on 1900 date system) being 40087, if the cell containing the formula is format to General etc you will see the Integer value of the Date, to see the Date simply format the cell to use a Date Format.

6. ## Re: Need formula to Yield the date of the last transaction of a particular part #

Excellent! Thank You! With a little modifiction the code seems to work. The only thing is that there are some items in inventory that have no transactions. is there some kind of if then or true false function I could tack onto this to put a 0, or blank in the cell if it hasn't had a transaction yet? In addition, just for further knowledge, why make the lookup value 2, rather than a cell reference, and the lookup vector 1/(cell reference=cell reference). I don't know that I understand directly the code behind this.

7. ## Re: Need formula to Yield the date of the last transaction of a particular part #

Based on

=LOOKUP(2,1/(Transaction!D1:D100=Sheet1!K2),Transactions!C1:C100)

Modify to

=IF(ISNA(MATCH(Sheet1!K2,Transactions!D1:D100,0)),0,LOOKUP(2,1/(Transaction!D1:D100=Sheet1!K2), Transactions!C1:C100))

Then if you wish to hide the 0's you can use a Custom Format on the cell(s) containing the formulae of: dd-mmm-yy;;
(adjust date format to suit preferences)

Re: use of LOOKUP(2,1/(...)) etc... I normally refer to this post where I tried to explain the basic premise some time back - others have done so better I am sure...

http://www.excelforum.com/2100732-post14.html

8. ## Re: Need formula to Yield the date of the last transaction of a particular part #

If I had faster fingers..my answer would have been very similar...

with my explanation here:

http://www.excelforum.com/excel-gene...ml#post1997590

9. ## Re: Need formula to Yield the date of the last transaction of a particular part #

Alright thanks, i actually understand it pretty thoroughly now. the =IF(ISNA(MATCH code that the other person wrote seems to work as well. It successfully returns 0 instead of a date for index items with no transactions, but when I format the cells to Date it returns 01/00/00 instead of a blank. Any ideas?

10. ## Re: Need formula to Yield the date of the last transaction of a particular part #

Dates in XL are Integers ... day 0 on 1900 Date System is 0 Jan 1900

To show 0 as blank you can apply a Custom Format to the cell(s) containing the formulae as outlined previously

Originally Posted by DO
...if you wish to hide the 0's you can use a Custom Format on the cell(s) containing the formulae of: dd-mmm-yy;;
(adjust date format to suit preferences)
In your case it looks like you would want a format of: mm/dd/yy;;

11. ## Re: Need formula to Yield the date of the last transaction of a particular part #

Right on, thank you. Just out of curiousity what is the ISNA function?

12. ## Re: Need formula to Yield the date of the last transaction of a particular part #

See Excel Help for the ISNA function.. it explains it there.. (Is Not Available).. referring to the #N/A error..

So if result is #N/A the do one thing, else do the other...

13. ## Re: Need formula to Yield the date of the last transaction of a particular part #

Alright, well this forum has really helped me out. I got the exact information I needed, and suprisingly quickly. I will definately come back to it if need be.

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1