Combine multiple list while removing spaces in between and keeping duplicates

[keenetics]

Hi, I've been trying to come up with a formula that combines multiple list while removing spaces in between and keeping duplicates but to no success. Is there any formula to do this?

E.g.

List1	List2	List3	Combined
Apple	Apple		Apple
Paper			Paper
			Food
Food			Apple
		Apple	Orange
		Tomato	Apple
	Orange		Tomato

<tbody>
</tbody>

Thanks!

 

[jbeaucaire]

This answer is LEAST confusing without the titles in row1. Put the list1, list2 and list3 in A1:A7, B1:B7, C1:C7, the enter this ARRAY formula in D1:

=IF(ROW()<=COUNTA($A$1:$A$7), INDEX($A$1:$A$7, SMALL(IF($A$1:$A$7<>"", ROW($A$1:$A$7), ""), ROW())),
IF(ROW()<=COUNTA($A$1:$B$7), INDEX($B$1:$B$7, SMALL(IF($B$1:$B$7<>"", ROW($B$1:$B$7), ""), ROW()-COUNTA($A$1:$A$7))),
IF(ROW()<=COUNTA($A$1:$C$7), INDEX($C$1:$C$7, SMALL(IF($C$1:$C$7<>"", ROW($C$1:$C$7), ""), ROW()-COUNTA($A$1:$B$7))), "")))

...confirm that formula by pressing CTRL+SHIFT+ENTER to activate the array. You will know the array is active when curly braces { } appear around your formula.

Now copy D1 downward.

2014-09-12_0909 - JerryBeaucaire's library