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,"")

Nothing in columns L & M

Please Login or Register  to view this content.

is this a mistake or have you added helper columns?

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,"")

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

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.

Originally Posted by benishiryo

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

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.

Top man!! Used the countif function.

Thanks