Forum Discussion

JDHelle's avatar
JDHelle
Copper Contributor
Jul 08, 2019

Sort Complication

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 num...
Excel on Mac
JDHelle's avatar
JDHelle
Copper Contributor
Jul 08, 2019

SergeiBaklan 

 

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

  • JDHelle's avatar
    JDHelle
    Copper Contributor
    Jul 09, 2019

    SergeiBaklan 

     

    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
    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Jul 09, 2019

      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

      See Sheet2 attached. Or I missed something?

      • JDHelle's avatar
        JDHelle
        Copper Contributor
        Jul 10, 2019

        SergeiBaklan 

         

        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

         

Resources