Sort Complication

Copper Contributor

I have an Excel document with an inventory of all my Edison recordings.  I sort the file by 'Coupling' but as you can see in the first table when there are multiple records with the same coupling number all the 'L' (Left Side) and all the "R" (Right Side) end up together.  What I need is to have the "L" side find its "R" side so they end up like the second table below.  

 

Example:  The first 50115-L listed below has its adjoining right side key listed in the 'Joined Record' column - Diamond Disc50115-R2452-A1-46.  The matching "R" side would be found by searching the 'This_Records_Join_Field'.  I don't know if this is even possible.

 

Sorted by Coupling   
TitleCouplingJoined RecordThis_Records_Join_Field
Aisha--Indian Intermezzo.50115-LDiamond Disc50115-R2452-A1-46Diamond Disc50115-L2468-A1-19 SA
Aisha--Indian Intermezzo.50115-LDiamond Disc50115-R2452-A1-19Diamond Disc50115-L2468-A1-7
Aisha--Indian Intermezzo.50115-LDiamond Disc50115-R2452-A3-1Diamond Disc50115-L2468-A4-1
Aisha--Indian Intermezzo.50115-LDiamond Disc50115-R2452-C2-13Diamond Disc50115-L2468-A5-9
Naïla--Intermezzo.50115-RDiamond Disc50115-L2468-A5-9Diamond Disc50115-R2452-C2-13
Naïla--Intermezzo.50115-RDiamond Disc50115-L2468-A1-19 SADiamond Disc50115-R2452-A1-46
Naïla--Intermezzo.50115-RDiamond Disc50115-L2468-A4-1Diamond Disc50115-R2452-A3-1
Naïla--Intermezzo.50115-RDiamond Disc50115-L2468-A1-7Diamond Disc50115-R2452-A1-19

 

 

Desired Results   
TitleCouplingJoined RecordThis_Records_Join_Field
Aisha--Indian Intermezzo.50115-LDiamond Disc50115-R2452-A1-46Diamond Disc50115-L2468-A1-19 SA
Naïla--Intermezzo.50115-RDiamond Disc50115-L2468-A1-19 SADiamond Disc50115-R2452-A1-46
Aisha--Indian Intermezzo.50115-LDiamond Disc50115-R2452-A1-19Diamond Disc50115-L2468-A1-7
Naïla--Intermezzo.50115-RDiamond Disc50115-L2468-A1-7Diamond Disc50115-R2452-A1-19
Aisha--Indian Intermezzo.50115-LDiamond Disc50115-R2452-A3-1Diamond Disc50115-L2468-A4-1
Naïla--Intermezzo.50115-RDiamond Disc50115-L2468-A4-1Diamond Disc50115-R2452-A3-1
Aisha--Indian Intermezzo.50115-LDiamond Disc50115-R2452-C2-13Diamond Disc50115-L2468-A5-9
Naïla--Intermezzo.50115-RDiamond Disc50115-L2468-A5-9Diamond Disc50115-R2452-C2-13
7 Replies

@JDHelle 

You may add helper column as

image.png

with formula in H3

=ROW()*(COUNTIF(E$3:E3,D3)=0)+COUNTIF(E$3:E3,D3)*(MATCH(E3,D:D,0)+0.5)

(drag it down) and sort by this column

@Sergei Baklan 

 

Thank you so much!  I would have never figured that out.

@JDHelle , you are welcome

@Sergei Baklan 

 

Getting some strange results with this formula.  Beginning at 10004-L there seems to be a problem.

 

 

CouplingJoined RecordThis_Records_Join_Field 
10001-LDiamond Disc10001-R12002-L1-3Diamond Disc10001-L12003-L1-52
10001-RDiamond Disc10001-L12003-L1-5Diamond Disc10001-R12002-L1-33
10003-LDiamond Disc10003-R12006-F1-2 [1-1]Diamond Disc10003-L12007-B1-64.5
10003-RDiamond Disc10003-L12007-B1-6Diamond Disc10003-R12006-F1-2 [1-1]5
10004-LDiamond Disc10004-R12008-A1-4Diamond Disc10004-L12009-B2-36
10004-LDiamond Disc10004-R12008-A1-6Diamond Disc10004-L12009-B1-66.5
10004-RDiamond Disc10004-L12009-B2-3Diamond Disc10004-R12008-A1-47.5
10004-RDiamond Disc10004-L12009-B1-6Diamond Disc10004-R12008-A1-69
50043-LDiamond Disc50043-R3792-A3-5Diamond Disc50043-L2065-H8-310
50043-LDiamond Disc50043-R3792-A1-3Diamond Disc50043-L2065-H6-710.5
50043-RDiamond Disc50043-L2065-H8-3Diamond Disc50043-R3792-A3-511.5
50043-RDiamond Disc50043-L2065-H6-7Diamond Disc50043-R3792-A1-313
50045-LDiamond Disc50045-R3061-A14-2Diamond Disc50045-L3045-C1-714.5
50045-RDiamond Disc50045-L3045-C1-7Diamond Disc50045-R3061-A14-215
50052-LDiamond Disc50052-R3812-A2-5Diamond Disc50052-L2789-B1-116
50052-LDiamond Disc50052-R3812-B3-12Diamond Disc50052-L2789-B1-717
50052-LDiamond Disc50052-R3812-C2-47Diamond Disc50052-L2789-C2-4918
50052-LDiamond Disc50052-R3812-A1-5 [7-1]Diamond Disc50052-L2789-B4-1 [6-2]16.5
50052-RDiamond Disc50052-L2789-B1-1Diamond Disc50052-R3812-A2-517.5
50052-RDiamond Disc50052-L2789-B1-7Diamond Disc50052-R3812-B3-1218.5
50052-RDiamond Disc50052-L2789-C2-49Diamond Disc50052-R3812-C2-4719.5
50052-RDiamond Disc50052-L2789-B4-1 [6-2]Diamond Disc50052-R3812-A1-5 [7-1]23

