+ Reply to Thread
Results 1 to 13 of 13

Date of last transaction

  1. #1
    Registered User
    Join Date
    10-01-2009
    Location
    North Charleston
    MS-Off Ver
    Excel 2003
    Posts
    9

    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. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    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.
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Registered User
    Join Date
    10-01-2009
    Location
    North Charleston
    MS-Off Ver
    Excel 2003
    Posts
    9

    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. #4
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    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. #5
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    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. #6
    Registered User
    Join Date
    10-01-2009
    Location
    North Charleston
    MS-Off Ver
    Excel 2003
    Posts
    9

    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.
    Last edited by JMaderson; 10-01-2009 at 01:20 PM.

  7. #7
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    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
    Last edited by DonkeyOte; 10-01-2009 at 01:23 PM.

  8. #8
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    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. #9
    Registered User
    Join Date
    10-01-2009
    Location
    North Charleston
    MS-Off Ver
    Excel 2003
    Posts
    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. #10
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    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

    Quote 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. #11
    Registered User
    Join Date
    10-01-2009
    Location
    North Charleston
    MS-Off Ver
    Excel 2003
    Posts
    9

    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. #12
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    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. #13
    Registered User
    Join Date
    10-01-2009
    Location
    North Charleston
    MS-Off Ver
    Excel 2003
    Posts
    9

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

Bookmarks

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