I need to compare to columns... w/ a twist!

Tags:
excel
forum
October 3, 2011 at 08:27:29
Specs: Windows XP
excel comparing two columns with a twist.. OK...
If the two columns match leave them side by side... if they do not match insert a blank cell underneath...

From:
....A.................B..................C.................D
1cat...........77.99................cat..............6.63
2dog..........87.63................pig..............6.75
3rat.............7.55.................rat............78.98

To:
....A...............B................C...................D
1cat.........77.99...............cat...............6.63
2dog........87.63...................................................
3.....................................pig...............6.75
4rat............7.55...............rat..............78.98

in the about example I am comparing columns 'A' and 'C', which must stay in alpha/numeric order. HELP!!!!!!!!!!!!


See More: I need to compare to columns... w/ a twist!

Report •

#1
October 3, 2011 at 13:32:23
First, a posting tip:

Before posting any more data in this forum, please click on the blue line at the end of this post and read the instructions on how to post data. Thanks.

As for your task, try this code:

Sub AnimalCrackers()
'Find last Row with data in Column A
  lastRw = Range("A" & Rows.Count).End(xlUp).Row
'Compare Column A to Column C in reverse order
    For nxtRw = lastRw To 1 Step -1
'If Column C is not empty and doesn't match Column A
'Insert a Row and move data in C and D down to new Row
      If Range("C" & nxtRw) <> "" Then
        If Range("A" & nxtRw) <> Range("C" & nxtRw) Then
          Range("A" & nxtRw + 1).EntireRow.Insert shift:=xlDown
          Range("C" & nxtRw & ":D" & nxtRw).Cut _
            Destination:=Range("C" & nxtRw + 1)
        End If
      End If
    Next
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •
Start a discussion
Related Solutions
See More ↓


Ask Question