Convert multiple columns of data (variable length) into one single long column (ignore blanks)

Question

Hi chaps,

I have two columns of data extracted from a webpage (data in range N202:O5000). The data can vary in length, may contain the odd blank cell and usually doesn't fill the whole range (many blanks at the bottom).

	N	O		AM
201
202	Date	label		Date
203	2013-04-08T	Blue		2013-04-08T
204	Value			Value
205		id		1150
206	1150	3		label
207				20001523
208	label			label
209	20001523			Blue
210				id
211				3
212

Using formulas (in AM202:AM10000) I want to extract all data from the 1st column (N202:N5000) (ignoring any blank cells), and place it in AM202 downwards. Once I've extracted all values from N202:N5000, I then want to extract all values from O202:O5000, placing these again in column AM, directly below the extracted values from column N202:N5000 (with no gaps).

A formula based solution would be great. I tried examples to convert tables to column etc but these all have defined data extents and don't address ignoring the blank cells.

Many thanks for looking,

Wessex.

Answer 1

Hi Wessex,

Instead of formula, you can copy these in AM column in 202 row onwards one after another, that is first copy N202:N5000 and then below that capy O202:O5000 and follow below steps:

• After copying the data, select all the data in AM column below 202 and below
• Click on F5
• Click on Special
• Select "Blanks" and click on Ok. This will select all the blank columns in the selected range.
• Click on any selected cell and right click > Delete...
• Select Shift cells up.

Hope this helps. I am not sure if this can be done using formula, but can be used as a workaround without formula.

Regards,

Nikhil Baheti.