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.

migrated from stackoverflow.com Jun 22 '13 at 7:56

This question came from our site for professional and enthusiast programmers.

  • 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
up vote 1 down vote accepted

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.

Your Answer

 

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.