Creating a subset in Excel

How would you go about creating a subset in a new column using built in excel functions (not vba if possible). I have prevously created a column of data points that are either any number kept in the subset or a 0 which will be removed from the subset. The concept would be similar to using a filter, but using a filter is not an option because the subset column needs to be created automatically. The subset must not have any blank rows between the data. For example column 1 (the original set) and column 2 (the subset):

Column 1      Column 2
5                        5
1                        1
0                        4
4                        3
0                        2
3
2

Thanks in advance.

2 Answers

This would be really easy in VBA, and require no intervention from the user other than selecting the range of cells from which to create a sub-list, and identifying which column you want the output to appear in:

Sub CreateSubSetList() Dim rng As Range: Set rng = Application.InputBox("Select the column which contains your list", "Select column", Type:=8) Dim colNum As Variant: colNum = Application.InputBox("Input the destination column letter", "Destination Column?") Dim oRng As Range Dim cl As Range Dim c As Long: c = 1 If rng Is Nothing Then Exit Sub Set rng = rng.Columns(1) Set oRng = Range(Columns(colNum).Address).Columns(1) If oRng Is Nothing Then Exit Sub For Each cl In rng.Cells If Not cl.Value = 0 Then oRng.Cells(c).Value = cl.Value c = c + 1 End If Next End Sub 

Assuming column 1 data is in A1:A7, then Ctrl+Shift+Enter following formula over complete range B1:B7, or cse and drag down.

=IFERROR(INDEX($A$1:$A$7,SMALL(IF($A$1:$A$7<>0,ROW($A$1:$A$7)),ROW()),),""). 

Note iferror only works from Excel 2007 onwards.