# Sorting Data Across Two Columns

#### Helpless_Tim

##### New Member
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:

 1 1 2 3 3 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

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

'   Sort column B

'   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
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.

 £52 1 1 £64 £60 2 3 £17 £99 4 £49 5 5 £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

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
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

 £10 Apples Apples £9 £8 Oranges Necturines £15 £15 Necturines Pears £7

<tbody>
</tbody>

And once sorted, I get to something like this:

 £10 Apples Apples £9 -£1 £8 Oranges £8 £15 Necturines Necturines £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

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

'   Sort column C and D by column C

'   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

#### Helpless_Tim

##### New Member
That's perfect, thank you H_T

You are welcome.