Remove spaces from index/match list

[cpclarke]

Hello All,
I have a table "CompanyTBL" (A2:B10) and a Named List "TradeLST" (B2:B10). I want to collate a list of company names (the green coloured list) that match the criteria (as shown below). The function shown in inserted into the green list D2 etc. and copied to all cells. It returns the correct values but I want to exclude the "null" values (spaces) in the list. This list will be used later by another dependent dropdown selection so don't want any empty cells. How do modify my function to remove the null cell values and group the returned values to the top of each column? Any help much appreciated.

=IF(COUNTIF(INDEX(CompanyTBL,MATCH($B2,TradeLST,0),2),D$1),$A2,"")

 

[Marcelo Branco]

Try

Formula in D2 copied across until H2 and down
=IFERROR(INDEX($A$2:$A$10,AGGREGATE(15,6,(ROW($A$2:$A$10)-ROW($A$2)+1)/($B$2:$B$10=D$1),ROWS(D$2:D2))),"")

Hope this helps

M.

 

[cpclarke]

Marcelo,
I don't understand your black magic yet, buy your formula worked! I will have to unpack and digest it later but this gets a job off my desk. Many, many thanks to you.

Phil

 

[Marcelo Branco]

Phil,
You're welcome. Thanks for the feedback.

M.