Combine multiple columns of data into one column

Hi,
I'm looking for a way to combine (not concatenate) the data from 5 columns in a single column. In other words, if I had 5 rows of data in column A, 3 rows of data in column B, 11 rows of data in column C, 8 rows of data in column D and 9 rows of data in column E, I want column F to have the 36 values (5+3+11+8+9 = 36) from the other columns.

The data has significantly more rows than I've used in my example and cutting and pasting is getting a little tedious every time I need to combine the data.

The data starts in row 3 for each column and any given row has no more than 258 values.
There are no intermingling blank cells in a given column; the blanks are at the end of each column.
The data is text, not numeric

(Back story: The data in columns A-E is generated using array formulas that pulls data from other worksheets. I need to combine those columns in to one, then remove duplicate values and sort the resulting combined column. The removing duplicates and sorting part I can handle; it's the combining part I'm having problems with. I was about to drop into VBA to solve this but since when it comes to writing formulas I'm a noob, I thought that I check here first. I saw this thread but no one solved it.)

Any help would greatly appreciated!
Thanks!
Paul

This thread shows how to combine 2 and 3 lists into a single list.

See if you can adapt it for your 5 lists.

Originally Posted by PMBottas

I think that VBA is going to be the way to go ...

I agree!

Good luck!

Hi,

I can give you a single formula which will work for any number of columns, though it would be useful to first know the following:

1) Are there ever any blank cells in between the data in any of the columns in question? (In your attached file there aren't, though that doesn't mean that it's not a possibility.)

2) If there are any such blank cells, are these cells "genuinely" blank, or do they perhaps contain formulas in them which may result in ""?

Regards

Here's another possibility.

I just reread the original post and it appears you want to end up with just the unique values from the combined lists.

See if this does what you want.

Data Range

	A	B	C	D	E	F
1	List1	List2	List3	------	Count	Uniques
2	A	A	B		9	A
3	B	C	D			B
4	D	E	E			C
5	F	G	G			D
6		H				E
7			I			F
8						G
9						H
10						I
11

Enter this array formula** in E2. This will return the count of the unique entries in the range.

=SUM(IF(A2:C7<>"",1/COUNTIF(A2:C7,A2:C7)))

Enter this array formula** in F2:

=IF(ROWS(F$2:F2)<=E$2,INDEX(A$2:C$7,MIN(IF(A$2:C$7<>"",IF(ISNA(MATCH(A$2:C$7,F$1:F1,0)),ROW(A$2:C$7)-ROW(A$2)+1))),MOD(MIN(IF(A$2:C$7<>"",IF(ISNA(MATCH(A$2:C$7,F$1:F1,0)),(ROW(A$2:C$7)-ROW(A$2)+1)*10^5+(COLUMN(A$2:C$7)-COLUMN(A$2)+1)))),10^5)),"")

Copy down until you get blanks.

Still a pretty hefty formula but might be a bit shorter than the other formula.

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

The formula references cell F1. Cell F1 must not contain an entry that is also within the data range. Typically, cell F1 would be a column header.