Creating Column from non-blanks in table

[Innoguide]

I have a table structured as below with names in some of the cells and blanks in others and I need to create a results column that shows only the non-blank values. The actual data set is 10+ columns wide and 100 long. Also, it's important to first list all the data from column 1, then column 2 and so on

Any help would be greatly appreciated!

	A	B	C	D
1	Header 1	Header 2	Header 3	Header 4		Result
2	Joe	Jeanie	Helle	Kristen		Joe
3	Ralph	Mary				Ralph
4	Sue					Sue
5						Jeanie
6		Biff				Mary
7						Biff
8						Helle
9						Kristen
10

<colgroup><col width="64" span="7" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[Kaper]

You may use for instance such code:

Sub foo()
Dim i&, lastcolumn&, lastrow&
lastcolumn = Cells(1, Columns.Count).End(xlToLeft).Column
Cells(1, lastcolumn + 2) = "Result"
For i = 1 To lastcolumn
  Range(Cells(2, i), Cells(Rows.Count, i).End(xlUp)).Copy Cells(Rows.Count, lastcolumn + 2).End(xlUp).Offset(1, 0)
Next i
lastrow = Cells(Rows.Count, lastcolumn + 2).End(xlUp).Row
Range(Cells(1, lastcolumn + 2), Cells(lastrow, lastcolumn + 2)).AutoFilter field:=1, Criteria1:=""
Range(Cells(2, lastcolumn + 2), Cells(lastrow + 1, lastcolumn + 2)).SpecialCells(xlCellTypeVisible).Delete shift:=xlUp
ActiveSheet.AutoFilterMode = False
End Sub

 

[Innoguide]

Thanks Kaper

How would the macro change if I only wanted to include columns A-H, the header is in row 4 (data starting in row 5) and the results need to show in column K.

Also - if anyone has any thoughts on how to do this with a formula looking at the table/array I'd be interested in that as well

You may use for instance such code:

Sub foo()
Dim i&, lastcolumn&, lastrow&
lastcolumn = Cells(1, Columns.Count).End(xlToLeft).Column
Cells(1, lastcolumn + 2) = "Result"
For i = 1 To lastcolumn
  Range(Cells(2, i), Cells(Rows.Count, i).End(xlUp)).Copy Cells(Rows.Count, lastcolumn + 2).End(xlUp).Offset(1, 0)
Next i
lastrow = Cells(Rows.Count, lastcolumn + 2).End(xlUp).Row
Range(Cells(1, lastcolumn + 2), Cells(lastrow, lastcolumn + 2)).AutoFilter field:=1, Criteria1:=""
Range(Cells(2, lastcolumn + 2), Cells(lastrow + 1, lastcolumn + 2)).SpecialCells(xlCellTypeVisible).Delete shift:=xlUp
ActiveSheet.AutoFilterMode = False
End Sub

 

[Kaper]

Try:

Sub foo2()
Dim i&, lastcolumn&, lastrow&
lastcolumn = Cells(1, "H").Column
Cells(4, "K") = "Result"
For i = 1 To lastcolumn
  Range(Cells(5, i), Cells(Rows.Count, i).End(xlUp)).Copy Cells(Rows.Count, "K").End(xlUp).Offset(1, 0)
Next i
lastrow = Cells(Rows.Count, "K").End(xlUp).Row
Range(Cells(4, "K"), Cells(lastrow, "K")).AutoFilter field:=1, Criteria1:=""
Range(Cells(5, "K"), Cells(lastrow + 1, "K")).SpecialCells(xlCellTypeVisible).Delete shift:=xlUp
ActiveSheet.AutoFilterMode = False
End Sub

 

[Innoguide]

Thanks for your help!

Try:

Sub foo2()
Dim i&, lastcolumn&, lastrow&
lastcolumn = Cells(1, "H").Column
Cells(4, "K") = "Result"
For i = 1 To lastcolumn
  Range(Cells(5, i), Cells(Rows.Count, i).End(xlUp)).Copy Cells(Rows.Count, "K").End(xlUp).Offset(1, 0)
Next i
lastrow = Cells(Rows.Count, "K").End(xlUp).Row
Range(Cells(4, "K"), Cells(lastrow, "K")).AutoFilter field:=1, Criteria1:=""
Range(Cells(5, "K"), Cells(lastrow + 1, "K")).SpecialCells(xlCellTypeVisible).Delete shift:=xlUp
ActiveSheet.AutoFilterMode = False
End Sub

 

[markmzz]

Also - if anyone has any thoughts on how to do this with a formula looking at the table/array I'd be interested in that as well

Maybe the suggestion below (with names and formula) can help you:

1) Create the names below:

Data - Refers to: =Sheet1!$A$5:$H$104
ColsRows - Refers to: =IF(Data<>"",(COLUMN(Data)-COLUMN(INDEX(Data,1,1))+1)*10^7+ROW(Data)-ROW(INDEX(Data,1,1))+1)

2) Enter the formula below in K5 and copy down:

=IFERROR(INDEX(Data,
MOD(SMALL(ColsRows,ROWS(K$5:K5)),10^7),
INT(SMALL(ColsRows,ROWS(K$5:K5))/10^7)),"")

I hope this helps.

Markmzz

 

[Innoguide]

Thanks Markmzz -
The formula worked perfectly and I even learne that you can base named ranges on a formula (similar as to data validation) so very helpful.

 

[markmzz]

Thanks Markmzz -
The formula worked perfectly and I even learne that you can base named ranges on a formula (similar as to data validation) so very helpful.

You are welcome.

I'm happy that the formula help you and thanks for the feedback.

Markmzz