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
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
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.
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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 :)
 
Upvote 0
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!
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,212,927
Messages
6,110,708
Members
448,293
Latest member
jin kazuya

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top