Combine multiple list while removing spaces in between and keeping duplicates

keenetics

New Member
Joined
Jun 23, 2013
Messages
3
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!
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

jbeaucaire

Well-known Member
Joined
May 8, 2002
Messages
6,012
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
 

Watch MrExcel Video

Forum statistics

Threads
1,108,624
Messages
5,523,967
Members
409,547
Latest member
AW2020

This Week's Hot Topics

Top