#### 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).

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

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

1 Replies
552 Views

Similar Articles

[PageSpeed] 44

```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

"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

Similar Artilces:

Access Input Mask for phone number
What is a phone number input mask that would result in the first case 916.723.5828 and second case if 916 is not entered 723.5828 notice the first "." is left off. "Bruce" <Bruce@discussions.microsoft.com> wrote in message news:B4830038-5478-4859-B5AD-70C5311EDF1F@microsoft.com... > What is a phone number input mask that would result in the first case > 916.723.5828 and second case if 916 is not entered 723.5828 notice the > first > "." is left off. "Bruce" <Bruce@discussions.microsoft.com> wrote in message n...

converting column of text to text box for repetitive records
Hi, I have a spreadsheet which contains employee information including job skills. My problem is that each skill (cook,candlemaker, etc.)creates a new row for that employee, duplicating fields that don't change, name, ss#,etc with the only new info in the row being the skill Like this: Jane doe,123456789,555-555-5555,cook Jane doe,123456789,555-555-5555,candlemaker What I want is 1 row for each employee with the last column containing all the skills in a text box or separated by commas. Is there an easy way to do this in Excel? Or even Access? Thanks, Clark --- Outgoing mail is certifie...

Sales Return with Lot Number
Hi All, I have spent almost 4 days now tryingt o figure out whats wrong, search every document realated to Sales Return , SOP Integration...... but of no help.... I am trying to integrate the Returns in SOP Transaction Entry Screen using Integration Manager. we are running GP10 with SP1. Issue Example: Item No: TEST101 UofMSchedule: EACH UofMSchedule Master Table: EACH = 1 = EACH CASE = 10 = EACH PACK = 20 = EACH I am able to integrate all the items with lot as return Returned Qty = On Hand. if the item UOM = EACH (which is actullay Base UOM). But if the same item is returned in C...

Formula/Macro to tally up numbers in Excel
I have a list of numbers in a column, e.g. :- 1 4 6 1 35 14 15 15 2 1 6 6 1 19 15 5 and want the computer to work out for me, how many times number x has turned up in column B? I presume this is possible with a function/macro within excel fairly easily? Any advice/pointers in the right direction would be appreciated. Please prefably reply in the newsgroup, or via email to "newshelp@the-core.freeserve.co.uk.DELETE", removing the .DELETE from the end of my address. Thanks, Mark Pyne Hi Have a look at COUNTIF() and try something like: =COUNTIF(B:B,1) to count how many 1s there ...

List a ranking
I'm a newbie at this, so help is appreciated. I have an Excel sheet like this: Team A .600 =rank(b1,b1:b3) Team B .750 =rank(b2,b1:b3) Team C .300 =rank(b3,b1:b3) I want to get another cell to list how far they are from the #1 rank. So, cell d1 should be equal to .150 and d3 equal to .450. The percentages will change frequently, so it needs to figure out what is #1. I know I could just do =b2-b1 and b2-b3, but only because I can see the b2 is the highest percentage. Am I making sense? Is there a way to do this? TIA. BR =MAX(\$B\$1:\$B\$3)-B1 etc. -- HTH RP ...

Check Numbers ?
Money 2004 standard. My check register check number (currently 5138) is = soon to overtake the check number assigned to Epay (next is 5212) (it = started at 5000). Will this be a problem when the two meet ? Can I reset the Epay number to 1000 and avoid ? How to do it.... Tnx for any thoughts.... chet clech wrote: > Money 2004 standard. My check register check number (currently 5138) > is soon to overtake the check number assigned to Epay (next is 5212) > (it started at 5000). Will this be a problem when the two meet ? > Can I reset the Epay number to 1000 and avoid ? How to do i...

I need to relink to a text file on a share drive, but I want to link according to the data path value instead of the share drive letter. Ex: //cdf888/example Instead of c:/example Any idea on how I can do this. Thanks Using network neighborhood, you should be able to use UNC (Universal Naming Convention) to map to a network share. \\ComputerName\ShareName\OptionalFolderName\FileName.mdb Access will recognize this and continue to open the file. -- Arvin Meyer, MCP, MVP http://www.datastrat.com http://www.mvps.org/access http://www.accessmvp.com "Database Girl" <DatabaseG...

Adding the highest numbers in a column
Hello: I have several columns of 8 numbers in a worksheet and I want the sum of the largest 6 numbers in each column. What formula do I use to make this calculation? Thanks >-----Original Message----- >Hello: > >I have several columns of 8 numbers in a worksheet and I >want the sum of the largest 6 numbers in each column. What >formula do I use to make this calculation? > >Thanks >. >I have figured out what I need to do. What if there is a tie? Assume there are these numbers 2 2 2 3 4 5 6 7 if you want all numbers included =SUMIF(A1:A8,">="...

sorting columns with more than 2 text entries
Data>Text to Columns>Next works well if all entries have a "single" first and last name, eg, "Joe Smith". However, if there is a "Mary Joe Smith" listed, how do I put only the last text entry into a new column? I am downloading payment information from a PayPal account and want to sort by last name for our lunches. Unfortunatley the "name" information (first, last, etc...) is contained in a single cell. -- JAB Hi JAB! Use this formula to extract the 1st name in cell A1: =LEFT(A1,FIND(" ",A1,1)) Use this formula to extract the l...

Sections of Text Being Marked as Figures
When I add and position images in a Word 2007 document, it will (apparently randomly) decide that some sections of text are figures. They can be edited as usual, but this messes up the automatic figure numbering in image captions. If the document has an automatically updating field for the figures, it will insert the whole body of text that it thinks is a figure into the table (this is sometimes several paragraphs). Deleting and re-typing the text does not seem to help (this can also be very time-consuming to re-type several sections that add up to a few pages in larger docu...

Text vs. Number
Why is is that if I have a tab-deliminated file or csv file that I open in excel the following happens: If the field has twelve digits (a UPC code) -- say 068888701648 -- that when it gets into the Excel spreadsheet is drops the front zero and calls it a number. I don't want that to happen. Also when I export to a csv file I want the field to be a text field -- "068888701648" -- as opposed to a number. This is the reason. What I've been doing up to this point is creating a custom format -- 000000000000 -- twelve digits. Most UPC codes have a zero at...

Varible
Dear all, I need to create a variable to return the number of the column that the active cell is selected, for example: if the cell selected is in column B, the number returned by variable is two; if the cell selected is in column E, the number returned by variable is 5. Thanks a lot!!! Andr=E9. msgbox activecell.column -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com <gatarossi@ig.com.br> wrote in message news:1186930049.763360.96940@e9g2000prf.googlegroups.com... Dear all, I need to create a variable to return the number of the column that the act...

Text Box in a Report
This is my control source for a text box: =([Company]) & Chr(13) & Chr(10) & ([BizShipAddr]) & Chr(13) & Chr(10) & Trim([BizshipCity]) & ", " & ([Bizshipstate]) & " " & ([BizshipZip]) Programmically is it possible, if there is NO company name, not to put a blank line there? I have another text box above it with Attn: ContactName and if there is no company name, there is a blank line between that & the street address. I kept them in seperate boxes as Attn line is bold red. Thanks Curtis On Tue, 1 May 2007 12:47:15 -0700,...

Chart Text Box Data Table
For the boss, I would like to display a series of figures (as text) that are derived from the data underlying a chart. For example, the chart shows electricity demand in kW over the course of the day. In the upper corner of the chart, I want to display the total usage in kWhr for the whole day, its cost, and the percentage difference from our budget. I know how to do this with just one value: create a text box, then use the formula bar to enter something like "=Sheet1!\$C\$4". But to do that with all the information I want to display would require about a dozen text boxes, a...

counting the number of dates
I was using the tip counting the number of times dates in certain month appears. I also needed it to count the number of "E"'s for that month. the array that I used was: =sum((month(a2:a11)=b2)*(year(a2:a11)=c2*1) column d would have some blanks and some E'S. Would it be better off to use if,and,or statements than the array or would I just and the if statements at the end of the array? --- Message posted from http://www.ExcelForum.com/ =SUM((MONTH(A2:A11)=B2)*(YEAR(A2:A11)=C2)*ISNUMBER(A2:A11)) to be confirmed with control+shift+enter instead of just with enter. =SU...

Concantenate columns with dates and text
The first two columns in my spreadsheet are dates. The third is text. When I try to concantenate, the dates turn into numbers. Any advice would be greatly appreciated. Thank you. In Excel, dates are stored as the number of days since the last day of the nineteenth century (i.e. 1/1/1901 is day 1). Times are recorded as a fraction of a day. What you see when you type a date into Excel is that underlying number subjected to a date format. To work around this, you need to use the Text() function. This will change your date value to a text date with your chosen format. For example to se...

Setting week number in Microsoft CRM
Hi! I am operating with different week numbers in my CRM. - CRM-appointment is at the moment showing week no. 25 - Outlook calender i showing week no. 25 - but on the startside there is showing week no. 26. How can i change week no. 26 to 25 ? Best Regard Claus Hi! You can change the start day of the week. Try it, and it will also change the week number. Sanne Hi Sanne! Thank you, do you now if there is a special way to update the week. no. for the startside from Outlook? Best Regard Claus "Sanne" <Groenhof@gmail.com> skrev i en meddelelse news:1119354490.286033.5...

text rotation
I develop a fingerprint application on pocket pc and I need to display message during the capture process. So for now I use an edit box but I would like to make a rotation of 180 � to make the message readable by the people whom I capture the fingerprint. Should I just make a rotation of the graphic display, I mean just of the edit box area or is there any other mean ? Didn't you post this a week ago!!! Here are the answers from the last post you had You won't be able to do this with an edit control, but you can do it with a static control. Dervie a class from a CStatic class, ta...

How do I automatically change text
I would like to automatically change the text in a series of cells. For example the value currently shown in the cell is " 00/05-05-097-5W5/00 ". How would I remove the 00/ & /00, so the cell would show " 05-05-097-5W5 "? I hope this is not too confusing. Thanks Monty Search and replace? Search for 00/ and replace with <space>, ditto for /00 On 15 Mar 2005 21:39:09 -0800, mleeds@hitechfluid.com (Monty) wrote: >I would like to automatically change the text in a series of cells. >For example the value currently shown in the cell is " >00/05-0...

convert text to formula using VLookup
My question; I've seen somewhere that you can convert a text string into a formula using the INDIRECT Function. What I am constructing is a maintenance Schedule where some items need to be serviced on a weekly basis, others monthly, quarterly, bi-annually, and yearly. (Those are the only five rotations I require at the moment.) Once constructed the format will take on the appearance of a calendar with conditional formatting to highlight the dates the service falls on. Each of the rotations require (I think) different formulas, so what I want to do is have a list of the f...

publisher 2003, email, background ruins text...
Hi I create a publication, file, send email, this page as message and the text is very poor, seems to convert it into a picture. Is it the font (using arial)? would a better font work or is this what is expected ? TIA ...

Error number: 0x80070005
I have this error and I can not fix.. Please help Hello Kristi, Please always state what OS you're & service pack and what version of Internet Explorer? as it often does make a difference - Also, could you supply some information on what your firewall is, anti-virus, other R-TP programs, e.g. Spybot Search & Destroy (reduced functionality called "TeaTimer") Avira (From version 9) (Included in antivirus package), Ad-Aware, etc? http://support.microsoft.com/kb/555375 -=- Which update(s) ? -=- July 15, 2009 Error message "0x80070005 Access ...

Rank within Categories
I am trying to work out a way to rank within categories. Column E has different categories. Column i has the data. Column am working on i want to give the ranking where each data point falls within its own category. i have tried to hash something out but am stuck...here is my feeble attempt =IF(\$E5="SE",IF(\$E\$5:\$E\$40="SE",IF(I5>0,RANK(I5,\$I\$5:\$I\$40,0),"N/ A"), ...... ..... signfies a string of IFs. the Next one being IF(\$E5="C".... I have also tried an array.....that doesnt seem to work =IF(OR(E6={"C","SE","NE","...

Text Boxes #2
Hello, Is it possible to rotate a text box at 45 degrees? Thanks. Bill Bill, The rotate tool was taken away from us; most shapes now have a rotate handle. Except the lowly text box. You can add text to a rectangle instead, and rotate it with its rotate handle. But the text won't rotate. You can make a text box in PowerPoint, rotate it, and the text will rotate. But if you paste that into Excel, it ain't rotated any more. ): A couple of distant relative solutions: Use a WordArt (Drawing toolbar) object. Rotatable. Or put your stuff into a cell, and rotate it with Format -...

pick a number from 1 to 10
Hi All, I would like to have a web page where Internet users can go pick a number from 1 to 10. However, once a user picks a number that number should not be used again therefore that number belongs to that user. How do I manage if, let's say, 2 users happen to pick the same number at the very same time how do I prevent that from happening? Is that a concurrency issue? Thanks, rodchar "rodchar" <rodchar@discussions.microsoft.com> wrote in message news:070ED8B9-198A-40FA-BC2E-E0FAFDD1CD05@microsoft.com... > Hi All, > > I would like to have a...