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

Lookup rows until reached value

Tommy24

New Member
Hello,
I'm new here so maybe not aware of every rules but I'm blocked on a formula in order to lookup several rows until reaching a certain amount and drag it.
Is someone already faced this kind of formula ?
I attached you a file who will be maybe more explicit.
Many thanks
 

Attachments

p45cal

Well-Known Member
Power Query offering in attached. See result table at cell J2.
Right-click it and choose Refresh to update it as you would a pivot table.
Change the extent of the table at cell D2 to include some more sample rows; one with apple and the other a mis-spelling to see what happens.
The sort order of the table at cell A2 doesn't matter, the query does its own sorting.
One anomaly is if you have 0 for the amount in the Real Impact table it still returns one row from the first table.
 

Attachments

Tommy24

New Member
Hello p45cal
Sorry to reply so late, your code works well but it's very complicated for me to adapt it to the real file I have.

Do you know if there is a way to find the same result with formulas only, avoiding Power Query ?

Thanks
 

John Jairo V

Well-Known Member
Hi to all!
Another option (With Microsoft 365) could be:
PHP:
=LET(o,SORT(A3:B18,{1;2},{1;-1}),s,"|",
f,LAMBDA(t,p,TEXTBEFORE(t,p)),g,LAMBDA(t,p,TEXTAFTER(t,p)),n,INDEX(o,,1),
a,--MAP(SCAN("|0",n&s&INDEX(o,,2),LAMBDA(i,x,f(x,s)&s&(f(i,s)=f(x,s))*g(i,s)+g(x,s))),LAMBDA(n,g(n,s))),
r,MAP(n,LAMBDA(x,LET(v,FILTER(a,n=x),XLOOKUP(SUMIF(D3:D4,x,E3:E4),v,v,,1)))),
VSTACK(A2:B2,FILTER(o,(a<=r)*COUNTIF(D3:D4,n))))
Check file. Blessings!
 

Attachments

p45cal

Well-Known Member
Can you give the password to unprotect the sheet. I can't place a table even on unlocked cells.
So far I can get this:
81148

Also I can't change your pivot table to repeat the headers in the Account column which would help with a formula solution.
ps. would a user defined function be OK? it would require the file to be macro-enabled.
 

p45cal

Well-Known Member
You told me the password to unprotect that sheet in a private converstion, which worked. The attached has that sheet that I protected still with the same password.
In the attached is the solution I pictured in my last message. It's a Power Query solution and does NOT use the pivot table om Sheet1, but instead refers to the file:
W:\08_OTC\01_AR_Collections\2021\1. Off-Trade\1. Dashboard OD.CL\BE Offtrade 29-09-2022.xlsx
on your system.
Check:
1. It's more or less correct (you'll need to right-click somewhere on the result table (at cell J18 of Sheet1)) to update the information from your system (at the moment it's showing data from my system). If it is then I'll need to make it a bit more user friendly (eg. for determining the file you want to get info from, and for filtering that data)
2. You're happy for this to be a Power Query solution (no macro code needed (except to make it a lot more user-friendly))

Otherwise I'll go about writing a macro-only user-defined function. I'll wait to hear first.
 

Attachments

Tommy24

New Member
Hello
It works well, I adapted it to my files and I am very happy with it, no need a macro. Many thanks for your great help!
 

p45cal

Well-Known Member
Good. Be aware that the query filters out account no. 30062069 as that is what your pivot table did.
 

Tommy24

New Member
Indeed, I checked all the filters and they remained as they was in the pivot, so it's exactly what I was looking for.

Thanks again !
 
Top