Ranking Numbers with Text

I am trying to sort a list of serial numbers containing alpha an
numeric text into ascending order so LOOKUP will work. ie:
5KJJAHAS63PL84681 input into col. E
5KJJAHAS43PL84685
5KJJAHASx3PL84683
5KJJAHAS83PL84686
5KJJAHAS63PL84689
5KJJAHAS43PL84684
5KJJAHAS23PL84682
5KJJAHAS03PL84688
5KJJAHASx3PL84610
5KJJAHAS83PL84690
5KJJAHAS83PL84691
Only the ninth and 13 thru 17 digits change. 
Worksheets are protected except for inputs.
I need the sort to use the LOOKUP function.
I tried =IF(E3>0,(RIGHT(E3,5)*1),999999) to RANK by the last 5
=RANK(D3,D$3:D$102,1)+COUNTIF(D$3:D3,D3)-1,
but then LOOKUP doesn't recognize when I use 
=OFFSET(E$3,MATCH(ROW()-2,C$3:C$102,0)-1,0).
Thanks for your tutorlege, Jerrygolfe

--
Message posted from http://www.ExcelForum.com

0
12/17/2003 8:25:09 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
1977 Views

Similar Articles

[PageSpeed] 38

Jerry

Maybe you can use VLOOKUP() or HLOOKUP() instead
and set the fourth argument to FALSE, e.g.
=VLOOKUP(A1,G3:H56,4,FALSE)

-- 
Best Regards
Leo Heuser

Followup to newsgroup only please.

"jerrygolfer" <jerrygolfer.yllzz@excelforum-nospam.com> skrev i en
meddelelse news:jerrygolfer.yllzz@excelforum-nospam.com...
> I am trying to sort a list of serial numbers containing alpha and
> numeric text into ascending order so LOOKUP will work. ie:
> 5KJJAHAS63PL84681 input into col. E
> 5KJJAHAS43PL84685
> 5KJJAHASx3PL84683
> 5KJJAHAS83PL84686
> 5KJJAHAS63PL84689
> 5KJJAHAS43PL84684
> 5KJJAHAS23PL84682
> 5KJJAHAS03PL84688
> 5KJJAHASx3PL84610
> 5KJJAHAS83PL84690
> 5KJJAHAS83PL84691
> Only the ninth and 13 thru 17 digits change.
> Worksheets are protected except for inputs.
> I need the sort to use the LOOKUP function.
> I tried =IF(E3>0,(RIGHT(E3,5)*1),999999) to RANK by the last 5
> =RANK(D3,D$3:D$102,1)+COUNTIF(D$3:D3,D3)-1,
> but then LOOKUP doesn't recognize when I use
> =OFFSET(E$3,MATCH(ROW()-2,C$3:C$102,0)-1,0).
> Thanks for your tutorlege, Jerrygolfer
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
leo.heuser (111)
12/17/2003 8:47:03 PM
Reply:

Similar Artilces:

Dropdown List
Excel 2002 Our Excel company timesheet has some dropdown list boxes that allow the user to select what text will appear in the associated cell (e.g. a short list of names of the person who will authorise the timesheet). The list is defined in the Source field of the List function in the Data Validation dialog box. The font size of the text in the cell is 18pt, but the size of the text in the drop-down list is so small, it cannot be read. Is there any way to increase the size of the text in the dropdown list? -- Mike -Please remove 'safetycatch' from email address before firing...

