Home

Sorting on Last 2 Digits of Number

%3CLINGO-SUB%20id%3D%22lingo-sub-656944%22%20slang%3D%22en-US%22%3ESorting%20on%20Last%202%20Digits%20of%20Number%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-656944%22%20slang%3D%22en-US%22%3E%3CP%3EIs%20there%20a%20way%20to%20sort%20data%20on%20the%20last%202%20digits%20of%20a%208%20digit%20number%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-656944%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-657121%22%20slang%3D%22en-US%22%3ERe%3A%20Sorting%20on%20Last%202%20Digits%20of%20Number%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-657121%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F351547%22%20target%3D%22_blank%22%3E%40HSSC1%3C%2FA%3E%26nbsp%3B%2C%20please%20check%20this%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fexcelribbon.tips.net%2FT010905_Sorting_by_the_Last_Digits_in_Variable_Length_Data.html%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fexcelribbon.tips.net%2FT010905_Sorting_by_the_Last_Digits_in_Variable_Length_Data.html%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-657389%22%20slang%3D%22en-US%22%3ERe%3A%20Sorting%20on%20Last%202%20Digits%20of%20Number%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-657389%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F351547%22%20target%3D%22_blank%22%3E%40HSSC1%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20your%208-digit%20numbers%20are%20in%20A2%3AA10%2C%20the%20formula%20in%20B2%2C%20copied%20down%20rows%2C%20to%20sort%20the%20numbers%20based%20on%20their%20last%202%20digits%20is%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DLOOKUP(PI()%2C%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3E1%2F(--RIGHT(A%242%3AA%2410%2C2)%3DAGGREGATE(15%2C4%2C--RIGHT(A%242%3AA%2410%2C2)%2CROW()-1))%2C%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3EA%242%3AA%2410)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3ESee%20the%20sample%20in%20the%20attached%20file.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
HSSC1
Occasional Visitor

Is there a way to sort data on the last 2 digits of a 8 digit number?

2 Replies

@HSSC1 

If your 8-digit numbers are in A2:A10, the formula in B2, copied down rows, to sort the numbers based on their last 2 digits is: 

=LOOKUP(PI(),
1/(--RIGHT(A$2:A$10,2)=AGGREGATE(15,4,--RIGHT(A$2:A$10,2),ROW()-1)),
A$2:A$10)

See the sample in the attached file.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies