PDA

View Full Version : Delete Row until Data Matches



maninjapan
11-15-2011, 02:29 PM
I am attempting to match 2 sets of data by date and time.
The main set of data has already been filtered down to a single row of data per day
The second set of data is raw data and contains data from throughout the day. I need to filter this down so that it only has the matching dates and times as the main data

I was thinking a do until loop would run go through each row, deleting data until it matched the time and date of the data on the left hand side, once it did it would step down to the next row and repeat.

One point to note The time of the left hand side may not always be the same (or I could have used a simple filter to get the data...)

I have attempted the following code, but unable to get it to run.
I have attached a sample of data showing a before and after sheet. I've tried this myself but am completely stuck. Hope someone can help.....

mdmackillop
11-15-2011, 04:45 PM
Add some helper columns (H, P, Q) then you can filter on Q

maninjapan
11-16-2011, 01:34 AM
Thanks, that makes sense. I am attempting to use something along the lines of the following Do Until loop to delete rows of data until it matches, move to the next row and repeat.
It doesnt work in its current form, but I hope at least I am onthe right track here. What do I need to fix to get this into the correct format?



Sub Match_Data()

End Sub
Dim i As Long, LastRow As Long

LastRow = Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To LastRow

Do Until Range("J" & i).Value = 1

Range("I" & i & ":P" & i).Delete Shift:=xlUp

Loop


Next i

End Sub

mdmackillop
11-16-2011, 06:53 AM
When deleting, start from the bottom

LastRow = Range("A" & Rows.Count).End(xlUp).Row
For i = LastRow To 2 Step -1
If Range("J" & i).Value = 1 Then
Range("I" & i).Resize(, 8).Delete Shift:=xlUp
End If
Next i

maninjapan
11-16-2011, 07:28 AM
Thanks for that, if that is the case I don't think this is the correct way to attack the problem them.

There is a lot of unnecessary time points in the right hand side. I just need to pull out the matching rows (matched by time and date).

By using the following condition as the bottom row of data is 0 and this only changes to 1 if it is moved up and matches the data on the left.

If Range("J" & i).Value = 1 Then

Also by deleting from the bottom up it will delete all the data points before they have a chance to be compared to the data on the left.

Another way to look at this problem I guess, is row of data on the left (A:H) needs to pull out its matching row of data on the right (J:Q) and delete the rest.

mdmackillop
11-16-2011, 11:19 AM
Using VBA I would
Add the formulae
Filter the results
Copy filtered cells
Clear the filter
Clear the long list
Paste back the copied results

I can look at this later, if you need assistance with this.

mdmackillop
11-16-2011, 01:10 PM
Sub Macro2()
Dim Rng1 As Range, Rng2 As Range
Dim sh As Worksheet
Dim Rw As Long
Application.ScreenUpdating = False
Set sh = Sheets("Before")
With sh
'Set ranges
Set Rng1 = Range(.Cells(1, 1), .Cells(Rows.Count, 1).End(xlUp)).Offset(, 7)
Set Rng2 = Range(.Cells(1, 9), .Cells(Rows.Count, 9).End(xlUp)).Offset(, 7)
'Write formulae
With Rng1
.FormulaR1C1 = "=SUM(RC1:RC2)"
.NumberFormat = "m/d/yyyy h:mm"
.Columns.AutoFit
End With

With Rng2
.FormulaR1C1 = "=SUM(RC9:RC10)"
.NumberFormat = "m/d/yyyy h:mm"
.Columns.AutoFit
.Offset(, 1).FormulaR1C1 = "=Countif(C8,RC16)"
End With

'Sort data to collect 1 at top
.Sort.SortFields.Clear
.Sort.SortFields.Add Key:=Range("Q1"), _
SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With .Sort
.SetRange Range("I:Q")
.Header = xlNo
.Orientation = xlTopToBottom
.Apply
End With

'Find first 0 and clear remainder
Set r = .Range("Q:Q").Find(0, LookIn:=xlValues).Offset(, -8)
r.Select
Range(r, r.End(xlToRight).End(xlDown)).Clear
'Remove helpers
Range("H:H,P:Q").Clear
End With
Application.ScreenUpdating = True
End Sub

maninjapan
11-30-2011, 10:00 AM
Apologies for taking so long to get back to this. I have run this and it worked first time!! Thank you very much. I believe it is 90% of the way there. One thing I have realized is there may not be the matching data point in the unfiltered data. Is it possible to leave the row blank if there is no data for that data and time? It is easy to see whats missing after running this example but the full range of data is a few years long across a small group of products to match to the main data, not just 2 as in this example....

maninjapan
11-30-2011, 10:06 AM
I have attached a data sample with this macro. Hopefully his will illustrate what I am getting at.