# Combine multiple list while removing spaces in between and keeping duplicates

#### keenetics

##### New Member
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!

### Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
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

Replies
6
Views
539
Replies
0
Views
324
Replies
12
Views
823
Replies
0
Views
938
Replies
10
Views
1K

Threads
1,218,909
Messages
6,145,166
Members
450,592
Latest member
Boulder127

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back