@JDHelle 

In my case it sorts correctly

Coupling	Joined Record	This_Records_Join_Field	Helper
10001-L	Diamond Disc10001-R12002-L1-3	Diamond Disc10001-L12003-L1-5	3
10001-R	Diamond Disc10001-L12003-L1-5	Diamond Disc10001-R12002-L1-3	3.5
10003-L	Diamond Disc10003-R12006-F1-2 [1-1]	Diamond Disc10003-L12007-B1-6	5
10003-R	Diamond Disc10003-L12007-B1-6	Diamond Disc10003-R12006-F1-2 [1-1]	5.5
10004-L	Diamond Disc10004-R12008-A1-4	Diamond Disc10004-L12009-B2-3	7
10004-R	Diamond Disc10004-L12009-B2-3	Diamond Disc10004-R12008-A1-4	7.5
10004-L	Diamond Disc10004-R12008-A1-6	Diamond Disc10004-L12009-B1-6	9
10004-R	Diamond Disc10004-L12009-B1-6	Diamond Disc10004-R12008-A1-6	9.5
50043-L	Diamond Disc50043-R3792-A3-5	Diamond Disc50043-L2065-H8-3	11
50043-R	Diamond Disc50043-L2065-H8-3	Diamond Disc50043-R3792-A3-5	11.5
50043-L	Diamond Disc50043-R3792-A1-3	Diamond Disc50043-L2065-H6-7	13
50043-R	Diamond Disc50043-L2065-H6-7	Diamond Disc50043-R3792-A1-3	13.5
50045-L	Diamond Disc50045-R3061-A14-2	Diamond Disc50045-L3045-C1-7	15
50045-R	Diamond Disc50045-L3045-C1-7	Diamond Disc50045-R3061-A14-2	15.5
50052-L	Diamond Disc50052-R3812-A2-5	Diamond Disc50052-L2789-B1-1	17
50052-R	Diamond Disc50052-L2789-B1-1	Diamond Disc50052-R3812-A2-5	17.5
50052-L	Diamond Disc50052-R3812-B3-12	Diamond Disc50052-L2789-B1-7	19
50052-R	Diamond Disc50052-L2789-B1-7	Diamond Disc50052-R3812-B3-12	19.5
50052-L	Diamond Disc50052-R3812-C2-47	Diamond Disc50052-L2789-C2-49	21
50052-R	Diamond Disc50052-L2789-C2-49	Diamond Disc50052-R3812-C2-47	21.5
50052-L	Diamond Disc50052-R3812-A1-5 [7-1]	Diamond Disc50052-L2789-B4-1 [6-2]	23
50052-R	Diamond Disc50052-L2789-B4-1 [6-2]	Diamond Disc50052-R3812-A1-5 [7-1]	23.5

If sort only by helper column Coupling will be not in ascending order (Sheet3 attached). To sort both, sort first by Coupling, after that apply formula to the first cell of Helper column and drag it down. Better to keep formula for the first Helper in separate cell and copy/paste it using formula bar

image.png

See Sheet2 attached. Or I missed something?

@Sergei Baklan 

 

I must be missing something.  

 

1. I sorted the Coupling Number column

2. Added the formula and drug is down the column

3. Copied the results of the Helper Column and pasted it back as a value, not a formula.

4. Sorted the Helper column.

 

The results are not correct.  The coupling numbers are not correct.

 

