Forum Discussion
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...
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
JDHelle , you are welcome
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 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?