• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

data lookup

M

Martin

New Member
Hi forum ! it's been a while since last visiting....


Yesterday I found myself in front of a new Excel challenge, which I'd like your feedback on.


I have a list, with 3 columns: product description, last purchase date, Qty.

I wanted to display in a next column the last qty purchased for each product.

So far, I managed to return the last purchasing date of the selected product, but then I found out that I was unable to even retrieve its row number, to use it as reference in an INDEX, or any other way to do it.


Any suggestions?


Thanks !!!
 
Luke,


I Believe I found it !!


=INDEX($C$2:$C481;MAX(IF((A$2:A481=A482);ROW(A$2:A481)-1)))


entered as an array.


any non-array ideas?


Thanks !
 
BTW, I am using that formula in a List.


I've noticed that whenever I enter a new row, that formula copies itself wrongly.


Any comments?


Thanks !
 
Similar,

=INDEX(C:C,SUMPRODUCT(MAX((A$2:A2=A3)*(ROW(A$2:A2)))))


Sorry about not stating before, but this formula:

=INDEX(C:C,MAX(IF(A$2:A2=A3,ROW(A$2:A2))))


should have been an array. my apologies.
 
Can you clarify what you mean by "copies itself wrongly"? Should the formula not be copying itself, or is it changing cell references incorrectly?
 
sure !!


when adding a new record, the formula is changing cell references incorrectly.


this is for row 498, the last on my list:


=INDEX($C$2:$C497;MAX(IF(($A$2:A497=A498);ROW($A$2:A497)-1)))


this appears for row 499, the new record:


=INDEX($C$2:$C500;MAX(IF(($A$2:A500=A499);ROW($A$2:A500)-1)))


note that the ending row for the ranges is 500, when it should be 498.


Thanks !
 
Ugh. I see what you mean, the list feature is screwing up all the relative references...this may take some time.
 
Okay, I think this non-array formula works for a List. Putting this in D2:

=IF(COUNTIF($A$1:OFFSET($A$1,ROW(D2)-2,),A2)=0,"",INDEX(C:C,SUMPRODUCT(MAX(($A$1:OFFSET($A$1,ROW(D2)-2,)=A2)*(ROW($A$1:OFFSET($A$1,ROW(D2)-2,)))))))


It looks like when use you arrays with a List, XL tries to automatically change the arrays to extend to last row in list. So, I'm using the OFFSET function to relate everything back to the beginning of the list.
 
Luke, you are a Monster!!! (in the best sense of it !!!)


Now, we all know index/match is better than offset as it is non-volatile, hence faster.


Your mission, should you choose to accept it, is to upgrade your current formula, avoiding the use of vlookup !!!


That said, you are now my personal hero, along with Chandoo, Debra, Daniel, Hui, and so on...well, you are now in my Hall of Fame !!!


THanks so much for your help !
 
Thanks for the compliments, Martin.

Challenge accepted, and defeated:

=IF(COUNTIF($A$1:INDEX(A:A,ROW(D2)-1,),A2)=0,"",INDEX(C:C,SUMPRODUCT(MAX(($A$1:INDEX(A:A,ROW(D2)-1,)=A2)*(ROW($A$1:INDEX(A:A,ROW(D2)-1,)))))))
 
WOW !!!!!!


now, you are only one step closer to the top !!!


I'm going to review it, as the results seem to be the same, and see if I can get rid of all the IFs, using Daniel Ferry's technique of treat IF as boolean operations.


in any case, my inventory is now even faster !!!


Thanks again for your kind support !


RGds,

Martin
 
You must log in or register to reply here.
Back
Top