How to convert a SMALL and FREQUENCY formula to AGGREGATE (15) without CTRL+SHIFT+ENTER

[GerryZ]

hello everybody!
you can see here a very difficult formula that extract a unique list of items display horizzontaly
I need to convert this array formula without making CTRL+SHIF+ENTER maybe with the function AGGREGATE.
Is it possibile?
thank you very much in advance!!



{=IFERROR(INDEX($C2:$L2,SMALL(IF(TRANSPOSE(FREQUENCY(IF($C2:$L2<>"",MATCH($C2:$L2,$C2:$L2,0)),COLUMN($C2:$L2)-COLUMN($C2)+1)),COLUMN($C2:$L2)-COLUMN($C2)+1),COLUMNS($C11:C11))),"")}

 

[pgc01]

You're welcome.

I didn't understand very well this formula expeciallly the function LEN but works without doing CTRL+SHIF+ENTER

I divide by (Len()<>0) so that it creates an error when the cell is empty or has a null string.

Thank you but I dont't know how to use VBA
do you have any links that I can see how to do it?

I hope this introduction will help

Excel VBA Programming - a free course for complete beginners

 

[Domenic]

@Gerry

That's because you've missed both the "~" and "" within the MATCH function.

@pgc

I really like your solution. Nice one!

 

[pgc01]

@pgc

I really like your solution. Nice one!

Thank you Domenic. I guess both my 2 neurons were working.