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
Bob Phillips
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
Bob Phillips
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
Bob Phillips
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)
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.