converting a multicolumn matrix to a single column omitting blanks

[Riesenrind]

I have a matrix with a lot of blanks. I need to convert them with a matrix formular to a single column with no blanks. Can anyone help?

Thanks in advance,
Andrew

 

[Robert Mika]

I have a matrix with a lot of blanks. I need to convert them with a matrix formular to a single column with no blanks. Can anyone help?

Thanks in advance,
Andrew

Look here:

http://www.mrexcel.com/forum/excel-...ll-extract-table-into-single-column-list.html
Otherwise post your sample
Which Excel version are you using?

 

[Riesenrind]

Hi Robert,
Thanks for that. Howerver I really need a formular to do it, rather than a macro. Do you happen to know how to convert that to a matrix formular, where I can use a range as input?
A.

 

[Robert Mika]

Hi Robert,
Thanks for that. Howerver I really need a formular to do it, rather than a macro. Do you happen to know how to convert that to a matrix formular, where I can use a range as input?
A.

Scroll down in the provided link and you will find two formulas.
Otherwise post your data here.

 

[Riesenrind]

Hi Robert,

Thanks a lot for your help so far:

This is what my data looks like (see below).
I need to extract the IDs in E2:H7 (see below)
I was hoping to find a nice VBA function like Function MatrixToCol(InputValues As Variant) As Variant

I tried to adapt the formulas posted in the link you gave me, but so far I have not suceeded in getting a useful result.
[TABLE="width: 712"]
<tbody>[TR]
[TD="colspan: 2"]C[/TD]
[TD]D[/TD]
[TD][/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD="colspan: 2"]Product Name on with "Local"[/TD]
[TD]Local[/TD]
[TD][/TD]
[TD]Non-Local[/TD]
[TD]UK[/TD]
[TD]US[/TD]
[TD]AU[/TD]
[/TR]
[TR]
[TD]m_Billshock[/TD]
[TD][/TD]
[TD="align: center"]FALSE[/TD]
[TD][/TD]
[TD]m_Billshock[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]hoRm_Loc_MS[/TD]
[TD][/TD]
[TD="align: center"]TRUE[/TD]
[TD][/TD]
[TD][/TD]
[TD]hoRm_UK_MS[/TD]
[TD]hoRm_US_MS[/TD]
[TD]hoRm_AU_MS[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: center"]FALSE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]b_EssPro[/TD]
[TD][/TD]
[TD="align: center"]FALSE[/TD]
[TD][/TD]
[TD]b_EssPro[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]b_IndTruCom_4[/TD]
[TD][/TD]
[TD="align: center"]FALSE[/TD]
[TD][/TD]
[TD]b_IndTruCom_4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]b_IndLocCom_1[/TD]
[TD][/TD]
[TD="align: center"]TRUE[/TD]
[TD][/TD]
[TD][/TD]
[TD]b_IndUKCom_1[/TD]
[TD]b_IndUSCom_1[/TD]
[TD]b_IndAUCom_1[/TD]
[/TR]
</tbody>[/TABLE]

Cheers,
Andrew