Jul 08 2019 01:27 PM
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 | |||
Title | Coupling | Joined Record | This_Records_Join_Field |
Aisha--Indian Intermezzo. | 50115-L | Diamond Disc50115-R2452-A1-46 | Diamond Disc50115-L2468-A1-19 SA |
Aisha--Indian Intermezzo. | 50115-L | Diamond Disc50115-R2452-A1-19 | Diamond Disc50115-L2468-A1-7 |
Aisha--Indian Intermezzo. | 50115-L | Diamond Disc50115-R2452-A3-1 | Diamond Disc50115-L2468-A4-1 |
Aisha--Indian Intermezzo. | 50115-L | Diamond Disc50115-R2452-C2-13 | Diamond Disc50115-L2468-A5-9 |
Naïla--Intermezzo. | 50115-R | Diamond Disc50115-L2468-A5-9 | Diamond Disc50115-R2452-C2-13 |
Naïla--Intermezzo. | 50115-R | Diamond Disc50115-L2468-A1-19 SA | Diamond Disc50115-R2452-A1-46 |
Naïla--Intermezzo. | 50115-R | Diamond Disc50115-L2468-A4-1 | Diamond Disc50115-R2452-A3-1 |
Naïla--Intermezzo. | 50115-R | Diamond Disc50115-L2468-A1-7 | Diamond Disc50115-R2452-A1-19 |
Desired Results | |||
Title | Coupling | Joined Record | This_Records_Join_Field |
Aisha--Indian Intermezzo. | 50115-L | Diamond Disc50115-R2452-A1-46 | Diamond Disc50115-L2468-A1-19 SA |
Naïla--Intermezzo. | 50115-R | Diamond Disc50115-L2468-A1-19 SA | Diamond Disc50115-R2452-A1-46 |
Aisha--Indian Intermezzo. | 50115-L | Diamond Disc50115-R2452-A1-19 | Diamond Disc50115-L2468-A1-7 |
Naïla--Intermezzo. | 50115-R | Diamond Disc50115-L2468-A1-7 | Diamond Disc50115-R2452-A1-19 |
Aisha--Indian Intermezzo. | 50115-L | Diamond Disc50115-R2452-A3-1 | Diamond Disc50115-L2468-A4-1 |
Naïla--Intermezzo. | 50115-R | Diamond Disc50115-L2468-A4-1 | Diamond Disc50115-R2452-A3-1 |
Aisha--Indian Intermezzo. | 50115-L | Diamond Disc50115-R2452-C2-13 | Diamond Disc50115-L2468-A5-9 |
Naïla--Intermezzo. | 50115-R | Diamond Disc50115-L2468-A5-9 | Diamond Disc50115-R2452-C2-13 |
Jul 08 2019 02:24 PM
You may add helper column as
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
Jul 08 2019 02:59 PM
Jul 09 2019 12:32 PM
Getting some strange results with this formula. Beginning at 10004-L there seems to be a problem.
Coupling | Joined Record | This_Records_Join_Field | |
10001-L | Diamond Disc10001-R12002-L1-3 | Diamond Disc10001-L12003-L1-5 | 2 |
10001-R | Diamond Disc10001-L12003-L1-5 | Diamond Disc10001-R12002-L1-3 | 3 |
10003-L | Diamond Disc10003-R12006-F1-2 [1-1] | Diamond Disc10003-L12007-B1-6 | 4.5 |
10003-R | Diamond Disc10003-L12007-B1-6 | Diamond Disc10003-R12006-F1-2 [1-1] | 5 |
10004-L | Diamond Disc10004-R12008-A1-4 | Diamond Disc10004-L12009-B2-3 | 6 |
10004-L | Diamond Disc10004-R12008-A1-6 | Diamond Disc10004-L12009-B1-6 | 6.5 |
10004-R | Diamond Disc10004-L12009-B2-3 | Diamond Disc10004-R12008-A1-4 | 7.5 |
10004-R | Diamond Disc10004-L12009-B1-6 | Diamond Disc10004-R12008-A1-6 | 9 |
50043-L | Diamond Disc50043-R3792-A3-5 | Diamond Disc50043-L2065-H8-3 | 10 |
50043-L | Diamond Disc50043-R3792-A1-3 | Diamond Disc50043-L2065-H6-7 | 10.5 |
50043-R | Diamond Disc50043-L2065-H8-3 | Diamond Disc50043-R3792-A3-5 | 11.5 |
50043-R | Diamond Disc50043-L2065-H6-7 | Diamond Disc50043-R3792-A1-3 | 13 |
50045-L | Diamond Disc50045-R3061-A14-2 | Diamond Disc50045-L3045-C1-7 | 14.5 |
50045-R | Diamond Disc50045-L3045-C1-7 | Diamond Disc50045-R3061-A14-2 | 15 |
50052-L | Diamond Disc50052-R3812-A2-5 | Diamond Disc50052-L2789-B1-1 | 16 |
50052-L | Diamond Disc50052-R3812-B3-12 | Diamond Disc50052-L2789-B1-7 | 17 |
50052-L | Diamond Disc50052-R3812-C2-47 | Diamond Disc50052-L2789-C2-49 | 18 |
50052-L | Diamond Disc50052-R3812-A1-5 [7-1] | Diamond Disc50052-L2789-B4-1 [6-2] | 16.5 |
50052-R | Diamond Disc50052-L2789-B1-1 | Diamond Disc50052-R3812-A2-5 | 17.5 |
50052-R | Diamond Disc50052-L2789-B1-7 | Diamond Disc50052-R3812-B3-12 | 18.5 |
50052-R | Diamond Disc50052-L2789-C2-49 | Diamond Disc50052-R3812-C2-47 | 19.5 |
50052-R | Diamond Disc50052-L2789-B4-1 [6-2] | Diamond Disc50052-R3812-A1-5 [7-1] | 23 |
Jul 09 2019 02:07 PM
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
See Sheet2 attached. Or I missed something?
Jul 10 2019 09:09 AM
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 | ||||||
Coupling | Joined Record | This_Records_Join_Field | Helper | Coupling | Joined Record | This_Records_Join_Field | Helper | |
50043-L | Diamond Disc50043-R3792-A3-5 | Diamond Disc50043-L2065-H8-3 | 3 | 50043-L | Diamond Disc50043-R3792-A3-5 | Diamond Disc50043-L2065-H8-3 | 3 | |
50043-L | Diamond Disc50043-R3792-A1-3 | Diamond Disc50043-L2065-H6-7 | 4 | 50043-L | Diamond Disc50043-R3792-A1-3 | Diamond Disc50043-L2065-H6-7 | 4 | |
50043-R | Diamond Disc50043-L2065-H8-3 | Diamond Disc50043-R3792-A3-5 | 5 | 50043-R | Diamond Disc50043-L2065-H8-3 | Diamond Disc50043-R3792-A3-5 | 5 | |
50043-R | Diamond Disc50043-L2065-H6-7 | Diamond Disc50043-R3792-A1-3 | 7.5 | 50043-R | Diamond Disc50043-L2065-H6-7 | Diamond Disc50043-R3792-A1-3 | 7 | |
50045-L | Diamond Disc50045-R3061-A14-2 | Diamond Disc50045-L3045-C1-7 | 7 | 50045-L | Diamond Disc50045-R3061-A14-2 | Diamond Disc50045-L3045-C1-7 | 7.5 | |
50045-R | Diamond Disc50045-L3045-C1-7 | Diamond Disc50045-R3061-A14-2 | 8 | 50045-R | Diamond Disc50045-L3045-C1-7 | Diamond Disc50045-R3061-A14-2 | 8 | |
50052-L | Diamond Disc50052-R3812-A2-5 | Diamond Disc50052-L2789-B1-1 | 9 | 50052-L | Diamond Disc50052-R3812-A2-5 | Diamond Disc50052-L2789-B1-1 | 9 | |
50052-L | Diamond Disc50052-R3812-B3-12 | Diamond Disc50052-L2789-B1-7 | 10 | 50052-L | Diamond Disc50052-R3812-B3-12 | Diamond Disc50052-L2789-B1-7 | 9.5 | |
50052-L | Diamond Disc50052-R3812-C2-47 | Diamond Disc50052-L2789-C2-49 | 9.5 | 50052-L | Diamond Disc50052-R3812-C2-47 | Diamond Disc50052-L2789-C2-49 | 10 | |
50052-L | Diamond Disc50052-R3812-A1-5 [7-1] | Diamond Disc50052-L2789-B4-1 [6-2] | 10.5 | 50052-L | Diamond Disc50052-R3812-A1-5 [7-1] | Diamond Disc50052-L2789-B4-1 [6-2] | 10.5 | |
50052-R | Diamond Disc50052-L2789-B1-1 | Diamond Disc50052-R3812-A2-5 | 11.5 | 50052-R | Diamond Disc50052-L2789-B1-1 | Diamond Disc50052-R3812-A2-5 | 11.5 | |
50052-R | Diamond Disc50052-L2789-B1-7 | Diamond Disc50052-R3812-B3-12 | 12.5 | 50052-R | Diamond Disc50052-L2789-B1-7 | Diamond Disc50052-R3812-B3-12 | 12.5 | |
50052-R | Diamond Disc50052-L2789-C2-49 | Diamond Disc50052-R3812-C2-47 | 15 | 50052-R | Diamond Disc50052-L2789-C2-49 | Diamond Disc50052-R3812-C2-47 | 15 | |
50052-R | Diamond Disc50052-L2789-B4-1 [6-2] | Diamond Disc50052-R3812-A1-5 [7-1] | 17.5 | 50052-R | Diamond Disc50052-L2789-B4-1 [6-2] | Diamond Disc50052-R3812-A1-5 [7-1] | 17 | |
50054-L | Diamond Disc50054-R2732-C2-6 | Diamond Disc50054-L 2730-C2-5 | 17 | 50054-L | Diamond Disc50054-R2732-C2-6 | Diamond Disc50054-L 2730-C2-5 | 17.5 | |
50054-R | Diamond Disc50054-L 2730-C2-5 | Diamond Disc50054-R2732-C2-6 | 18 | 50054-R | Diamond Disc50054-L 2730-C2-5 | Diamond Disc50054-R2732-C2-6 | 18 | |
50056-L | Diamond Disc50056-R2733-A3-3 | Diamond Disc50056-L2729-B2-9 | 19.5 | 50056-L | Diamond Disc50056-R2733-A3-3 | Diamond Disc50056-L2729-B2-9 | 19.5 | |
50056-L | Diamond Disc50056-R2733-A3-6 | Diamond Disc50056-L2729-B1-1 | 20.5 | 50056-L | Diamond Disc50056-R2733-A3-6 | Diamond Disc50056-L2729-B1-1 | 20.5 | |
50056-R | Diamond Disc50056-L2729-B2-9 | Diamond Disc50056-R2733-A3-3 | 21 | 50056-R | Diamond Disc50056-L2729-B2-9 | Diamond Disc50056-R2733-A3-3 | 21 | |
50056-R | Diamond Disc50056-L2729-B1-1 | Diamond Disc50056-R2733-A3-6 | 23.5 | 50056-R | Diamond Disc50056-L2729-B1-1 | Diamond Disc50056-R2733-A3-6 | 23.5 | |
50058-L | Diamond Disc50058-R12901-8 | Diamond Disc50058-L9771-8 | #N/A | 50058-L | Diamond Disc50058-R12901-8 | Diamond Disc50058-L9771-8 | #N/A | |
50058-R | Diamond Disc50058-L9771-8 | Diamond Disc50058-R12901-8 | #N/A | 50058-R | Diamond Disc50058-L9771-8 | Diamond Disc50058-R12901-8 | #N/A |
Jul 10 2019 02:39 PM
Please see in Sheet4 attached from left to right all steps
1) We have unsorted range
2) We sort it by Coupling
3) We add Helper column - correct formula in first cell and drag it down
4) We sort entire range by Helper column
It works. Perhaps you may attach sample file removing sensitive information and keeping only dozen or so rows to sort.