Creating Column from non-blanks in table

I

Innoguide

Board Regular
Joined
Sep 13, 2011
Messages
159
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!

[TABLE="width: 448"]
<colgroup><col width="64" span="7" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl65, width: 64"]A[/TD]
[TD="class: xl65, width: 64"]B [/TD]
[TD="class: xl65, width: 64"]C[/TD]
[TD="class: xl65, width: 64"]D[/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl65, width: 64"][/TD]
[/TR]
[TR]
[TD="class: xl65"]1[/TD]
[TD]Header 1[/TD]
[TD]Header 2[/TD]
[TD]Header 3[/TD]
[TD]Header 4[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]Result[/TD]
[/TR]
[TR]
[TD="class: xl65"]2[/TD]
[TD="class: xl66"]Joe[/TD]
[TD="class: xl66"]Jeanie[/TD]
[TD="class: xl66"]Helle[/TD]
[TD="class: xl66"]Kristen[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl66"]Joe[/TD]
[/TR]
[TR]
[TD="class: xl65"]3[/TD]
[TD="class: xl66"]Ralph[/TD]
[TD="class: xl66"]Mary[/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl65"][/TD]
[TD="class: xl66"]Ralph[/TD]
[/TR]
[TR]
[TD="class: xl65"]4[/TD]
[TD="class: xl66"]Sue[/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl65"][/TD]
[TD="class: xl66"]Sue[/TD]
[/TR]
[TR]
[TD="class: xl65"]5[/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl65"][/TD]
[TD="class: xl66"]Jeanie[/TD]
[/TR]
[TR]
[TD="class: xl65"]6[/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"]Biff[/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl65"][/TD]
[TD="class: xl66"]Mary[/TD]
[/TR]
[TR]
[TD="class: xl65"]7[/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl65"][/TD]
[TD="class: xl66"]Biff[/TD]
[/TR]
[TR]
[TD="class: xl65"]8[/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl65"][/TD]
[TD="class: xl66"]Helle[/TD]
[/TR]
[TR]
[TD="class: xl65"]9[/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl65"][/TD]
[TD="class: xl66"]Kristen[/TD]
[/TR]
[TR]
[TD="class: xl65"]10[/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl65"][/TD]
[TD="class: xl66"] [/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Sort by date Sort by votes
You may use for instance such code:


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
 
Upvote 0
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


Kaper said:
You may use for instance such code:


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
Click to expand...
 
Upvote 0
Try:
Code: 
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
 
Upvote 0
Thanks for your help!

Kaper said:
Try:
Code: 
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
Click to expand...
 
Upvote 0
Innoguide said:
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
Click to expand...

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
 
Upvote 0
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.
 
Upvote 0
Innoguide said:
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.
Click to expand...

You are welcome.

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

Markmzz
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Solved
Can I sort a table by applying columns in a different order to the sort request?
Replies
5
Views
204
TheRedCardinal
T
V
  • Solved
Is it possible to sort table column values and display the header value in the next set of table columns
Replies
7
Views
138
Peter_SSs
Peter_SSs
ExcelNewbie2020
  • Solved
extract the header of sets of non empty cell
Replies
3
Views
100
ExcelNewbie2020
ExcelNewbie2020
S
  • Question Question
Macro to compare two sheets with identical column headers and first column, then showing all table differences in third sheet
Replies
1
Views
303
DanteAmor
DanteAmor
U
  • Solved
Count Number of Times Values appear in a Column and Rank top 3
Replies
5
Views
203
Fluff
Fluff

Forum statistics

Threads
1,224,828
Messages
6,181,201
Members
453,022
Latest member
RobertV1609

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top