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.
3
-
Do you want to keep the 0 value for those rows, or do you just want to remove the whole row? In that case, is sorting by the fourth column and removing those rows by hand an option?poke– poke2010-05-19 19:05:07 +00:00Commented May 19, 2010 at 19:05
-
@poke I want to remove the entire row. But since I have time data I cannot sort it. Specifically I have chunks of different time data. start1 to finish1... start2 ..finish2 etc..hkf– hkf2010-05-19 19:29:55 +00:00Commented May 19, 2010 at 19:29
-
just so you know, if you sort by the binary column, it wont change the relative order of the date columns. in this respect, you could very easily just sort by the fourth column and delete the offending rows. The macro solution below would be useful if you need to do this programmatically or really cannot sortSprague– Sprague2010-05-19 20:01:55 +00:00Commented May 19, 2010 at 20:01
Add a comment
|
6 Answers
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
Comments
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
3 Comments
hkf
Ok. great! But now how do I get rid of all those blank spaces. If I have to do it manually then that defeats the entire purpose!
corsiKa
Off the top of my head, try copying it, and pasting special to remove empty rows. You can then put 1's at the end if you need to, since you know they all have it. It's not "auto-magic" but it's snappy enough.
Richard Morgan
Here's a question on reducing sparsely populated cells which may help: stackoverflow.com/questions/32029/…
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
Comments
You could use a filter to show only the useful rows. Use an additional field to compute if the row has to be shown.
Comments
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)
Comments
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.
