Array to put results in one column in to one cell

[Karnik]

Hi Experts,
Would appreciate any suggestion for array to put 1 to 4 cells (max) results from one column in to one cell.

TOTAL No. Rank
10 1 4
20 1
30 1 2
40 1 1

Last column results to put in one cell as 4,2,1, or 421

Attached sheet may assist clearly
Thanks in advance
Karnik

[Glenn Kennedy]

This formula

=concatall(IF($C$3:$C$22=G2,IF($D$3:$D$22<>"",$D$3:$D$22,""),""),"")

array entered, along with this code in a module:

Please Login or Register  to view this content.

does that.


Array Formulae are a little different from ordinary formulae in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

Remember to enable macros on opening...

[Glenn Kennedy]

Nope. forget all that. use this array formula instead: No need for VBA. It gives the results concatenated and in ascending order.

=CONCATENATE(IFERROR(SMALL(IF(($C$3:$C$22=G2)*($D$3:$D$22> 0),$D$3:$D$22),1),""),IFERROR(SMALL(IF(($C$3:$C$22=G2)*($D$3:$D$22> 0),$D$3:$D$22),2),""),IFERROR(SMALL(IF(($C$3:$C$22=G2)*($D$3:$D$22> 0),$D$3:$D$22),3),""),IFERROR(SMALL(IF(($C$3:$C$22=G2)*($D$3:$D$22> 0),$D$3:$D$22),4),""),"")

[Karnik]

Hi Glen,
Thanks for Solution, complex issue is made very simple
Thanks
Karnik

[Glenn Kennedy]

You're welcome and thanks for the rep...