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

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
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
174
TheRedCardinal
T
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
181
DanteAmor
DanteAmor
U
  • Solved
Count Number of Times Values appear in a Column and Rank top 3
Replies
5
Views
190
Fluff
Fluff
S
  • Question Question
VBA code for adding new group of columns with 3 condictions from 2 dictionary and 1 array Then fill in data from value from original columns
Replies
1
Views
89
sijpie
sijpie
JenniferMurphy
  • Question Question
Is this a good solution to Excel's table header weaknesses?
Replies
3
Views
285
zero269
Z

Forum statistics

Threads
1,223,270
Messages
6,171,102
Members
452,379
Latest member
IainTru

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