SOLVED

Data sorting

%3CLINGO-SUB%20id%3D%22lingo-sub-2375774%22%20slang%3D%22en-US%22%3EData%20sorting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2375774%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20everyone%2C%3C%2FP%3E%3CP%3EI%20have%20a%20very%20long%20list%20of%20data%20showing%20image%20file%20path%20names%20from%20multiple%20folders.%20I%20want%20to%20be%20able%20to%20sort%20this%20data%20into%20five%20columns%20(CAM1%2CCAM2%2CCAM3%2CCAM4%20and%20Sphere).%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20able%20to%20copy%20and%20paste%20the%20data%20but%20it%20takes%20a%20significant%20amount%20of%20time%20to%20sort%20through%20as%20the%20sequence%20is%3C%2FP%3E%3CP%3ETrackbCAM1%2C%20TrackbCAM2%2C%20TrackbCAM3%2C%20TrackbCAM4%2C%20trackbsphere%2C%20TrackcCAM1%2C%26nbsp%3BTrackcCAM2%2C%26nbsp%3BTrackcCAM3%20etc....%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDoes%20anyone%20know%20a%20quick%20way%20to%20sort%20this%20in%20excel%3F%20Any%20help%20would%20be%20very%20much%20appreciated.%3C%2FP%3E%3CP%3Ethanks%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2375774%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ETraining%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2375892%22%20slang%3D%22en-US%22%3ERe%3A%20Data%20sorting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2375892%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1045941%22%20target%3D%22_blank%22%3E%40AndrewEastop%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20you%20have%20Excel%20in%20Microsoft%20365%2C%20you%20can%20use%20the%20new%20FILTER%20function%20for%20this.%3C%2FP%3E%0A%3CP%3EOtherwise%2C%20Advance%20Filter%20(Data%20tab%20of%20the%20ribbon%20%26gt%3B%20Advanced)%20is%20a%20quick%20way%20to%20do%20this.%3C%2FP%3E%0A%3CP%3EFirst%2C%20insert%20a%20header%20in%20A1.%20I%20used%20File%2C%20but%20it%20can%20be%20anything.%3C%2FP%3E%0A%3CP%3ESet%20up%20a%20criteria%20range%3A%20File%20in%20cell%20C1%20(spelled%20exactly%20as%20in%20A1)%20and%20*CAM1*%20in%20C2.%3C%2FP%3E%0A%3CP%3EAlso%20set%20up%20a%20copy-to%20range%3A%20File%20in%20cell%20D1%20(as%20in%20A1).%3C%2FP%3E%0A%3CP%3ESelect%20A1%20and%20click%20Advanced.%3C%2FP%3E%0A%3CP%3EIgnore%20the%20warning%20that%20Excel%20puts%20up.%3C%2FP%3E%0A%3CP%3ESpecify%20the%20criteria%20range%2C%20select%20'Copy%20to%20another%20range'%20and%20specify%20the%20Copy%20to%20range%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22S0432.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F282665i82AFD4E2C01F5289%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22S0432.png%22%20alt%3D%22S0432.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EClick%20OK.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EDo%20the%20same%20for%20the%20other%20cameras.%20It%20took%20me%20less%20time%20to%20execute%20than%20to%20write%20this%20down.%3C%2FP%3E%0A%3CP%3ESee%20the%20attached%20version.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2376105%22%20slang%3D%22en-US%22%3ERe%3A%20Data%20sorting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2376105%22%20slang%3D%22en-US%22%3EThank%20you%20very%20much%20Hans%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2380583%22%20slang%3D%22en-US%22%3ERe%3A%20Data%20sorting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2380583%22%20slang%3D%22en-US%22%3E%3CP%3EWould%20anyone%20know%20how%20to%20get%20the%20file%20path%20to%20show%20in%20numerical%20order%20for%20this%20data%20also%3F%3C%2FP%3E%3CP%3EAt%20the%20moment%20it%20goes%201%2C10%2C100%2C1000%2C%201001%2C1002%20etc%20but%20I%20would%20rather%20it%20went%201%2C2%2C3%2C4%2C5...%26nbsp%3B%3C%2FP%3E%3CP%3EI%20tried%20the%20az%20sort%20button%20but%20nothing%20changed%20%3CLI-EMOJI%20id%3D%22lia_disappointed-face%22%20title%3D%22%3Adisappointed_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3CP%3Ethanks%26nbsp%3B%3C%2FP%3E%3CP%3EAndrew%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2380682%22%20slang%3D%22en-US%22%3ERe%3A%20Data%20sorting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2380682%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1045941%22%20target%3D%22_blank%22%3E%40AndrewEastop%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESee%20the%20attached%20version%2C%20now%20a%20.xlsm.%20You%20will%20have%20to%20allow%20macros.%3C%2FP%3E%0A%3CP%3EI%20have%20already%20run%20the%20FixNum%20macro%20on%20columns%20C%2C%20F%2C%20...%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2380760%22%20slang%3D%22en-US%22%3ERe%3A%20Data%20sorting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2380760%22%20slang%3D%22en-US%22%3EGreat%20job%20Hans%2C%20thank%20you%20very%20much!%20How%20exactly%20did%20you%20do%20it%20lol%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2380776%22%20slang%3D%22en-US%22%3ERe%3A%20Data%20sorting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2380776%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1045941%22%20target%3D%22_blank%22%3E%40AndrewEastop%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20macro%20loads%20a%20column%20into%20an%20array%20in%20memory.%3C%2FP%3E%0A%3CP%3EIt%20then%20uses%20string%20functions%20such%20as%20InStr%2C%20Left%2C%20Mid%20and%20Format%20to%20expand%20the%20numbers%20after%20the%20camera%20such%20as%201%2C%2012%2C%20103%20to%20four%20digits%3A%200001%2C%200012%2C%200103.%3C%2FP%3E%0A%3CP%3EThe%20new%20values%20are%20written%20to%20the%20column%20to%20the%20right.%20The%20two%20columns%20are%20sorted%20on%20the%20second%20column%20(the%20one%20with%20the%20expanded%20numbers).%3C%2FP%3E%0A%3CP%3EFinally%2C%20that%20second%20column%20is%20cleared%20again.%3C%2FP%3E%0A%3CP%3EBecause%20array%20operations%20are%20relatively%20fast%2C%20the%20macro%20only%20takes%20a%20few%20seconds%20to%20run.%3C%2FP%3E%0A%3CP%3EYou%20can%20view%20the%20code%20in%20the%20Visual%20Basic%20Editor.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Hello everyone,