1. Sort by Coupling  2. Added Formula    3. Sorted by Helper
CouplingJoined RecordThis_Records_Join_FieldHelper CouplingJoined RecordThis_Records_Join_FieldHelper
50043-LDiamond Disc50043-R3792-A3-5Diamond Disc50043-L2065-H8-33 50043-LDiamond Disc50043-R3792-A3-5Diamond Disc50043-L2065-H8-33
50043-LDiamond Disc50043-R3792-A1-3Diamond Disc50043-L2065-H6-74 50043-LDiamond Disc50043-R3792-A1-3Diamond Disc50043-L2065-H6-74
50043-RDiamond Disc50043-L2065-H8-3Diamond Disc50043-R3792-A3-55 50043-RDiamond Disc50043-L2065-H8-3Diamond Disc50043-R3792-A3-55
50043-RDiamond Disc50043-L2065-H6-7Diamond Disc50043-R3792-A1-37.5 50043-RDiamond Disc50043-L2065-H6-7Diamond Disc50043-R3792-A1-37
50045-LDiamond Disc50045-R3061-A14-2Diamond Disc50045-L3045-C1-77 50045-LDiamond Disc50045-R3061-A14-2Diamond Disc50045-L3045-C1-77.5
50045-RDiamond Disc50045-L3045-C1-7Diamond Disc50045-R3061-A14-28 50045-RDiamond Disc50045-L3045-C1-7Diamond Disc50045-R3061-A14-28
50052-LDiamond Disc50052-R3812-A2-5Diamond Disc50052-L2789-B1-19 50052-LDiamond Disc50052-R3812-A2-5Diamond Disc50052-L2789-B1-19
50052-LDiamond Disc50052-R3812-B3-12Diamond Disc50052-L2789-B1-710 50052-LDiamond Disc50052-R3812-B3-12Diamond Disc50052-L2789-B1-79.5
50052-LDiamond Disc50052-R3812-C2-47Diamond Disc50052-L2789-C2-499.5 50052-LDiamond Disc50052-R3812-C2-47Diamond Disc50052-L2789-C2-4910
50052-LDiamond Disc50052-R3812-A1-5 [7-1]Diamond Disc50052-L2789-B4-1 [6-2]10.5 50052-LDiamond Disc50052-R3812-A1-5 [7-1]Diamond Disc50052-L2789-B4-1 [6-2]10.5
50052-RDiamond Disc50052-L2789-B1-1Diamond Disc50052-R3812-A2-511.5 50052-RDiamond Disc50052-L2789-B1-1Diamond Disc50052-R3812-A2-511.5
50052-RDiamond Disc50052-L2789-B1-7Diamond Disc50052-R3812-B3-1212.5 50052-RDiamond Disc50052-L2789-B1-7Diamond Disc50052-R3812-B3-1212.5
50052-RDiamond Disc50052-L2789-C2-49Diamond Disc50052-R3812-C2-4715 50052-RDiamond Disc50052-L2789-C2-49Diamond Disc50052-R3812-C2-4715
50052-RDiamond Disc50052-L2789-B4-1 [6-2]Diamond Disc50052-R3812-A1-5 [7-1]17.5 50052-RDiamond Disc50052-L2789-B4-1 [6-2]Diamond Disc50052-R3812-A1-5 [7-1]17
50054-L Diamond Disc50054-R2732-C2-6Diamond Disc50054-L 2730-C2-517 50054-L Diamond Disc50054-R2732-C2-6Diamond Disc50054-L 2730-C2-517.5
50054-RDiamond Disc50054-L 2730-C2-5Diamond Disc50054-R2732-C2-618 50054-RDiamond Disc50054-L 2730-C2-5Diamond Disc50054-R2732-C2-618
50056-LDiamond Disc50056-R2733-A3-3Diamond Disc50056-L2729-B2-919.5 50056-LDiamond Disc50056-R2733-A3-3Diamond Disc50056-L2729-B2-919.5
50056-LDiamond Disc50056-R2733-A3-6Diamond Disc50056-L2729-B1-120.5 50056-LDiamond Disc50056-R2733-A3-6Diamond Disc50056-L2729-B1-120.5
50056-RDiamond Disc50056-L2729-B2-9Diamond Disc50056-R2733-A3-321 50056-RDiamond Disc50056-L2729-B2-9Diamond Disc50056-R2733-A3-321
50056-RDiamond Disc50056-L2729-B1-1Diamond Disc50056-R2733-A3-623.5 50056-RDiamond Disc50056-L2729-B1-1Diamond Disc50056-R2733-A3-623.5
50058-LDiamond Disc50058-R12901-8Diamond Disc50058-L9771-8#N/A 50058-LDiamond Disc50058-R12901-8Diamond Disc50058-L9771-8#N/A
50058-RDiamond Disc50058-L9771-8Diamond Disc50058-R12901-8#N/A 50058-RDiamond Disc50058-L9771-8Diamond Disc50058-R12901-8#N/A

 

@JDHelle 

Please see in Sheet4 attached from left to right all steps

1) We have unsorted range

image.png

2) We sort it by Coupling

image.png

3) We add Helper column - correct formula in first cell and drag it down

image.png

4) We sort entire range by Helper column

image.png

 

It works. Perhaps you may attach sample file removing sensitive information and keeping only dozen or so rows to sort.