Remove duplicate and blank with formula

[ssimo2]

Hello, I need to create a formula to extract data from a table, depending on two parameter.
I use two combo to select parameters, but I need to have into combo only different values, without blank space.

Into attached file the example problem, I want to remove duplicate from Data1 and shows into List1 and select values from Data2, removing blank and shows into List2.

Ending... I don't want to use Macro.

Any suggestions?
Thanks a lot

[benishiryo]

hi ssimo2, welcome to the forum. try this array formula in H2:

Please Login or Register  to view this content.

you have to paste the formula inside the formula bar & press CTRL + SHIFT + ENTER

change all range A2:A8 to B2:B8 when you want to use it in N2

[Andrew-R]

In H2 (and copied down):

=IF(ROW(A1)>SUM(--(MATCH($A$2:$A$8,A:A,0)=ROW($A$2:$A$8))),"",INDEX(A:A,LARGE(INDEX(IF(MATCH($A$2:$A$8,A:A,0)=ROW($A$2:$A$8),ROW($A$2:$A$8)),0),SUM(--(MATCH($A$2:$A$8,A:A,0)=ROW($A$2:$A$8)))-(ROW(A1)-1))))

This is an array formula and must be entered with Ctrl-Shift-Enter, not just enter.

Likewise, in N2:

=IF(ROW(A1)>SUM(--(MATCH($B$2:$B$8,B:B,0)=ROW($B$2:$B$8))),"",INDEX(B:B,LARGE(INDEX(IF(MATCH($B$2:$B$8,B:B,0)=ROW($B$2:$B$8),ROW($B$2:$B$8)),0),SUM(--(MATCH($B$2:$B$8,B:B,0)=ROW($B$2:$B$8)))-(ROW(A1)-1))))

Also confirmed with Ctrl-Shift-Enter

[ChemistB]

A non-arrayed approach;
First I created two keys (col F and G which can be hidden later) to count and uniquely identify values in col A and B. For example in F2 copied down;

=IF(COUNTIF($A$2:A2,A2)=1,"D1_"& COUNTIF($F$1:F1,"D1*")+1,"")

Then in I2 copied down (pulls values from A based on identifiers in F)

=IF(ISERROR(MATCH("D1_"&ROW(A1),$F$2:$F$8,0)),"",INDEX($A$2:$A$8,MATCH("D1_"&ROW(A1),$F$2:$F$8,0)))
See attachment

[ssimo2]

Thanks a lot to all, I've solved using formula suggested by benishiryo!

Moreover, to dinamically select data into list2, according to selection in list1, i've added a column to show only right values.

Attached the result file.

Thanks to all!