Sorting Data Across Two Columns

Helpless_Tim

New Member
Joined
May 16, 2019
Messages
5
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:

11
2
33
4
5

<tbody>
</tbody>


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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,771
Office Version
365
Platform
Windows
Welcome to the Board!

Assuming that your data is in columns A and B are there is no header row, this should work:
Code:
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

New Member
Joined
May 16, 2019
Messages
5
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.


£5211£64
£602
3£17
£994
£4955£49

<tbody>
</tbody>


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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,771
Office Version
365
Platform
Windows
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

New Member
Joined
May 16, 2019
Messages
5
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

New Member
Joined
May 16, 2019
Messages
5
I normally start with something like this:

£10ApplesApples£9
£8OrangesNecturines£15
£15NecturinesPears£7

<tbody>
</tbody>


And once sorted, I get to something like this:

£10ApplesApples£9-£1
£8Oranges£8
£15NecturinesNecturines£15£0
Pears£7£-7

<tbody>
</tbody>










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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,771
Office Version
365
Platform
Windows
The images really helped clarify what you are trying to do.

Try this version:
Code:
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
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,771
Office Version
365
Platform
Windows
You are welcome.
 

Forum statistics

Threads
1,085,429
Messages
5,383,619
Members
401,842
Latest member
BathAntelope

Some videos you may like

This Week's Hot Topics

Top