3

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.

  • 1
    You need VBA for this – isJustMe Jun 21 '13 at 17:40
  • If you are creating the Column1, why don't you just modify whatever you are doing to simply not create zero values? I'm sure there is a way to do something like this with Formula, but it would be a lot easier in VBA. Also, this is a programming site, so if you are specifically looking for function hacks, I'd suggest posting this at SuperUser.com instead. – David Zemens Jun 21 '13 at 17:40
  • In case it is useful To delete all the cells in the first column containing 0: Highlight the data and press Ctrl-H for Replace; type 0 but leave the replace box empty, then Replace All; highlight the data in the column again and use Ctrl-G (GoTo), click Special and find all blanks. In Excel 2007/10 choose the Home Tab, Cells Group, Delete, and Delete Cells, Shift Cells up. As others have mentioned, I don't think a formula suitable - after all, your description suggests that you need to actually remove the values. – Andrew Gibson Jun 21 '13 at 17:53
1

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
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy