How to line up match rows between various columns

[cytochrome]

Hi there,
Just wondering what is the best way to accomplish this.
I have 6 columns of data.

G1	CA1	134​	G1	CA1	134​
G1	CA2	112.15​	G1	CA2	110​
G2	CA3	4264.2​	G3	CA1	4264.2​
G4	CA3	12.98​	G4	CA3	12​
G5	CA3	32.18​	G5	CA4	32.18​
G6	CA4	46.25​	G6	CA4	46.25​
G7	CA5	160.78​	G8	CA5	1​
G9	CA9	1
G10	CA10	2

What's the best way to rearrange/sort them to have it look like this?

G1	CA1	134​	G1	CA1	134​
G1	CA2	112.15​	G1	CA2	110​
G2	CA3	4264.2​
			G3	CA1	4264.2​
G4	CA3	12.98​	G4	CA3	12​
G5	CA3	32.18​	G5	CA4	32.18​
G6	CA4	46.25​	G6	CA4	46.25​
G7	CA5	160.78​
			G8	CA5	1​
G9	CA9	1
G10	CA10	2

Basically the conditions to match up are A&B against D&E. If there is a VBA that can arrange that it would be amazing.

 

[Anfinsen]

Hello,
I am not sure VBA will work to sort your data as you wish. The variables to sort by are invalid, meaning, CA1-CA10 would sort like this;
CA1
CA10
CA2
CA3 and so on.
If you concatenate the values in A and B, and the values in D and E, and then sort each section (A-C and D-F) individually by the concatenated value, it will sort it improperly.

You could have VBA separate the values in A and B, and D and E, between characters and numbers, sort that (again sorting A-C and D-F individually, and then have the VBA concatenate the values back together after the sort is done. Not entirely sure if all that is plausible?

 

[cytochrome]

So minus VBA possibility. Is there an easy way to arrange/sort that data?

 

[Anfinsen]

So minus VBA possibility. Is there an easy way to arrange/sort that data?

If you concatenate the values in A and B, and the values in D and E, and then sort each section (A-C and D-F) individually by the concatenated value, it will sort it improperly.
But this will get you close to where you want to be.

 

[kevin9999]

You might want to try this with a few different scenarios, and it does assume the numeric suffix in columns A and D data is already in order, but it seems to work on the data in your sample. I won't say it will work with any combination, but it is a start.

Option Explicit
Sub cytochrome()
    Dim ws As Worksheet
    Set ws = Worksheets("Sheet1")   '<~~ Change to actual sheet name if needed
    Dim LRow As Long, i As Long, A As Long, B As Long, s As String
    LRow = ws.Cells(Rows.Count, 1).End(xlUp).Row
   
    For i = LRow To 1 Step -1
        If ws.Cells(i, 1) <> "" Or ws.Cells(i, 4) <> "" Then
            If ws.Cells(i, 1) <> "" Then s = ws.Cells(i, 1): A = GetNum(s)
            If ws.Cells(i, 4) <> "" Then s = ws.Cells(i, 4): B = GetNum(s)
           
            If (A - B) < 0 And ws.Cells(i, 4) <> "" Then
                ws.Cells(i, 4).Resize(, 3).Insert xlDown
                ws.Cells(i + 1, 1).Resize(, 3).Insert xlDown
            End If
            If (A - B) > 0 And ws.Cells(i, 4) <> "" Then ws.Cells(i, 1).Resize(, 3).Insert xlDown
        End If
    Next i
End Sub

Function GetNum(s As String) As Long
    GetNum = Mid(s, 2, Len(s) - 1)
End Function

Before:

Book1
	A	B	C	D	E	F
1	G1	CA1	134	G1	CA1	134
2	G1	CA2	112.15	G1	CA2	110
3	G2	CA3	4264.2	G3	CA1	4264.2
4	G4	CA3	12.98	G4	CA3	12
5	G5	CA3	32.18	G5	CA4	32.18
6	G6	CA4	46.25	G6	CA4	46.25
7	G7	CA5	160.78	G8	CA5	1
8	G9	CA9	1
9	G10	CA10	2
Sheet1

After:

Book1
	A	B	C	D	E	F
1	G1	CA1	134	G1	CA1	134
2	G1	CA2	112.15	G1	CA2	110
3	G2	CA3	4264.2
4				G3	CA1	4264.2
5	G4	CA3	12.98	G4	CA3	12
6	G5	CA3	32.18	G5	CA4	32.18
7	G6	CA4	46.25	G6	CA4	46.25
8	G7	CA5	160.78
9				G8	CA5	1
10	G9	CA9	1
11	G10	CA10	2
Sheet1

 

[cytochrome]

Thank you so much for helping. Will test it out with my actual data and let you know.