Automatic Data sorting/ analysis in Excel

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 fou开发者_高级运维r 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.

---

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

---

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

---

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

---

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

---

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)

---

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.