I have a spreadsheet with data from 4 sensors that needs to be cleaned up. There are a lot of frames with dropped sensors and I need to delete incomplete frame sets (in other words, delete those frames that don't have data for all 4 sensors).
Column A is the sensor#
Column B is the frame# (framecount never starts at 1).
Column C is x
Column D is y
Column E is z
For a complete frame, Column B will have the same frame# in 4 sequential rows. I want to delete every row that is part of an incomplete frame.
My data looks like this:
1, 3579, x1, y1, z1
2, 3579, x2, y2, z2
7, 3579, x7, y7, z7
8, 3579, x8, y8, z8
1, 3580, x1, y1, z1
2, 3580, x2, y2, z2
7, 3580, x7, y7, z7
8, 3580, x8, y8, z8
1, 3581, x1, y1, z1
2, 3581, x2, y2, z2
7, 3581, x7, y7, z7
8, 3581, x8, y8, z8
1, 3582, x1, y1, z1
2, 3582, x2, y2, z2
7, 3582, x7, y7, z7
8, 3582, x8, y8, z8
1, 3583, x1, y1, z1
2, 3583, x2, y2, z2
1, 3584, x1, y1, z1
2, 3584, x2, y2, z2
1, 3585, x1, y1, z1
2, 3585, x2, y2, z2
1, 3586, x1, y1, z1
2, 3586, x2, y2, z2
7, 3586, x7, y7, z7
8, 3586, x8, y8, z8
In the dataset above, I would want to delete the bold rows for incomplete frames 3583, 3584 & 3585.
Can anyone help with a macro? I have hundreds of worksheets to process so formulas, fill downs, filtering and copy/pasting would take days. Thanks so much for any assistance you can provide!
I tried this code on an earlier dataset that had 8 sensors (it uses the sensor# instead of the frame#) but it didn't work.
sub clean_data()
'determine the number of rows
numrows = 1
Do While ActiveSheet.Cells(numrows, 1).Value > 0
numrows = numrows + 1
Loop
numrows = numrows - 1
ActiveSheet.Cells(1, 14).Value = "Original"
ActiveSheet.Cells(1, 15).Value = "Cleaned"
ActiveSheet.Cells(2, 13).Value = "Row Count:"
ActiveSheet.Cells(2, 14).Value = numrows
'determine the number of frames, the number of entire frames missing, and which entire frames are missing
numframes = 0
numframes = ActiveSheet.Cells(numrows, 4).Value - ActiveSheet.Cells(1, 4).Value + 1
j = 4
missingframes = 0
numsensor1 = 0
numsensor2 = 0
numsensor3 = 0
numsensor4 = 0
numsensor5 = 0
numsensor6 = 0
numsensor7 = 0
numsensor8 = 0
For i = 1 To numrows
If ActiveSheet.Cells(i + 1, 4).Value - ActiveSheet.Cells(i, 4).Value > 1 Then
missingframes = missingframes + (ActiveSheet.Cells(i + 1, 4).Value - ActiveSheet.Cells(i, 4).Value) - 1
'activesheet.Cells(j, 2).Value = activesheet.Cells(i, 4).Value
'activesheet.Cells(j, 3).Value = (activesheet.Cells(i + 1, 4).Value - activesheet.Cells(i, 4).Value) - 1
'j = j + 1
End If
If ActiveSheet.Cells(i, 1).Value = 1 Then
numsensor1 = numsensor1 + 1
ElseIf ActiveSheet.Cells(i, 1).Value = 2 Then
numsensor2 = numsensor2 + 1
ElseIf ActiveSheet.Cells(i, 1).Value = 3 Then
numsensor3 = numsensor3 + 1
ElseIf ActiveSheet.Cells(i, 1).Value = 4 Then
numsensor4 = numsensor4 + 1
ElseIf ActiveSheet.Cells(i, 1).Value = 5 Then
numsensor5 = numsensor5 + 1
ElseIf ActiveSheet.Cells(i, 1).Value = 6 Then
numsensor6 = numsensor6 + 1
ElseIf ActiveSheet.Cells(i, 1).Value = 7 Then
numsensor7 = numsensor7 + 1
ElseIf ActiveSheet.Cells(i, 1).Value = 8 Then
numsensor8 = numsensor8 + 1
End If
Next i
'activesheet.Cells(1, 3).Value = j
ActiveSheet.Cells(3, 13).Value = "Frame Count:"
ActiveSheet.Cells(3, 14).Value = numframes
ActiveSheet.Cells(4, 13).Value = "Missing Frames:"
ActiveSheet.Cells(4, 14).Value = missingframes
ActiveSheet.Cells(5, 13).Value = "Sensor 1:"
ActiveSheet.Cells(5, 14).Value = numsensor1
ActiveSheet.Cells(6, 13).Value = "Sensor 2:"
ActiveSheet.Cells(6, 14).Value = numsensor2
ActiveSheet.Cells(7, 13).Value = "Sensor 3:"
ActiveSheet.Cells(7, 14).Value = numsensor3
ActiveSheet.Cells(8, 13).Value = "Sensor 4:"
ActiveSheet.Cells(8, 14).Value = numsensor4
ActiveSheet.Cells(9, 13).Value = "Sensor 5:"
ActiveSheet.Cells(9, 14).Value = numsensor5
ActiveSheet.Cells(10, 13).Value = "Sensor 6:"
ActiveSheet.Cells(10, 14).Value = numsensor6
ActiveSheet.Cells(11, 13).Value = "Sensor 7:"
ActiveSheet.Cells(11, 14).Value = numsensor7
ActiveSheet.Cells(12, 13).Value = "Sensor 8:"
ActiveSheet.Cells(12, 14).Value = numsensor8
'practice code for insertion and copy/paste
'activesheet.Cells(10, 1).Offset(1).EntireRow.Insert shift:=xlDown 'practice row insert
'activesheet.Rows(3).Select
'Selection.Copy
'activesheet.Rows(11).Activate
'activesheet.Paste
'find first complete set of sensor data
j = 0
i = 0
Do While i <> numrows
j = j + 1
i = i + 1
If j = 8 And ActiveSheet.Cells(i, 1).Value = 8 Then
ActiveSheet.Cells(13, 13).Value = "First Set"
first_set = i - 7
ActiveSheet.Cells(13, 14).Value = first_set
i = numrows
ElseIf j <> 8 And ActiveSheet.Cells(i, 1).Value = 8 Then
j = 0
End If
Loop
'find missing sensors and fill in with data from previous sensor frame
j = 1
i = first_set + 8
k = 0
Do While k = 0
'check for sensors 1 - 8 in sequence
If ActiveSheet.Cells(i, 1).Value = j Then
ActiveSheet.Cells(i, 12).Value = 0
j = j + 1
ElseIf ActiveSheet.Cells(i, 1).Value <> j Then
ActiveSheet.Cells(i - 1, 1).Offset(1).EntireRow.Insert shift:=xlDown 'insert a row to accept copied data
ActiveSheet.Rows(i - 8).Select 'select previous frame with data for missing sensor and then copy the data
Selection.Copy
ActiveSheet.Rows(i).Activate 'change focus to inserted row and paste in the missing data
ActiveSheet.Paste
ActiveSheet.Cells(i, 4).Value = ActiveSheet.Cells(i - 8, 4) + 1
ActiveSheet.Cells(i, 12).Value = 1
numrows = numrows + 1
j = j + 1
End If
If j = 9 Then
j = 1
End If
If i = numrows Then
k = 1
Else
i = i + 1
End If
Loop
'View cleaned data
missingframes = 0
numsensor1 = 0
numsensor2 = 0
numsensor3 = 0
numsensor4 = 0
numsensor5 = 0
numsensor6 = 0
numsensor7 = 0
numsensor8 = 0
i = 1
l = 0
Do While l = 0
If ActiveSheet.Cells(i + 1, 4).Value - ActiveSheet.Cells(i, 4).Value > 1 Then
k = 1
j = 0
Do While j = 0
ActiveSheet.Cells((i + k - 1), 1).Offset(1).EntireRow.Insert shift:=xlDown 'insert a row to accept copied data
ActiveSheet.Rows((i + k - 1) - 7).Select 'select previous frame with data for missing sensor and then copy the data
Selection.Copy
ActiveSheet.Rows(i + k).Activate 'change focus to inserted row and paste in the missing data
ActiveSheet.Paste
ActiveSheet.Cells(i + k, 4).Value = ActiveSheet.Cells(i, 4).Value + 1
ActiveSheet.Cells(i + k, 12).Value = 2
numrows = numrows + 1
k = k + 1
If k = 9 Then
j = 1
End If
Loop
End If
i = i + 1
If i = numrows + 1 Then
l = 1
End If
Loop
'determine the number of rows
numrows = 1
Do While ActiveSheet.Cells(numrows, 1).Value > 0
numrows = numrows + 1
Loop
numrows = numrows - 1
'activesheet.Cells(1, 14).Value = "Original"
'activesheet.Cells(1, 15).Value = "Cleaned"
ActiveSheet.Cells(2, 13).Value = "Row Count:"
ActiveSheet.Cells(2, 15).Value = numrows
'determine the number of frames, the number of entire frames missing, and which entire frames are missing
numframes = 0
numframes = ActiveSheet.Cells(numrows, 4).Value - ActiveSheet.Cells(1, 4).Value + 1
For i = 1 To numrows
If ActiveSheet.Cells(i + 1, 4).Value - ActiveSheet.Cells(i, 4).Value > 1 Then
missingframes = missingframes + (ActiveSheet.Cells(i + 1, 4).Value - ActiveSheet.Cells(i, 4).Value) - 1
End If
Next i
For i = 1 To numrows
If ActiveSheet.Cells(i, 1).Value = 1 Then
numsensor1 = numsensor1 + 1
ElseIf ActiveSheet.Cells(i, 1).Value = 2 Then
numsensor2 = numsensor2 + 1
ElseIf ActiveSheet.Cells(i, 1).Value = 3 Then
numsensor3 = numsensor3 + 1
ElseIf ActiveSheet.Cells(i, 1).Value = 4 Then
numsensor4 = numsensor4 + 1
ElseIf ActiveSheet.Cells(i, 1).Value = 5 Then
numsensor5 = numsensor5 + 1
ElseIf ActiveSheet.Cells(i, 1).Value = 6 Then
numsensor6 = numsensor6 + 1
ElseIf ActiveSheet.Cells(i, 1).Value = 7 Then
numsensor7 = numsensor7 + 1
ElseIf ActiveSheet.Cells(i, 1).Value = 8 Then
numsensor8 = numsensor8 + 1
End If
Next i
'activesheet.Cells(1, 3).Value = j
'activesheet.Cells(3, 13).Value = "Frame Count:"
ActiveSheet.Cells(3, 15).Value = numframes
'activesheet.Cells(4, 13).Value = "Missing Frames:"
ActiveSheet.Cells(4, 15).Value = missingframes
'activesheet.Cells(5, 13).Value = "Sensor 1:"
ActiveSheet.Cells(5, 15).Value = numsensor1
'activesheet.Cells(6, 13).Value = "Sensor 2:"
ActiveSheet.Cells(6, 15).Value = numsensor2
'activesheet.Cells(7, 13).Value = "Sensor 3:"
ActiveSheet.Cells(7, 15).Value = numsensor3
'activesheet.Cells(8, 13).Value = "Sensor 4:"
ActiveSheet.Cells(8, 15).Value = numsensor4
'activesheet.Cells(9, 13).Value = "Sensor 5:"
ActiveSheet.Cells(9, 15).Value = numsensor5
'activesheet.Cells(10, 13).Value = "Sensor 6:"
ActiveSheet.Cells(10, 15).Value = numsensor6
'activesheet.Cells(11, 13).Value = "Sensor 7:"
ActiveSheet.Cells(11, 15).Value = numsensor7
'activesheet.Cells(12, 13).Value = "Sensor 8:"
ActiveSheet.Cells(12, 15).Value = numsensor8
End Sub
I have hundreds of files so I don't want to have to import to Matlab, run the script then export back to excel. But here is Matlab code that worked conceptually (column 4 contained the frame# in this dataset):
i=xlsread('33_F_.xlsm');
`i2=[i(:,1) i(:,4) i(:,6:11)];
i3=[];
[m,n]= size(i2);
count=1;
frame=i2(1,2);
for j=2:m
if(count==1)
frame=i2(j,2);
end
if(i2(j,2)==frame)
count=count+1;
else
frame=i2(j,2);
count=1;
i2(j-count:j-1)=[];
end
if(count==4)
count=0;
i3=[i3;i2(j-3:j,:)];
end
end`
Create a column "F" with the formula =COUNTIF(B:B,B1) in the first row. Double-click the fill-down button (the bottom-right corner of the cell) to copy the formula for all your data.
Then, click within the table and do CTRL+A followed by CTRL+L - when it asks if your data has headers, say "no" and it will add column names. Then, filter by column F for any sensors less than 4, and delete those rows. Clear the filter and you will be good to go.
Edit following commentary/discussion, here is a VBA code that does what you need it to do (tested):
Public Sub clean()
Dim i As Integer
i = 1
Do
Dim a As Integer
a = WorksheetFunction.CountIf(Range("B:B"), Cells(i, 2))
If (a < 4) And Len(Cells(i, 1)) > 0 Then
Rows(i).Delete
Else
i = i + 1
End If
Loop While Len(Cells(i, 1)) > 0
End Sub