 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / October 2004Re: Sort 8 numbers by the 6&7th digits???
Hello, I have a gigantic spreadsheet, and its all 8 numbers. We are trying to sort based on the 6 and 7th digits.. 40010101 is: Aisle bay level position 40 01 01 01 Aisles are 40 to 43.. bays are 01 to 56... the level, starts at 01, and goes all the way to 11. positions are only either 01, or 02. We are trying to sort only levels 07, 08, and 09. All the rest can be discarded.... A1 40010101 40010601 40010701 40010702 41010202 42060801 42060802 42080901 43030802 44041101 44051102........ is their an easy formula to sort all 14,000 of these ? Thanks ! I tried =RIGHT(a:a14000,4) <6, (>10)) Angela, =MID(A1,6,2) will return the 6&7th characters. If you do this on an adajcent column, you can then sort by this column -- Kiera Born ignorant and still perfecting the ar Thanks Kieran ! :) >Angela, > >=MID(A1,6,2) will return the 6&7th characters. > >If you do this on an adajcent column, you can then sort by this column. You may want to separate the values into different columns, too. Insert 4 columns to the right of this column. data|text to columns fixed width and plop it into that adjacent column. You could format those helper columns: format|cells|number tab custom 00 It might make it easier for the next project. > Hello, > I have a gigantic spreadsheet, and its all 8 numbers. [quoted text clipped - 31 lines] > > I tried =RIGHT(a:a14000,4) <6, (>10))  SignatureDave Peterson ec35720@msn.com Dave, Many Thanks ! >You may want to separate the values into different columns, too. > [quoted text clipped - 45 lines] >> >> I tried =RIGHT(a:a14000,4) <6, (>10)) One way .. Assuming data is in Sheet1 A2:A14000 Put in B2: =IF(ISNA(MATCH(MID($A2,5,2),{"07"},0)),"",ROW()) Put in C2: =IF(ISNA(MATCH(MID($A2,5,2),{"08"},0)),"",ROW()) Put in D2: =IF(ISNA(MATCH(MID($A2,5,2),{"09"},0)),"",ROW()) Select B2:D2, copy down to D14000 In Sheet2 -------------- Put in A2: =IF(ISERROR(MATCH(SMALL(Sheet1!B:B,ROW(A1)),Sheet1!B:B,0)),"",OFFSET(Sheet1! $A$1,MATCH(SMALL(Sheet1!B:B,ROW(A1)),Sheet1!B:B,0)-1,)) Copy across to C2, fill down until blanks appear for all 3 cols A to C Col A will extract all the o7's, Col B will extract all the o8's, and Col C will extract all the o9's For the sample data in the post, it'll appear in cols A to C as: 40010701 42060801 42080901 40010702 42060802 43030802 Signature-- Rgds Max xl 97 --- Please respond in thread xdemechanik <at>yahoo<dot>com ---- > Hello, > I have a gigantic spreadsheet, and its all 8 numbers. [quoted text clipped - 31 lines] > > I tried =RIGHT(a:a14000,4) <6, (>10)) > We are trying to sort only levels 07, 08, and 09. > All the rest can be discarded.... Clarification: The suggested set-up was focusing on the above lines Signature-- Rgds Max xl 97 --- Please respond in thread xdemechanik <at>yahoo<dot>com ---- Max, Thanks ! Everyone here is so helpful, this is the best newsgroup ! :) >One way .. > [quoted text clipped - 24 lines] >40010702 42060802 > 43030802 > Max, > Thanks ! > Everyone here is so helpful, this is the best newsgroup ! > :) You're welcome, Angela ! And thanks for the feedback Trust what was suggested to your post worked for you .. that's important, too <g> Signature-- Rgds Max xl 97 --- Please respond in thread xdemechanik <at>yahoo<dot>com ---- |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.