Combining multiple collumns to create a single consolidated list

[jonjonjonjon]

Hi guys,

Is there a way to pick out cells that contain text from multiple columns and aggregate them into one single list?

eg: Problem:

red		circle		table
		square		book
Blue		triangle		pencil
green

<tbody>
</tbody>

Result needed:

red

Blue

green

circle

square

triangle

table

book

pencil

<tbody>
</tbody>

 

[XOR LX]

Hi,

Based on (non-numeric) data in A1:E5, array formula**:

=IF(ROWS($1:1)>COUNTIF($A$1:$E$5,"?*"),"",INDIRECT(TEXT(SMALL(IF($A$1:$E$5<>"",10^5*ROW($A$1:$E$5)+COLUMN($A$1:$E$5)),ROWS($1:1)),"R0C00000"),0))

and copied down until you start to get blanks for the results.

Slightly more complex set-up if you insist on the returns being in order of their respective column positions, as opposed to row positions which the above favours.

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

 

[jonjonjonjon]

Grazie! wow! thanks so much!

Much appreciated!

 

[jonjonjonjon]

ah sorry one more question!

What happens if the columns are separated with additional information that does not need to be taken into account?

Many thanks!

 

[jonjonjonjon]

so for example:

RED yes Car
BLUE No house

To give just column 1 and 3 results:

RED
BLUE
Car
House

 

[XOR LX]

What happens if the columns are separated with additional information that does not need to be taken into account?

Will there only be one group of column(s) to be excluded, or several?

Regards

 

[jonjonjonjon]

Hi,

The targeted columns will be separated by several other irrelevant columns and are across multiple worksheets. So essentially taking specific columns across multiple worksheets and combining them into one list.

Kind Regards

 

[XOR LX]

So you might have 2 columns from one worksheet, 3 from another, 10 from another, etc., all potentially separated within their respective worksheets by several columns?

Regards

 

[jonjonjonjon]

That's exactly right.

That said, note that the "separated by several columns" will be a constant number of columns, say 6. That is, the layout of each sheet is exactly the same - in each sheet, so i want columns 3, 9, 15, so on and so forth (so a difference of 6 columns).

Regards

 

[XOR LX]

This is quite straightforward with VBA. Using worksheet formulas alone it is doable, though a touch complex.

What's your preference?

Regards