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.