Export text to TextBox into Excel
Good Afternoon, Access 2003 I developed a module to open an excel file and then I would like export a text from a form to textbox into excel. My example works fine the len string is <255 characters. If is > add nothing into text box. Function ExportTextToExcel() Dim xlApp As Excel.Application Dim xlBook As Excel.Workbook Dim strText As String Set xlApp = CreateObject("Excel.Application") Set xlBook = xlApp.Workbooks.Open("C:\BLK2DB\AllocSystSQL\Temp\NET Statistics_2007.xls", , True) Set xlBook = GetObject("C:\Test.xls&q...

Counting the frequency of a text string
I've inherited some data to work with that I'm not sure how to approach. Basically, multiple answers are contained within a cell. I need to extract a sum of each individual answer. The data looks like this: Special Education Students;#Economically Disadvantaged Students;#Minority Students;#Regular Classroom Students;#Gifted and Talented Students Special Education Students;#Economically Disadvantaged Students;#Minority Students Special Education Students;#Economically Disadvantaged Students;#Minority Students;#Regular Classroom Students;#Gifted and Talented Students Special Edu...

Changing a Number in a Column Using Arrays?
I am trying to change a number in the left column to the number in the adjacent column. My setup looks like this. Number to Change Change to this Number 182001 182000 182002 182000 193002 193000 Code New Code (from the Change to this Number column) 181000 181000 182000 182000 182001 182000 182002 18...

RMS allow adding reorder number at matrix item creation
When creating a new matrix item it would be useful to be able to add in supplier reorder numbers at the component item screen (as you can with reorder point and restock levels by choosing columns), rather then having to go to the Inventory wizard and running Task 140 after the items have been created. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Micr...

COUNTIF with text fields
I have two list, A & B. B is a fixed list with the acceptable entries for list A. List A items must be found in list B and there can be no duplicates in list A. Both lists are format as text and include entries such as "7", "07" which should be recognized as not equal. COUNTIF returns a count of "2" for these values. Desired Invalid entries in A return "0" One valid entry returns a "1" Two equal entries in List A return "2" Three equal entries in List A return "3" etc. Jim /* WebApp Hosting http://www.advancedde...

SUM of Numbers with colons (:) such as Hours & Minutes
Does anyone know how to get the SUM of a column with numbers formated as Time (hh:mm) useing the SUM formula to get the total numbers of hours in that column. For example: Day-1 10:45 Day-2 11:05 Day-3 09:10 Total 31:00 Help is very appreciated. Wally Hi Wally for your formula use =SUM(B1:B3)*24 and format it as a number (format / cells - number) Cheers JulieD "CLARKSON PHARMACY" <CLARKSON.PHARMACY@VERIZON.NET> wrote in message news:XaCZd.3302$ed6.530@trndny06... > Does anyone know how to get the SUM of a column with numbers formated as...

matches in two collums of numbers?
How do I find out how many matches in two collums of numbers in Excel? http://www.cpearson.com/excel/duplicat.htm -- Regards, Peo Sjoblom (No private emails please) "kdbeal" <kdbeal@discussions.microsoft.com> wrote in message news:15397E07-F564-4982-94F7-E250717770EB@microsoft.com... > How do I find out how many matches in two collums of numbers in Excel? ...

auto numbering 1,2,3,.....
Hi see you other post -- Regards Frank Kabel Frankfurt, Germany "Richard" <anonymous@discussions.microsoft.com> schrieb im Newsbeitrag news:30C9E412-FF9E-4205-B800-461CD7B89EB8@microsoft.com... > Maybe you are trying to Autonumber the rows in Excel. here is the solution: In cell A1 type 1. In cell A2 type 2. Select both cells with the mouse. In cell A2 there is a thick square in the bottom left corner. Click it, hold the left mouse button and pull down. Now you see a yellow boxes showing the numbers - 2, 3, 4... and so on. If you need the numbers from 1 to 10, release ...

Auto-Fit Text Box as Default
In Publisher, can 1 setup "Best-Fit" in textbox auto-fill as default?? As it stands now, I have to go in and change it "per" document, but would like to have this always on for all my new docs Thank you H wood I don't know a way that you can do that. I am confused by the statement that you have to change it "per" document since it is a change that is made per textbox. The problem with making text boxes best fit by default is that they can't be linked to by other text boxes. -- Computing should be about insight, not numbers or flash. "Hwood&...

Cell won't wrap all of the text in it
I have a merged cell set to wrap text. Vertical alignment is set to top (I've played around with this option). I tried doing Auto Fit for height and width, but it doesn't make a difference. The cell contains a large amount of text, and it wraps until the very end. Then it just continues on the same line, so I can't see it in the actual cell. I can see it in the formula bar. Also, if I increase the width greatly, more of the text appears. There is still plenty of room in the row height, but for some reason the text won't wrap down to the next line, no matter what I try. ...

converting lower case text to upper case?
I have Msft Excel, vsn 2002, Windows XP. I desire to change all lower text that is in a vertical column of cells to upper case. I just purchased Msft Excel for Dummies, but could not find a method there for doing it. Can anyone suggest a simple method for changing all of the vertical cells in a single column to Upper Case with one instruction? Best, Aaron One way: in an unused column, say J, enter: J1: =UPPER(A1) then copy down as far as necessary. Copy column J, select column A and choose Edit/Paste Special, selecting the Values radio button. Then clear column J. If you do ...

Automatically number multiple sheets
I am trying to automatically number 35 sheets within the same workbook, using the header/footer custom option. I am unable to get each tab to see one another and sucessfully number each tab (sheet) within the workbook. It will start to number a few, then the formatting will stop. I am running Office XP SP3, on XP SP2. All help greatly appreciated. Not quite sure what you're doing, but maybe you could use a macro: Option Explicit Sub testme01() Dim iCtr As Long For iCtr = 1 To Worksheets.Count With Worksheets(iCtr).PageSetup .LeftHeader = "Nu...

multipy group of numbers
is there a quick way to multipy a large group of numbers by 1,000? Further explanation, I have different numbers in about 2500 cells. I want to multiply each number times 1,000 and have that product of the # in the cell times 1,000 present in the cell. "Mary" wrote: > is there a quick way to multipy a large group of numbers by 1,000? Put 1000 in an empty cell. copy that cell select your range of cells to adjust. Edit|paste special|check multiply Clean up that helper cell. Mary wrote: > > Further explanation, I have different numbers in about 2500 cells. I want to...

How do I process text file?
Hello all. I am using VB 2008 and need to process text fies. Looking at MSDN Lib I see that two methods are used to read a text file: Using StreamReader Dim fileReader As System.IO.StreamReader Using ReadAlltext Dim fileReader As String fileReader = My.Computer.FileSystem.ReadAllText("C:\test.txt") Is one preferred obver the other? Any considerations I should look into? Although the files that will be processed are text, I have seen a "bad" non ASCII character get into them. I validate for this condition, but given the fact that it is a non ASCII char,...

Query Source for Text Box
Hi, I'm trying to set the value of a text cell as the sum of the values in one table with conditions based on returned values from linked tables: =DSum("V","T1","(SELECT Sum(Value) AS V FROM CostTypeLook INNER JOIN (CCLook INNER JOIN CostBase ON (CCLook.[Cost Centre] = CostBase.[Cost Centre]) AND (CCLook.Co = CostBase.Co)) ON (CostTypeLook.Account = CostBase.Account) AND (CostTypeLook.Co = CostBase.Co) WHERE CostTypeLook.[Cost Type]=10 AND CCLook.Flag='NP') AS T1") But of course it isn't working which is why I'm having to ask...

Contract lines - number of items
Hello, Just started with MS CRM 3.0. I have set up contracts and contract lines, based on a Time template, for my clients. Normally their contract duration is a year with monthly billing intervals, so my question is do I: - Enter the full year's minutes in the time allotment or can I (preferred): - Enter only a months minutes in the time allotment, but put Quantity at 12. My intention here is to reset the months minutes each month. TIA Tony I was just wondering if you had found out the procedure, i am in the same boat! TIA "Tony" wrote: > Hello, > > Just star...

time delay refresh on random number
Is it possible to have a time delay on a random number. For example the random number refresh every x seconds. Read this: http://www.cpearson.com/Excel/OnTime.aspx -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Bertrand" wrote: > Is it possible to have a time delay on a random number. For example the > random number refresh every x seconds. Bertrand, You could use the OnTime method to fire a macro that writes the random number to a cell or a variable once every six seconds or so. Run Random6...

Maximum number of attachments to an email message in Outlook 2003?
Is there a maximum number of attachments (word documents) to an email message? I have clients that have their work returned to them but do not have them zipped because of the automated process at their end. However the some documents were missing and it looks like anything of 30 attachments gets "missed". Does anyone know the maximum number allowed? Help gratefully received. I've been known to send 60-80 spam messages to our Exchange admin w/no problems. Not knowing the e-mail system(s) involved, I'm thinking you might be hitting message size limitations, file(...

Sync Text in a "Off-Page" Reference shape
Hello all, I am running Visio 2002 SR-1 here at work and having a slight problem with syncing the text in "Off-Page" Reference (OPR) shapes. If I have one OPR on page-1 and another one on page-2 I have to update the text manually in order for them to be the same. Is there an option to keep the two shape's text sync'd? Thanks Mark There is a way to keep the shape text in sync, but the option is only available when you first drop the OPR shape on the page. When you drop the shape, you'll see a "Keep shape text synchronized" option in the dialog box that comes...

Custom Format text XX:XXXX:XX
I would like to have text custom formatted so that if a 8 character words is typed, it is automatically broken up like this XX:XXXX:XX. This works just fine with numbers with a ##\:####\:## custom format, but it will not work for text. Any suggestions? -- mustard ------------------------------------------------------------------------ mustard's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=20017 View this thread: http://www.excelforum.com/showthread.php?threadid=346023 Number formatting only works on numbers. But you could use a little macro waiting for y...

How can I change positive numbers to negative, i.e. change 50 to -
I have a very long list of positive numbers that I have converted from a wordpad report. Actually it is our list of receivables. They have been converted from text, and therefore the ones that should be credits, or negative numbers (in context of receivables) appear as just plain positive numbers, such as 150.00. I don't know of a simpler way to turn these numbers to negative (i.e. -150.00) without F2'ing each one and putting a minus (-) sign in front of each one. It would save me so much time just to be able to change these numbers from positive to negative! If you could ...

Last number in a column shows in the total/summay line cell
I am looking for a formula that would have the last number listed in a column listed in the total or summary row of the spreadsheet. Column A lists the month of the year Column B lists # of files pending Jan 10 Feb 12 Mar 7 Summary -- I want this to show the last number in Column B -- in this example 7. Thank you. hi in a cell of your choosing, enter.. =OFFS...

I can't make text follow border of the Autoshape in Publisher 2003
You aren't going to have this happen in Publisher unless you are willing to do a lot of tweaking. You need a draw program for good results. Draw Plus is free. http://www.freeserifsoftware.com/ I have a method of following a path using WordArt and Publisher on my web page. Time consuming, but it works. I have a PDF explaining how. Scroll way down... http://msauer.mvps.org/wordart_in_microsoft_publisher.htm -- Mary Sauer MVP http://msauer.mvps.org/ "Danny" <Danny@discussions.microsoft.com> wrote in message news:46EDA756-110A-4EFB-AB5A-EB508B90DE27@microsoft.com... >...

format numbers #2
I have a list of audio books in an excel 2003 spreadsheet. One column should show the duration of the book in this format: 8 hrs 23 mins. But what shows in the column is a 5 digit number, such as 54463 as an example. I cannot figure out how to format this column to translate this number into the hrs and mins of the actual book. Could someone please tell me how to do this? Thanks for your help Joanne Joanne Where does this number come from? It does not seem to relate to 8 hours 23 minutes at all (eg it isn't the total minutes for example or seconds). What is the source of this informa...