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

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
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
191
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
217
DanteAmor
DanteAmor
U
  • Solved
Count Number of Times Values appear in a Column and Rank top 3
Replies
5
Views
199
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
114
sijpie
sijpie
JenniferMurphy
  • Question Question
Is this a good solution to Excel's table header weaknesses?
Replies
3
Views
301
zero269
Z

Forum statistics

Threads
1,223,911
Messages
6,175,337
Members
452,637
Latest member
Ezio2866

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