PDA

View Full Version : Solved: Matching up columns of data



maninjapan
12-07-2009, 05:10 AM
I am trying to match up 2 columns of data, Column1 has some missing rows and I would like to delete the same rows from Column 2 so they only show the same data. This needs to check from the top down.

Example

1 1
2 2
3 3
5 4
6 5
7 6
7

In this example, it would delete Row 4 from Column 2. This is what I have so far, but not having any luck


Sub Insert()
Dim rng As Range, i As Integer
'Set the range to evaluate to rng.
Set rng = Range("A1:A2000")

If rng.Cells(i, "F").Value != rng.Cells(i, "B").Value Then rng.Cells(i, "E:G").Delete Shift:=xlUp

End Sub

xld
12-07-2009, 06:21 AM
Sub ProcessData()
Dim LastRow As Long
Dim i As Long

With ActiveSheet

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 1 To LastRow

If .Cells(i, "B").Value < .Cells(i, "A").Value Then

Do

.Cells(i, "B").Delete shift:=xlShiftUp
Loop Until .Cells(i, "B").Value >= .Cells(i, "A").Value Or .Cells(i, "B").Value = ""
End If
Next i
End With
End Sub

maninjapan
12-07-2009, 06:26 AM
Thanks xld, forgot one thing though,

If .Cells(i, "B").Value < .Cells(i, "A").Value Then

Delete and shift up Columns B throughF

Thanks

xld
12-07-2009, 07:03 AM
Sub ProcessData()
Dim LastRow As Long
Dim i As Long

With ActiveSheet

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 1 To LastRow

If .Cells(i, "B").Value < .Cells(i, "A").Value Then

Do

.Cells(i, "B").Resize(, 5).Delete shift:=xlShiftUp
Loop Until .Cells(i, "B").Value >= .Cells(i, "A").Value Or .Cells(i, "B").Value = ""
End If
Next i
End With
End Sub

maninjapan
12-08-2009, 12:47 PM
xld, that almost works. the example I gave didnt quite cover the full data I have , looks more like this. I want to look until the cell actually matches.


2 1
3 2
4 3
2 4
3 5
4 1
. 2
. 3
. 4
. 5

xld
12-08-2009, 02:51 PM
I am not sure I understand that mate. Can 2 repeat in column 1? What are the . about?

maninjapan
12-09-2009, 06:57 AM
Actually, the columns represent trading hours in a day, so it repeats everyday, its just that column 2 had more trading hours than column 1 and I wanted to get rid of the excess hours in column2. the '.'s are meant to mean column 1 is blank (I could get it to line up wthi jsut a space)