I have a very long list of data showing image file path names from multiple folders. I want to be able to sort this data into five columns (CAM1,CAM2,CAM3,CAM4 and Sphere). 

I am able to copy and paste the data but it takes a significant amount of time to sort through as the sequence is

TrackbCAM1, TrackbCAM2, TrackbCAM3, TrackbCAM4, trackbsphere, TrackcCAM1, TrackcCAM2, TrackcCAM3 etc....

 

Does anyone know a quick way to sort this in excel? Any help would be very much appreciated.

thanks 

9 Replies
best response confirmed by AndrewEastop (Occasional Contributor)
Solution

@AndrewEastop 

If you have Excel in Microsoft 365, you can use the new FILTER function for this.

Otherwise, Advance Filter (Data tab of the ribbon > Advanced) is a quick way to do this.

First, insert a header in A1. I used File, but it can be anything.

Set up a criteria range: File in cell C1 (spelled exactly as in A1) and *CAM1* in C2.

Also set up a copy-to range: File in cell D1 (as in A1).

Select A1 and click Advanced.

Ignore the warning that Excel puts up.

Specify the criteria range, select 'Copy to another range' and specify the Copy to range:

 

S0432.png

Click OK.

 

Do the same for the other cameras. It took me less time to execute than to write this down.

See the attached version.

Thank you very much Hans

Would anyone know how to get the file path to show in numerical order for this data also?

At the moment it goes 1,10,100,1000, 1001,1002 etc but I would rather it went 1,2,3,4,5... 

I tried the az sort button but nothing changed

thanks 

Andrew 

@AndrewEastop 

See the attached version, now a .xlsm. You will have to allow macros.

I have already run the FixNum macro on columns C, F, ...

Great job Hans, thank you very much! How exactly did you do it lol?

@AndrewEastop 

The macro loads a column into an array in memory.

It then uses string functions such as InStr, Left, Mid and Format to expand the numbers after the camera such as 1, 12, 103 to four digits: 0001, 0012, 0103.

The new values are written to the column to the right. The two columns are sorted on the second column (the one with the expanded numbers).

Finally, that second column is cleared again.

Because array operations are relatively fast, the macro only takes a few seconds to run.

You can view the code in the Visual Basic Editor.

Is there a way in which I join two macros together to make one? I have made your first solution into a macro aswell and thought it would make things very simply if i was able to join them together!

thanks 

Andrew 

@AndrewEastop 

See the attached version. The macro FixNumbers will now do it all.

thank you Hans!