+ Reply to Thread
Results 1 to 10 of 10

Extracting latest prices from a price book with past and present prices

  1. #1
    Forum Contributor
    Join Date
    02-14-2012
    Location
    Warrington
    MS-Off Ver
    Excel 365
    Posts
    376

    Extracting latest prices from a price book with past and present prices

    Customer Price Book Latest Info Only.xlsx

    Can anyone add formulas to columns H, I & J

    Column H: Needs to pull price from the price book for that particular part and customer, , only for the newest start validity.
    Columns I & J: Needs to pull price breaks from the price book for that particular part and customer, only for the newest start validity.

    Being applied to a 50,000 row report so would like to avoid any sort of array formulas please

  2. #2
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: Extracting latest prices from a price book with past and present prices

    hi benoj2005. if your data is as what you stated below in the file (always arranged), you can try this simple formulas:

    H3:
    =IF(A3&B3=A2&B2,"",D3)

    I3:
    =IF(AND(OR(L3<>"",M2<>""),OR(C3=C2,C3=C4)),E3,"")

    J3:
    =IF(AND(OR(L3<>"",M2<>""),OR(C3=C2,C3=C4)),F3,"")

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

  3. #3
    Forum Contributor
    Join Date
    02-14-2012
    Location
    Warrington
    MS-Off Ver
    Excel 365
    Posts
    376

    Re: Extracting latest prices from a price book with past and present prices

    Nothing in columns L & M
    Please Login or Register  to view this content.
    is this a mistake or have you added helper columns?
    Last edited by Cutter; 08-10-2012 at 08:05 AM. Reason: Removed whole post quote

  4. #4
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: Extracting latest prices from a price book with past and present prices

    whoops sorry. i did in another column in order not to mess with your data.

    so it's:
    =IF(AND(OR(H3<>"",I2<>""),OR(C3=C2,C3=C4)),E3,"")

    and:
    =IF(AND(OR(H3<>"",J2<>""),OR(C3=C2,C3=C4)),F3,"")

  5. #5
    Forum Contributor
    Join Date
    02-14-2012
    Location
    Warrington
    MS-Off Ver
    Excel 365
    Posts
    376

    Re: Extracting latest prices from a price book with past and present prices

    You're a star, seems to have worked a treat.

    I may need some more help with the next step so stay tuned :P

  6. #6
    Forum Contributor
    Join Date
    02-14-2012
    Location
    Warrington
    MS-Off Ver
    Excel 365
    Posts
    376

    Re: Extracting latest prices from a price book with past and present prices

    Slight hitch in the way you've done it.

    =IF(AND(OR(H3<>"",I2<>""),OR(C3=C2,C3=C4)),E3,"")

    Cells in red should not be returned because they are not the latest price breaks.

    Customer Price Book Latest Info Only.xlsx



    *** EDIT:: This Customer Price Book Latest Info Only (With helper columns and pivot).xlsx is my work around, but it involves two helper columns (Organge) and a pivot table. It works but it's messy and I'd like to not have them if possible. A maxif function would solve this, as mentioned an array would be too cpu intensive. ***
    Last edited by benoj2005; 08-08-2012 at 10:49 AM.

  7. #7
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: Extracting latest prices from a price book with past and present prices

    yeah i'm having a bit of trouble because i read earlier that you do not want an array formula. i did not use any helper column but i did a pivot to find the max date.
    Attached Files Attached Files

  8. #8
    Forum Contributor
    Join Date
    02-14-2012
    Location
    Warrington
    MS-Off Ver
    Excel 365
    Posts
    376

    Re: Extracting latest prices from a price book with past and present prices

    Quote Originally Posted by benishiryo View Post
    yeah i'm having a bit of trouble because i read earlier that you do not want an array formula. i did not use any helper column but i did a pivot to find the max date.
    Thanks, I wasnt aware you could reference a pivot table in that way. Eliminates the two helper columns.

    The thing that annoys me, if a pivot table has the coding to find the max date then you would think there is a formula to do the same. I know there is the array, but that kills my machine, where as a pivot dosen't so I'd assume it does it in a less cpu laborious way
    Last edited by benoj2005; 08-09-2012 at 04:56 AM.

  9. #9
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: Extracting latest prices from a price book with past and present prices

    this might help.

    for Column I3:
    =IF(OR(AND(SUMPRODUCT(($A$3:$A3=$A3)*($B$3:$B3=$B3))=1,E3<>""),AND($C3=$C2,I2<>"",E3<>"")),E3,"")
    or
    =IF(OR(AND(COUNTIFS($A$3:$A3,$A3,$B$3:$B3,$B3)=1,E3<>""),AND($C3=$C2,I2<>"",E3<>"")),E3,"")

    copy & paste to J3 & below too.

  10. #10
    Forum Contributor
    Join Date
    02-14-2012
    Location
    Warrington
    MS-Off Ver
    Excel 365
    Posts
    376

    Re: Extracting latest prices from a price book with past and present prices

    Top man!! Used the countif function.

    Thanks
    Last edited by Cutter; 08-10-2012 at 08:07 AM. Reason: Removed whole post quote

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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