Need a formula to calculate last 5 occurances. Expected output in columns f and g . Input in E2
Need a formula to calculate last 5 occurances. Expected output in columns f and g . Input in E2
Hi,
In F2 and drag to the right and down:
=INDEX(B$1:B$13,AGGREGATE(14,6,(ROW($C$2:$C$13)*($E$2=$A$2:$A$13)),ROWS(I$1:$J1)))
Formula F2 and E2
If you are using excel 2007, you can adopt the below cse formula
F2
Formula:Please Login or Register to view this content.
G2
Formula:Please Login or Register to view this content.
**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).
☚ Click ★ just below left if it helps, Boo?ath?
F2 cell array formula , Drag down and accross
HTML Code:
Only works for A and not for others and if i add more data to it it doesnt take that into consideration.
According to your example - it is only for A
If not - please post a representative file with versatile cases
And in the sample it is the last 5 values but the dates appear reversed! Which way round do you want them displayed?
Excel 2016 (Windows) 32 bit
E F G 2B 17/10/2020 - 55 3 02/10/2020 5
E F G 2B =IFERROR(INDEX($B$1:$B$100,LARGE(IF($A$2:$A$100=$E$2,ROW($A$2:$A$100),"#N/A"),ROW()-1)),"") =IFERROR(INDEX($C$1:$C$100,LARGE(IF($A$2:$A$100=$E$2,ROW($A$2:$A$100),"#N/A"),ROW()-1)),"") 3 =IFERROR(INDEX($B$1:$B$100,LARGE(IF($A$2:$A$100=$E$2,ROW($A$2:$A$100),"#N/A"),ROW()-1)),"") =IFERROR(INDEX($C$1:$C$100,LARGE(IF($A$2:$A$100=$E$2,ROW($A$2:$A$100),"#N/A"),ROW()-1)),"")
Happy with my answer * Add Reputation.
If You are happy with solution, please use Thread tools and mark thread as SOLVED.
Or try with a helper column
In H2 and drag down
Formula:Please Login or Register to view this content.
In F2 use the following formula and drag down and across
Formula:Please Login or Register to view this content.
See the file
Thank you all. Got it
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks