match and sort by two columns

post a workbook!

Hi Yowzer, welcome to the forum.

Click GO ADVANCED and use the paperclip icon to post up your sample workbook.

Try this on a copy of your sheet:

Option Explicit

Sub LineThemUp()
'JBeaucaire  2/10/10
'Lines up 2nd table column M with 1st table column A values
Dim LR As Long
Application.ScreenUpdating = False

Columns("A:A").Copy Range("Z1")

LR = Range("M" & Rows.Count).End(xlUp).Row
Range("M4:M" & LR).Copy Range("Z" & Rows.Count).End(xlUp).Offset(1, 0)

Range("Z:Z").SpecialCells(xlCellTypeBlanks).Delete xlShiftUp
Range("Z:Z").Sort [Z1], xlAscending, , , , , , xlYes

LR = Range("Z" & Rows.Count).End(xlUp).Row
Range("Z1:Z" & LR).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("L3"), Unique:=True
Range("Z:Z").Clear

Dim i As Long:  i = 4
Do
    Do While Cells(i, "A") = ""
        Range(Cells(i, "A"), Cells(i, "K")).Delete xlShiftUp
    Loop
        
    If Cells(i, "M") > Cells(i, "L") Then _
        Range(Cells(i, "M"), Cells(i, "W")).Insert xlShiftDown
    If Cells(i, "A") > Cells(i, "L") Then _
        Range(Cells(i, "A"), Cells(i, "K")).Insert xlShiftDown
    
    i = i + 1
Loop Until Cells(i, "K") = ""

Range("L:L").Clear
Application.ScreenUpdating = True
MsgBox "Complete"
End Sub

If that takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].

(Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated. It is found across from the "time" in each of our posts.)

Hehe...not exactly the same thing, but try this:

Option Explicit

Sub LineThemUp()
'JBeaucaire  2/16/2010
'Lines up values in columns B, O, AB and AL
Dim LR As Long
Application.ScreenUpdating = False

LR = Range("A" & Rows.Count).End(xlUp).Row

'Create a unique list of all values
Range("B3:B" & LR).Copy Range("DD1")
Range("O4:O" & LR).Copy Range("DD" & Rows.Count).End(xlUp).Offset(1, 0)
Range("AB4:AB" & LR).Copy Range("DD" & Rows.Count).End(xlUp).Offset(1, 0)
Range("AL4:AL" & LR).Copy Range("DD" & Rows.Count).End(xlUp).Offset(1, 0)

Range("DD:DD").SpecialCells(xlCellTypeBlanks).Delete xlShiftUp
Range("DD:DD").Sort [DD1], xlAscending, , , , , , xlYes

LR = Range("DD" & Rows.Count).End(xlUp).Row
Range("DD1:DD" & LR).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("DE3"), Unique:=True
Range("DD:DD").Clear

'Filter out the blanks
Range("A3:M" & LR).Sort Key1:=Range("B4"), Order1:=xlAscending, Header:=xlYes
Range("N3:Z" & LR).Sort Key1:=Range("O4"), Order1:=xlAscending, Header:=xlYes
Range("AA3:AJ" & LR).Sort Key1:=Range("AB4"), Order1:=xlAscending, Header:=xlYes
Range("AK3:AT" & LR).Sort Key1:=Range("AL4"), Order1:=xlAscending, Header:=xlYes

Dim i As Long:  i = 4
Do
    If Cells(i, "B") > Cells(i, "DE") Then _
        Range(Cells(i, "A"), Cells(i, "M")).Insert xlShiftDown
    If Cells(i, "O") > Cells(i, "**") Then _
        Range(Cells(i, "N"), Cells(i, "Z")).Insert xlShiftDown
    If Cells(i, "AB") > Cells(i, "DE") Then _
        Range(Cells(i, "AA"), Cells(i, "AJ")).Insert xlShiftDown
    If Cells(i, "AL") > Cells(i, "DE") Then _
        Range(Cells(i, "AK"), Cells(i, "AT")).Insert xlShiftDown
    i = i + 1
Loop Until Cells(i, "DE") = ""

Range("DE:DE").Clear
Application.ScreenUpdating = True
MsgBox "Complete"
End Sub

Be patient, this one will take a while to loop.

Where in the code? You can speed up this process by providing more detail than that...or post up the misbehaving workbook with the macro already in it so I can see for myself.

1) Delete the macro you downloaded from the ThisWorkbook module. The macro didn't go in there to begin with, and the forum corrupted the upload and put a bunch of ** in the code in place of column references I had used.

Delete it.

2) Click on INSERT > MODULE and post the code in a regular module. Download the code above again, I've repaired the corrupted code.


Make sure you only put global workbook-event macros into ThisWorkbook, sheet-event macros into the sheet modules...all other macros go in regular modules.

Sorry, change this line near the top:

Range("DD:DD").Sort [DD1], xlAscending, , , , , , xlYes

And this near the bottom:

    If Cells(i, "O") > Cells(i, "DE") Then _
        Range(Cells(i, "N"), Cells(i, "Z")).Insert xlShiftDown

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

(Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated. It is found across from the "time" in each of our posts.)

As per a Private Message:

Go to your original post, post #1 at the beginning of this whole thread.
Click on EDIT
Click on GO ADVANCED
Next to the title is a drop box, set this prefix box to [SOLVED]


==========
Across from my name in any post, this one included, are some icons. The Reputation Feedback icon is the one that looks like blue scales.

Kay,

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

====================