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.

C	D		E	F	G	H
Product Name on with "Local"	Local		Non-Local	UK	US	AU
m_Billshock		FALSE		m_Billshock
hoRm_Loc_MS		TRUE			hoRm_UK_MS	hoRm_US_MS	hoRm_AU_MS
		FALSE
b_EssPro		FALSE		b_EssPro
b_IndTruCom_4		FALSE		b_IndTruCom_4
b_IndLocCom_1		TRUE			b_IndUKCom_1	b_IndUSCom_1	b_IndAUCom_1

<tbody>
</tbody>

Cheers,
Andrew