Sorting Data Across Two Columns

[Helpless_Tim]

Hi,

I'm looking to sort two sets of data so that matching pairs appear in the same rows and gaps appear where there isn't a match. The end result would look something like this:

[TABLE="width: 500"]
<tbody>[TR]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


The columns either side of these (A and D, assuming that the above are B and C) need to then be compared, but the references contained in columns B and C are what needs to be matched up.

I use this method regularly and currently sort the data manually - a way to do it in a few button clicks would save me loads of time!

Cheers,

H_T

 

[Joe4]

Welcome to the Board!

Assuming that your data is in columns A and B are there is no header row, this should work:

Sub SortMacro()

    Dim r As Long

    Application.ScreenUpdating = False

'   Sort column A
    Columns("A:A").Sort key1:=Range("A1"), order1:=xlAscending, Header:=xlNo
        
'   Sort column B
    Columns("B:B").Sort key1:=Range("B1"), order1:=xlAscending, Header:=xlNo
    
'   Loop through all data
    r = 1
    Do Until Cells(r, "A") = ""
'       If column B is empty, then exit loop
        If Cells(r, "B") = "" Then Exit Do
'       Check to see if two columns are equal
        If Cells(r, "A") = Cells(r, "B") Then
'           If they are, move to next row
            r = r + 1
        Else
'           If column A is greater than column B
            If Cells(r, "A") > Cells(r, "B") Then
'               Move column A down one row
                Cells(r, "A").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
            Else
'               Move column B down one row
                Cells(r, "B").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
            End If
'           Move to next row
            r = r + 1
        End If
    Loop

    Application.ScreenUpdating = True
        
End Sub

If my assumptions aren't correct, we can tweak the code to account for the differences, if you let us know what the conditions are.

 

[Helpless_Tim]

Thanks Joe4. That the gist of it, but I really need it to also move around the data in the columns either side of the stuff being matched/sorted. That was my second comment - apologies if my language wasn't clear.

For clarity, the task is (roughly speaking) comparing forecast data to what actually happened, against specific budget lines, but the reports don't show lines that are 0. It'll look something like this, and once the data's been sorted I just do =A-D in column E to highlight where there are differences for me to act on.


[TABLE="width: 500"]
<tbody>[TR]
[TD]£52[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]£64[/TD]
[/TR]
[TR]
[TD]£60[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]3[/TD]
[TD]£17[/TD]
[/TR]
[TR]
[TD]£99[/TD]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]£49[/TD]
[TD]5[/TD]
[TD]5[/TD]
[TD]£49[/TD]
[/TR]
</tbody>[/TABLE]


Cheers,

H_T

Welcome to the Board!

Assuming that your data is in columns A and B are there is no header row, this should work:
If my assumptions aren't correct, we can tweak the code to account for the differences, if you let us know what the conditions are.

 

[Joe4]

Thanks Joe4. That the gist of it, but I really need it to also move around the data in the columns either side of the stuff being matched/sorted. That was my second comment - apologies if my language wasn't clear.

Yes, that wasn't too clear in the initial post. It is important to provide the essential details, which we still need.

So, let me explicitly ask for the details we need:
1. Exactly which two columns are being compared?
2. What other columns are "grouped" which each of these two columns, and should move with them (which other columns are associated with the first column in the compare, and which other columns are associated with the second column being compared?

 

[Helpless_Tim]

Yes, that wasn't too clear in the initial post. It is important to provide the essential details, which we still need.

So, let me explicitly ask for the details we need:
1. Exactly which two columns are being compared?
2. What other columns are "grouped" which each of these two columns, and should move with them (which other columns are associated with the first column in the compare, and which other columns are associated with the second column being compared?

1. The common reference in the two data sets are in columns B and C. Once the data is sorted, I need to compare the data in columns A and D.
2. Columns A and B are one set, columns C and D are the second data set.

The aim of the sheet is for me to able to see the difference between the figures in A and D, but the task here is to ensure that Item 1 in both the left hand dataset (columns A and B), and Item 1 in the right hand dataset (columns C and D) are on the same row - the code below does this well, but it doesn't move the the figures in A and D with it.

Hope that makes it clearer. I fear I may have over-complicated it - please do ask further questions if it doesn't make sense.

Thanks

 

[Helpless_Tim]

I normally start with something like this:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]£10[/TD]
[TD]Apples[/TD]
[TD]Apples[/TD]
[TD]£9[/TD]
[/TR]
[TR]
[TD]£8[/TD]
[TD]Oranges[/TD]
[TD]Necturines[/TD]
[TD]£15[/TD]
[/TR]
[TR]
[TD]£15[/TD]
[TD]Necturines[/TD]
[TD]Pears[/TD]
[TD]£7[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


And once sorted, I get to something like this:

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]£10[/TD]
[TD]Apples[/TD]
[TD]Apples[/TD]
[TD]£9[/TD]
[TD]-£1[/TD]
[/TR]
[TR]
[TD]£8[/TD]
[TD]Oranges[/TD]
[TD][/TD]
[TD][/TD]
[TD]£8[/TD]
[/TR]
[TR]
[TD]£15[/TD]
[TD]Necturines[/TD]
[TD]Necturines[/TD]
[TD]£15[/TD]
[TD]£0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Pears[/TD]
[TD]£7[/TD]
[TD]£-7[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]










Only there are a few hundred lines, and it's not fruit. I'll then sort A to E by column E, which shows me what I need to work on.

Hopefully this explains it better than me trying to use words, which I'm apparently failing at!

 

[Joe4]

The images really helped clarify what you are trying to do.

Try this version:

Sub SortMacro()

    Dim r As Long

    Application.ScreenUpdating = False

'   Sort columns A and B by column B
    Columns("A:B").Sort key1:=Range("B1"), order1:=xlAscending, Header:=xlNo
        
'   Sort column C and D by column C
    Columns("C:D").Sort key1:=Range("C1"), order1:=xlAscending, Header:=xlNo
    
'   Loop through all data
    r = 1
    Do Until Cells(r, "B") = ""
'       If column C is empty, then exit loop
        If Cells(r, "C") = "" Then Exit Do
'       Check to see if two columns are equal
        If Cells(r, "B") = Cells(r, "C") Then
'           If they are, move to next row
            r = r + 1
        Else
'           If column B is greater than column C
            If Cells(r, "B") > Cells(r, "C") Then
'               Move columns A and B down one row
                Range(Cells(r, "A"), Cells(r, "B")).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
            Else
'               Move columns C and D down one row
                Range(Cells(r, "C"), Cells(r, "D")).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
            End If
'           Move to next row
            r = r + 1
        End If
    Loop

    Application.ScreenUpdating = True
        
End Sub

 

[Helpless_Tim]

That's perfect, thank you

H_T

 

[Joe4]

You are welcome.