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.
[TABLE="width: 871"]
<colgroup><col span="11"><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]Num.[/TD]
[TD]4 Digit[/TD]
[TD]Fruit[/TD]
[TD]Order #[/TD]
[TD]Concat[/TD]
[TD][/TD]
[TD]Helper[/TD]
[TD]Concat[/TD]
[TD]Expected Result[/TD]
[TD]Expected Result[/TD]
[TD]INDEX SMALL IF[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]4[/TD]
[TD]AAAA[/TD]
[TD]PEAR[/TD]
[TD="align: right"]2[/TD]
[TD]2PEAR[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD]1APPLE[/TD]
[TD="align: right"]7[/TD]
[TD]DDDD[/TD]
[TD="align: right"]7[/TD]
[TD]DDDD[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]5[/TD]
[TD]BBBB[/TD]
[TD]APPLE[/TD]
[TD="align: right"]2[/TD]
[TD]2APPLE[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD]1APPLE[/TD]
[TD="align: right"]8[/TD]
[TD]GGGG[/TD]
[TD="align: right"]10[/TD]
[TD]FFFF[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]6[/TD]
[TD]CCCC[/TD]
[TD]PEAR[/TD]
[TD="align: right"]2[/TD]
[TD]2PEAR[/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD]1APPLE[/TD]
[TD="align: right"]10[/TD]
[TD]FFFF[/TD]
[TD="align: right"]8[/TD]
[TD]GGGG[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]7[/TD]
[TD]DDDD[/TD]
[TD]APPLE[/TD]
[TD="align: right"]1[/TD]
[TD]1APPLE[/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[TD]1APPLE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: right"]1[/TD]
[TD]EEEE[/TD]
[TD]PEAR[/TD]
[TD="align: right"]1[/TD]
[TD]1PEAR[/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[TD]1APPLE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="align: right"]10[/TD]
[TD]FFFF[/TD]
[TD]APPLE[/TD]
[TD="align: right"]1[/TD]
[TD]1APPLE[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD]2APPLE[/TD]
[TD="align: right"]5[/TD]
[TD]BBBB[/TD]
[TD="align: right"]5[/TD]
[TD]BBBB[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD="align: right"]8[/TD]
[TD]GGGG[/TD]
[TD]APPLE[/TD]
[TD="align: right"]1[/TD]
[TD]1APPLE[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD]2APPLE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD="align: right"]12[/TD]
[TD]HHHH[/TD]
[TD]PEAR[/TD]
[TD="align: right"]2[/TD]
[TD]2PEAR[/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD]2APPLE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD="align: right"]3[/TD]
[TD]IIII[/TD]
[TD]PEAR[/TD]
[TD="align: right"]1[/TD]
[TD]1PEAR[/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[TD]2APPLE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD="align: right"]2[/TD]
[TD]JJJJ[/TD]
[TD]PEAR[/TD]
[TD="align: right"]1[/TD]
[TD]1PEAR[/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[TD]2APPLE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[TD="align: right"]11[/TD]
[TD]KKKK[/TD]
[TD]PEAR[/TD]
[TD="align: right"]1[/TD]
[TD]1PEAR[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD]1PEAR[/TD]
[TD="align: right"]1[/TD]
[TD]EEEE[/TD]
[TD="align: right"]1[/TD]
[TD]EEEE[/TD]
[/TR]
[TR]
[TD="align: right"]13[/TD]
[TD="align: right"]9[/TD]
[TD]LLLL[/TD]
[TD]PEAR[/TD]
[TD="align: right"]2[/TD]
[TD]2PEAR[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD]1PEAR[/TD]
[TD="align: right"]2[/TD]
[TD]JJJJ[/TD]
[TD="align: right"]3[/TD]
[TD]IIII[/TD]
[/TR]
[TR]
[TD="align: right"]14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD]1PEAR[/TD]
[TD="align: right"]3[/TD]
[TD]IIII[/TD]
[TD="align: right"]2[/TD]
[TD]JJJJ[/TD]
[/TR]
[TR]
[TD="align: right"]15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[TD]1PEAR[/TD]
[TD="align: right"]11[/TD]
[TD]KKKK[/TD]
[TD="align: right"]11[/TD]
[TD]KKKK[/TD]
[/TR]
[TR]
[TD="align: right"]16[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[TD]1PEAR[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]17[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD]2PEAR[/TD]
[TD="align: right"]4[/TD]
[TD]AAAA[/TD]
[TD="align: right"]4[/TD]
[TD]AAAA[/TD]
[/TR]
[TR]
[TD="align: right"]18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD]2PEAR[/TD]
[TD="align: right"]6[/TD]
[TD]CCCC[/TD]
[TD="align: right"]6[/TD]
[TD]CCCC[/TD]
[/TR]
[TR]
[TD="align: right"]19[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD]2PEAR[/TD]
[TD="align: right"]9[/TD]
[TD]LLLL[/TD]
[TD="align: right"]12[/TD]
[TD]HHHH[/TD]
[/TR]
[TR]
[TD="align: right"]20[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[TD]2PEAR[/TD]
[TD="align: right"]12[/TD]
[TD]HHHH[/TD]
[TD="align: right"]9[/TD]
[TD]LLLL[/TD]
[/TR]
</tbody>[/TABLE]
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.
[TABLE="width: 871"]
<colgroup><col span="11"><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]Num.[/TD]
[TD]4 Digit[/TD]
[TD]Fruit[/TD]
[TD]Order #[/TD]
[TD]Concat[/TD]
[TD][/TD]
[TD]Helper[/TD]
[TD]Concat[/TD]
[TD]Expected Result[/TD]
[TD]Expected Result[/TD]
[TD]INDEX SMALL IF[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]4[/TD]
[TD]AAAA[/TD]
[TD]PEAR[/TD]
[TD="align: right"]2[/TD]
[TD]2PEAR[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD]1APPLE[/TD]
[TD="align: right"]7[/TD]
[TD]DDDD[/TD]
[TD="align: right"]7[/TD]
[TD]DDDD[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]5[/TD]
[TD]BBBB[/TD]
[TD]APPLE[/TD]
[TD="align: right"]2[/TD]
[TD]2APPLE[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD]1APPLE[/TD]
[TD="align: right"]8[/TD]
[TD]GGGG[/TD]
[TD="align: right"]10[/TD]
[TD]FFFF[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]6[/TD]
[TD]CCCC[/TD]
[TD]PEAR[/TD]
[TD="align: right"]2[/TD]
[TD]2PEAR[/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD]1APPLE[/TD]
[TD="align: right"]10[/TD]
[TD]FFFF[/TD]
[TD="align: right"]8[/TD]
[TD]GGGG[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]7[/TD]
[TD]DDDD[/TD]
[TD]APPLE[/TD]
[TD="align: right"]1[/TD]
[TD]1APPLE[/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[TD]1APPLE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: right"]1[/TD]
[TD]EEEE[/TD]
[TD]PEAR[/TD]
[TD="align: right"]1[/TD]
[TD]1PEAR[/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[TD]1APPLE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="align: right"]10[/TD]
[TD]FFFF[/TD]
[TD]APPLE[/TD]
[TD="align: right"]1[/TD]
[TD]1APPLE[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD]2APPLE[/TD]
[TD="align: right"]5[/TD]
[TD]BBBB[/TD]
[TD="align: right"]5[/TD]
[TD]BBBB[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD="align: right"]8[/TD]
[TD]GGGG[/TD]
[TD]APPLE[/TD]
[TD="align: right"]1[/TD]
[TD]1APPLE[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD]2APPLE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD="align: right"]12[/TD]
[TD]HHHH[/TD]
[TD]PEAR[/TD]
[TD="align: right"]2[/TD]
[TD]2PEAR[/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD]2APPLE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD="align: right"]3[/TD]
[TD]IIII[/TD]
[TD]PEAR[/TD]
[TD="align: right"]1[/TD]
[TD]1PEAR[/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[TD]2APPLE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD="align: right"]2[/TD]
[TD]JJJJ[/TD]
[TD]PEAR[/TD]
[TD="align: right"]1[/TD]
[TD]1PEAR[/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[TD]2APPLE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[TD="align: right"]11[/TD]
[TD]KKKK[/TD]
[TD]PEAR[/TD]
[TD="align: right"]1[/TD]
[TD]1PEAR[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD]1PEAR[/TD]
[TD="align: right"]1[/TD]
[TD]EEEE[/TD]
[TD="align: right"]1[/TD]
[TD]EEEE[/TD]
[/TR]
[TR]
[TD="align: right"]13[/TD]
[TD="align: right"]9[/TD]
[TD]LLLL[/TD]
[TD]PEAR[/TD]
[TD="align: right"]2[/TD]
[TD]2PEAR[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD]1PEAR[/TD]
[TD="align: right"]2[/TD]
[TD]JJJJ[/TD]
[TD="align: right"]3[/TD]
[TD]IIII[/TD]
[/TR]
[TR]
[TD="align: right"]14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD]1PEAR[/TD]
[TD="align: right"]3[/TD]
[TD]IIII[/TD]
[TD="align: right"]2[/TD]
[TD]JJJJ[/TD]
[/TR]
[TR]
[TD="align: right"]15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[TD]1PEAR[/TD]
[TD="align: right"]11[/TD]
[TD]KKKK[/TD]
[TD="align: right"]11[/TD]
[TD]KKKK[/TD]
[/TR]
[TR]
[TD="align: right"]16[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[TD]1PEAR[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]17[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD]2PEAR[/TD]
[TD="align: right"]4[/TD]
[TD]AAAA[/TD]
[TD="align: right"]4[/TD]
[TD]AAAA[/TD]
[/TR]
[TR]
[TD="align: right"]18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD]2PEAR[/TD]
[TD="align: right"]6[/TD]
[TD]CCCC[/TD]
[TD="align: right"]6[/TD]
[TD]CCCC[/TD]
[/TR]
[TR]
[TD="align: right"]19[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD]2PEAR[/TD]
[TD="align: right"]9[/TD]
[TD]LLLL[/TD]
[TD="align: right"]12[/TD]
[TD]HHHH[/TD]
[/TR]
[TR]
[TD="align: right"]20[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[TD]2PEAR[/TD]
[TD="align: right"]12[/TD]
[TD]HHHH[/TD]
[TD="align: right"]9[/TD]
[TD]LLLL[/TD]
[/TR]
</tbody>[/TABLE]