Extract All Values With Criteria Sorted

Lewzerrrr

Active Member
Joined
Jan 18, 2017
Messages
251
Hi,

I would like to be able to extract all values following one criteria but for the formula to automatically sort it.

Please see below table to what I'm referring to.

In table 1 would be the source data, I need to extract all the number and 4 digit letter based on the Order # & Fruit concat. but for it to then sort it within the formula.

In column L i've just used =IFERROR(INDEX($B$3:$B$14,SMALL(IF($F$3:$F$14=$I3,ROW($B$3:$B$14)-ROW($B$3)+1),$H3)),"") however this doesn't sort it as you can see in column K.

ABCDEFGHIJKL
1Num.4 DigitFruitOrder #ConcatHelperConcatExpected ResultExpected ResultINDEX SMALL IF
24AAAAPEAR22PEAR11APPLE7DDDD7DDDD
35BBBBAPPLE22APPLE21APPLE8GGGG10FFFF
46CCCCPEAR22PEAR31APPLE10FFFF8GGGG
57DDDDAPPLE11APPLE41APPLE
61EEEEPEAR11PEAR51APPLE
710FFFFAPPLE11APPLE12APPLE5BBBB5BBBB
88GGGGAPPLE11APPLE22APPLE
912HHHHPEAR22PEAR32APPLE
103IIIIPEAR11PEAR42APPLE
112JJJJPEAR11PEAR52APPLE
1211KKKKPEAR11PEAR11PEAR1EEEE1EEEE
139LLLLPEAR22PEAR21PEAR2JJJJ3IIII
1431PEAR3IIII2JJJJ
1541PEAR11KKKK11KKKK
1651PEAR
1712PEAR4AAAA4AAAA
1822PEAR6CCCC6CCCC
1932PEAR9LLLL12HHHH
2042PEAR12HHHH9LLLL

<colgroup><col span="11"><col><col></colgroup><tbody>
</tbody>
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Watch MrExcel Video

Forum statistics

Threads
1,109,533
Messages
5,529,406
Members
409,871
Latest member
i1patrick
Top