Find row of last occurrence

mintz

Board Regular
Joined
Aug 5, 2015
Messages
129
In Sheet1 I have a list of order records:

image.png



In Sheet2 I have a list of payment records:
image.png


In Sheet2, I need A10 to find the last paid order in the cells above it (i.e. 1033), then sum up the next order (1034) till the last order (1038) from Sheet1

something like
B10=SUM(Sheet1!ROW(1034):Sheet1!LASTROW)

How do I do that?
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Try to post Excel readable data, not images. Also, better not to discuss a non-working formula but specify the outcome you need.
 
Upvote 0
Try to post Excel readable data, not images. Also, better not to discuss a non-working formula but specify the outcome you need.

Yes just thought it would be easier to see what I'm trying from picture

Here's the data:

OrderTotal
103111.00
10329.00
103321.00
10343.00
1035-18.00
1035-26.00
10365.00
10378.00
1038-15.00
1038-25.00
1038-35.00

<tbody>
</tbody>


OrdersTotal
1000-100224.00
balance6.00
1003-100865.00
1009-102190.00
1022-102434.00
balance10.00
1025-103059.00
1031-103341.00
1034-103845.00

<tbody>
</tbody>


P.S. The paid column doesn't really matter
 
Last edited:
Upvote 0
Will it always be 4 digits? How about :
=SUMPRODUCT(--(VALUE(LEFT('Sheet 1'!A1:A5000))>=VALUE(RIGHT(A9,4))))
 
Upvote 0
In A10 of Sheet2 enter:

=RIGHT(A9,4)&"-"&LEFT(LOOKUP(REPT("z",255),Sheet1!A:A),4)

In B10 of Sheet2 control+shift+enter, not just enter:

=SUM(INDEX(Sheet1!B:B,MATCH(RIGHT(A9,4),LEFT(Sheet1!A:A,4),0)+1):INDEX(Sheet1!B:B,MATCH(REPT("z",255),Sheet1!A:A)))
 
Upvote 0
In A10 of Sheet2 enter:

=RIGHT(A9,4)&"-"&LEFT(LOOKUP(REPT("z",255),Sheet1!A:A),4)

In B10 of Sheet2 control+shift+enter, not just enter:

=SUM(INDEX(Sheet1!B:B,MATCH(RIGHT(A9,4),LEFT(Sheet1!A:A,4),0)+1):INDEX(Sheet1!B:B,MATCH(REPT("z",255),Sheet1!A:A)))

It seems to work great when the last order in Sheet1 has a "-" in the middle, but if it doesn't include a "-" the order doesn't get counted, how do I go around that?
e.g. when I added 1038-4 it counted it but when I added 1039 it just counted until 1038-4
 
Upvote 0
One exception case, when the row above is anything other than xxxx-xxxx (e.g. "balance") it needs to lookup the row above till it finds a xxxx-xxxx combination

ok:
image.png


error:
image.png


btw it can be a UDF if it's impossible to achieve with in-cell formulas
 
Upvote 0
It seems to work great when the last order in Sheet1 has a "-" in the middle, but if it doesn't include a "-" the order doesn't get counted, how do I go around that?
e.g. when I added 1038-4 it counted it but when I added 1039 it just counted until 1038-4

In A10 of Sheet2 enter:

=RIGHT(A9,4)&"-"&LEFT(LOOKUP(9.99999999999999E+307,SEARCH("?",Sheet1!A:A),Sheet1!A:A),4)

In b10 of Sheet2 control+shift+enter, not just enter:

=SUM(INDEX(Sheet1!B:B,MATCH(RIGHT(A9,4),LEFT(Sheet1!A:A,4),0)+1):INDEX(Sheet1!B:B,MATCH(9.99999999999999E+307,SEARCH("?",Sheet1!A:A))))
 
Upvote 0
One exception case, when the row above is anything other than xxxx-xxxx (e.g. "balance") it needs to lookup the row above till it finds a xxxx-xxxx combination

ok:
image.png


error:
image.png


btw it can be a UDF if it's impossible to achieve with in-cell formulas

Modify A10 to:

=RIGHT(LOOKUP(9.99999999999999E+307,SEARCH("????-????",$A$2:A9),$A$2:$A9),4)&"-"&LEFT(LOOKUP(9.99999999999999E+307,SEARCH("?",Sheet1!A:A),Sheet1!A:A),4)
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,998
Members
448,539
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top