COL 1  COL 2
 A      E
 B      G 
 C      A
 G      A
 D      C
 E      F
 D      F
 F      F

I want it to look like:

COL 1  COL 2
 A      A
 B      BLANK
 C      C
 D      BLANK
 D      BLANK
 E      E
 F      F
 G      G

Please help me out. I have tons of numbers in two columns and want to align them as shown above.


This would seem to reorganize the sample data you provided correctly.

Sub mcr_fix()
    Dim rw As Long, c As Long
    With ActiveSheet
        For c = 1 To 2
            With .Cells(1, 1).CurrentRegion.Columns(c)
                .Cells.Sort key1:=.Columns(1), order1:=xlAscending, _
                            Orientation:=xlTopToBottom, Header:=xlYes
            End With
        Next c
        rw = 3
        Do While CBool(Application.CountA(.Cells(rw, 1).Resize(1, 2)))
            If .Cells(rw, 1).Value > .Cells(rw, 2).Value Then
                .Cells(rw, 1).Insert Shift:=xlDown
            ElseIf .Cells(rw, 1).Value < .Cells(rw, 2).Value Then
                .Cells(rw, 2).Insert Shift:=xlDown
            End If
            rw = rw + 1
            If rw > 500 Then Exit Do
    End With
End Sub
  • I was in the middle of writing something similar. Nice work! – Pathfinder Nov 21 '14 at 10:16
  • Probably want to include how to use it – Pathfinder Nov 21 '14 at 10:18
  • @Pathfinder - I actually left my check-out-of-infinite-loop code line in when I copied and pasted after testing. I was just returning to advise the OP about it. – Jeeped Nov 21 '14 at 10:19
  • @AbeikuAcquah - after checking to make it it runs on a smaller sample you need to remove If rw > 500 Then Exit Do to run on tons of numbers. – Jeeped Nov 21 '14 at 10:20
  • wow. thats magnificent.thanks soo soo much. soo soo happy but i had a slight change from what the output should be. A2 should be blank but its B and A3 should have been B. Please resolve that for me. thankx – Abeiku Acquah Nov 22 '14 at 16:22

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.