Date of last transaction

**JMaderson** · 10-01-2009, 11:03 AM

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.

**NBVC** · 10-01-2009, 11:07 AM

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.

**JMaderson** · 10-01-2009, 11:26 AM

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.

**NBVC** · 10-01-2009, 11:49 AM

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

Maybe, also post a sample spreadsheet showing your intentions.

**DonkeyOte** · 10-01-2009, 12:08 PM

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.

**JMaderson** · 10-01-2009, 01:17 PM

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.

**DonkeyOte** · 10-01-2009, 01:20 PM

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

**NBVC** · 10-01-2009, 01:41 PM

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

with my explanation here:

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

**JMaderson** · 10-02-2009, 10:44 AM

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?

**DonkeyOte** · 10-02-2009, 10:47 AM

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;;

**JMaderson** · 10-02-2009, 01:14 PM

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

**NBVC** · 10-02-2009, 01:20 PM

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...

**JMaderson** · 10-02-2009, 01:24 PM

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.

Date of last transaction

LinkBack

Thread Tools

Rate This Thread

Display

Date of last transaction

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

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

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

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

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

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

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

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

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

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

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

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

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions