Consulting

Results 1 to 7 of 7

Thread: Solved: Matching up columns of data

  1. #1
    VBAX Tutor
    Joined
    Sep 2008
    Posts
    213
    Location

    Solved: Matching up columns of data

    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

    [VBA]
    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[/VBA]

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,272
    Location
    [VBA]

    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[/VBA]
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3
    VBAX Tutor
    Joined
    Sep 2008
    Posts
    213
    Location
    Thanks xld, forgot one thing though,
    [VBA]
    If .Cells(i, "B").Value < .Cells(i, "A").Value Then[/VBA]

    Delete and shift up Columns B throughF

    Thanks

  4. #4
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,272
    Location
    [VBA]


    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 [/VBA]
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  5. #5
    VBAX Tutor
    Joined
    Sep 2008
    Posts
    213
    Location
    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

  6. #6
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,272
    Location
    I am not sure I understand that mate. Can 2 repeat in column 1? What are the . about?
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  7. #7
    VBAX Tutor
    Joined
    Sep 2008
    Posts
    213
    Location
    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)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •