Automatic Data sorting/ analysis in Excel

Question

I have data in the form of four columns. The first three columns represent time, value1, value 2. The fourth column is binary, all 0's or 1's. Is there a way to tell excel to delete time, value1 and value 2, when the corresponding binary value in column four is 0? I know this is a lot easier in C++ or matlab, but for reasons beyond my control, I must do it in excel.

Answer 1

You could use a filter to show only the useful rows. Use an additional field to compute if the row has to be shown.

Answer 2

well you could do this to make it show up blank, then manipulate the new data:

        A                      B                 C                  D
=if(D1=0,"",time1)  =if(D1=0,"",value1)   =if(D1=0,"",value2)       0

Answer 3

As another alternative, you could put a fifth column in as 1,2,3,4, etc and then sort by column D (binary), then by column E (ordering number)

Answer 4

You need to use macros to do this, formulas have no ability to remove columns.

I hate writing Excel macros, but the pseudo code looks something like this:

declare row_marker = 1
while (Workbook.Cells(1, row_marker) contains data)
 if (Workbook.cells(1, rowmarker).OffsetRight(3).Value == 0)
   remove_row(workbook.rows(row_marker)
 else
   row_marker++;

What this is saying is start at row 1, if that row has 0 in the binary column, delete it, if not, move to the next row. Note: we aren't moving to the next row if we delete a row because the row moves up with us, since we're not referencing the row but it's offset in the workbook.

Answer 5

My Visual Basic is a bit rusty, but this macro will do it. Just fire up the VBA editor (Shift+F11), insert a new module (if there isn't one), and add the following code. Then make sure that the table you want to clean is on the active sheet go somewhere inside of the code and press the run button (F5) in the VBA editor. Then all lines with 0 in the D column should be gone.

Sub clean()
    Dim i As Integer
    i = 1
    While Range("D" & i).Value <> ""
        If Range("D" & i).Value = "0" Then
            Rows(i).Delete Shift:=xlUp
        Else
            i = i + 1
        End If
    Wend
End Sub

Answer 6

Wheter by hand or by vba, the quickest path is to sort on col.4, find the 1st zero, and delete everything below.
Something like:

    Sub test()
        Dim c As Range
        Range("a1").CurrentRegion.Sort key1:=Cells(1, 4), XlSortOrder:=xlDescending
        Set c = Range("4:4").Find("0")
        Range(c.Row & ":65000").Delete    'or .Clear
    End Sub