Vehicle Identification Number Function
I need a function to return a boolean T/F to check if a 17 character string meets the following checksum validation.
This is for Vehicle Identification Numbers for vehicle made after 1981
I need a VBA function that I can call multiple times in a workbook,
Example =VIN(A1) which returns True or False.
First, find the numerical value associated with each letter in the VIN. (I, O and Q are not allowed.) Digits use their own values.
A>1B>2C>3D>4E>5F>6G>7H>8
J>1K>2L>3M>4N>5P>7R>9
S>2T>3U>4V>5W>6X>7Y>8Z>9
Second, look up the weight factor for each position in the VIN except the 9th (the position of the check digit).
1st>82nd>73rd>64th>55th>46th>3
7th>28th>1010th>911th>812th>713th>6
14th>515th>416th>317th>2
Third, multiply the numbers and the numerical values of the letters by their assigned weight factor, and sum the resulting products. Divide the sum of the products by 11. The remainder is the calculated check digit. If the remainder is 10, the calculated check digit is the letter X.
Finally, if the calculated check digit did match the 9th digit of the VIN entered by a user, the VIN passed the checksum test. It failed the checksum test otherwise.
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Vlookup: Function That Will Search For The Value Of The Vehicle
I have a lookup table (see attached file) which holds the min & max value for a vehicle, the min & max size of the engine and the premium. I want a function that will search for the value of the vehicle (e.g. 100000 in cell H5) within the min value and max value columns as well as the engine size (e.g. 2000 in cell H6) in the min size & max size columns return the premium from the associated premium (which should be 13,200).
View Replies!
View Related
Vehicle Age
Basically, i want to work out the age of a large list of vehicles based on its uk registration plate number but have so far been completely out of luck,
View Replies!
View Related
One To One, And One To Many Identification
I have a spreadsheet that loads a huge amount of data (50,000 rows) that needs to be parsed as follows: Below are the Broker Names listed in Column A: Column A: Broker Name 1 = Tom Broker Name 2 = Dick Broker Name 3 = Harry Broker Name 4 = Frank Below are the Broker Codes listed on another section of the same worksheet: Column R (for example): Broker Code for Broker Name 1 (Tom) = BT Broker Code for Broker Name 2 (Dick) = BD Broker Code for Broker Name 3 (Harry) = BH Broker Code for Broker Name 4 (Frank) = BT I would like to have the Broker codes (e.g. BT, BD, BH or BT) put in Column C next to the corresponding Broker names. The part that I am having difficulty with is that it is a mixed situation whereby only one Broker Code (e.g. BD and BH) applies to one specific Broker Name, while other Broker Codes (e.g. BT) may apply to multiple Broker Names. Can a formula be developed that could be copied and pasted in Column C that would compare the list of codes against the list of names and return the appropriate Broker Code?
View Replies!
View Related
Fill Vehicle Allocation Table
I am reposting this as my previous post was deleted.. i dont know the real reason for the same... The raw data in the attachment contains: •C1 to AI1 refers to vehicle numbers. •Column A refers to Shift Time •Column B refers to Number of vehicles required in that particulars Shift time The output: •As you can see in the Sheet “Output Required”, fields marked in the color “light green” are manually allocated vehicles numbers available and required according to shift time •The logic used behind allocation is the same vehicle should not be used before or after within the span of 3 hrs, the gap between the vehicle usage before and after should be minimum 3 hrs. And the maximum time gap can be any value. •Vehicle usage can be maximum (ie. You can use the same vehicle by rotating within 24 hrs but keeping in mind that it is not used before 3 hrs). • all the available vehicles must be used before the same used vehicle is used in the new shift... ie. You should utilize all the vehicles before one is duplicated
View Replies!
View Related
Languages Identification Aid
One Excel file with two columns that is populated with a description code and a description. The total number of lines is 30 000 and the description is on the Languages of English, Spanish and Portuguese. I need to indicate in the third column all the descriptions that are not English.
View Replies!
View Related
Identification Of Special Numbers
I got some 10 digit numbers in column A, need to get the marking in column B as per the criteria given below for last 5 digit numbers. The checking should start with platinum. on getting a macro or any formulas for each criteria. Category For Last 5 Numbers MARKING Samples Numbers Ending with Double Digit SILVER 9280015622 Ending with 786 (Without 0) SILVER 9280025786 First 2 Digits0 & Last 3 From 101998 SILVER 9280000101998 Double Digit Repetition with 0 Separator GOLD 9280055055 Numbers in Sequence GOLD 9280012345 Numbers Ending with Triplets GOLD 9280052333 Numbers in Round Hundreds GOLD 9280082500 Numbers in Round Thousands GOLD 9280065000 Ending with Triplet GOLD 9280052333 Mirror Image Numbers GOLD 9280012321 Triplets with 0 as Prefix or Suffix GOLD 9280010444, 9280014440 Numbers having 786 (Others 0) XCLUSIVE 9280000786, 9280100786 Single digit Ending (All Others 0) XCLUSIVE 92800000019 Triplet followed by Double Zeros XCLUSIVE 9280055500 2 Consecutive Triplets XCLUSIVE 9280111555 Ending Quadruplet XCLUSIVE 9280056666 Last 4 digit 0 PLATINUM 9280010000 All Penta Numbers Including 0 PLATINUM 9280011111
View Replies!
View Related
Add Ticks In Cells For Copy Identification
I have this excel sheet with 200+ (number can change depend of situation) checkboxes here and I would like for the worksheet to do the following: 1) when i click on the checkbox, it copies all the data in the same row as the checkbox to a seperate sheet (sheet2) and not leaving any spaces if checkboxes are not click in order. Here comes the addition... 2) when i uncheck the checkboxes, the row that associated with the checkbox will be removed. 3) the checkboxes will all be uncheck with a commandbutton (reset switch) 4) variable number of checkboxes so that i dont have to link the subroutine to every checkbox with the move and remove subroutine. (because there will be unknow number of checkboxes and mostly 200+) example (want to be able to replace all the 1, 2, 3...with an X or any way possible) Private Sub CheckBox1_Click() MoveCheckBoxData CheckBox1 End Sub Private Sub CheckBox2_Click() MoveCheckBoxData CheckBox2 End Sub .........................
View Replies!
View Related
VBA Function  Extract Number(s) In String
My knowledge of functions is pretty limited, or negligible to be honest. I require a function to extract numbers from each cell in a selected range. Eg. cells contain the following data: cell 1: xxxxxx 45,59 cell 2: x xxxxx xx 6,45,27 cell 3: x xxxx 28 were x represents text I need to loop through each cell and extract each of the numbers and list them in a separate range. If applied to the above range of cells the function would show the below in the ouput range. Each number in its own cell. 6 27 28 45 45 59
View Replies!
View Related
Get The Number Of Workdays Between Two Dates In A VBA Function
Get the number of workdays between two dates in a VBA function. I tried simply using the Advanced Toolpak function NETWORKDAYS, but I get the error message "Sub or function not defined". I did reference to atpvbaen.xls in the Tools > References menu, so I don't know why Excel is not recognizing the function. I used the following function to test it: Function CalcNetDays(dInitial As Date, dEnd As Date) CalcNetDays = NetworkDays(dInitial, dEnd) End Function Any reason the function is not working? Also, I need to add the holidays to the function. The holidays are listed on a worksheet column. Do I simply add the range to the function, like below? CalcNetDays = NetworkDays(dInitial, dEnd, Range("Holidays"))
View Replies!
View Related
Changing Colour Of Number Using IF Function
Is it possible in anyway to make a formula that changes the colour and boldness of a number depending on whether it is bigger than 50 or 100? eg. if i enter all my numbers as dark grey. then i enter say, 65, i want it to automatically change to black and bold. if i enter 105, i want it to automatically change to red and bold without me having to do it manually?
View Replies!
View Related
Using VLOOKUP Return As Row Number In Function
I am wondering about the best syntax for using a VLOOKUP return as the row number in a CORREL function. I want to create rolling correlations from today's date. I have a VLOOKUP function that will return the row number corresponding to the chosen day's date. I now need to use that returned value in the CORREL function. That is, I would like it to look something like: =CORREL($E$VLOOKUP(today90,AD5:AE3143, 2):$E$VLOOKUP(today,AD5:AE3143, 2),$E$VLOOKUP(today90,AD5:AE3143, 2):$E$VLOOKUP(today,AD5:AE3143, 2)) When I enter this, I am told that I have an error. Is there a better way to nest this vlookup?
View Replies!
View Related
Hard Coded Number Range Into A Function
All I need is to use the forecast function =FORECAST(I902,F832:F901,I832:I901) but want to substitute a hard coded from and to value list, such as something like =forecast(71,F832:f901,{170}) That is, forecast case 71 given a range of 1 through 70. What is the correct syntax to give a from and to value? Curly brackets don't seem to work, and I don't want to do a list like {1,2,3,4,5,6,7...} which would be a pain.
View Replies!
View Related
Using 'left' Function To Take Out Number Upto A Certain Character
Pretty sure this has been asked but have searched the forum to no avail, but I need to extract the numbers from a value which has a letter on the end. eg. 1000x I need to get out the 1000 or 2p I need the 2 I have sooooo many values ranging in numbers of digits, so just basic left(A1,2) won't work, I'm sure I've seen a search or find function but don't know how to use them!
View Replies!
View Related
Function That Inputs A Certain Number Of A Specific Character
I need to make a list of Part Numbers in quotation marks. If a number of digits of a P/N is less than 13 a number of space characters has to be added to make the string 13 characters long. We have example P/N: 1234567890123 12345678 123456 should become: "1234567890123(no extra space characters should be filled13chars)" "12345678(5 extra space characters here)" "123456(7 extra space characters)" Is there a function that inputs a cerain number of a specific character ("space" in this example)?
View Replies!
View Related
Find Column Number And Then Use Index Function
I have a database with over 100 products listed across the first row. Column a has a list of over 500 projects. Across each project various columns are marked with a number depending on how many of each products are being used on that project. For Example A B C D E etc. Products > X Y Z AA Proj 1 2 3 Proj 2 1 4 5 Proj 3 2 4 etc. I want to be able to create a report for any given product. The report could look like, Product Z Proj 1 3 Proj 3 2 So I need to lookup the product code across row 1 and determine the column number and then INDEX down that column and find all non blank cells and read the project names from column A. I am familiar with formulas with INDEX and V/H LOOKUP functions. I am not very good with VBA codes.
View Replies!
View Related
Force LEFT Function To Recognize A Number?
Basically I have a phone number in column A: 1234561234 In column B, I want it to show me the first 3 numbers from the left, (so 123) So I do =LEFT(A2,3) Which gives me 123, but it's displayed as text, which ruins my whole formula that looks up the area code and displays the state. I googled the problem and found http://exceltip.com/st/Make_LEFT_Fun...umber/778.html which tells me to do: =IF(LEFT(A1,1)=1,"Ignore",A1) [sees 1 as a number] =IF(LEFT(A1,1)+0=1,"Ignore",A1) [sees 1 as a number] =IF(LEFT(A1,1)="1","Ignore",A1) [sees 1 as text] but when i try that it just displays the ENTIRE phone number: 1234561234
View Replies!
View Related
Mid Function :: Match Account Number With Description
I am trying to use a Mid function to help me in my vba problem. In my first table I have a list of account numbers. E.g. 'ZZ500543' In my second table I have a list of account descriptions. E.g. 'Denis Morgan ZZ500543 leak from bath' I need to match the account number in Table1 with the correct account description in Table2. I thought a Mid function would be the best option to pick out the account number in Table2? Is this right? How would I go about using it?
View Replies!
View Related
Match Function For Cell Reference Row Number
I am trying to acccess a cell value from a seperate worksheet. The cell reference needs to be generated by a seperate formula. In this example =DSInfo!C28 // provides the correct result i.e the contents of cell C28 on worsheet DSInfo =MATCH(A4,DSInfo!C1:C35,0) //provides the correct row number  in this case 28 However on trying to combine the two =DSInfo!C&MATCH(A4,DSInfo!C1:C35,0) // provides only a formula error. I'm guessing this is a simple syntax error on my part but after hours of trying various ways I'm still having no luck.
View Replies!
View Related
Force Text Function To Return Number
I am using a MID fn to extract a single digit from a numeric string. I then use an IF Function to return a value based on the extracted digit. My IF statement works fine on manually entered digits but doesn't recognize my MID fn result. What am I missing?
View Replies!
View Related
Calculate The Number Of Days Between Two Dates Using The Days360 Function
I was calculating the number of days between two dates using the Days360 function. However, now it is not accurate enough for me... Is there a function that is more accurate? based on 365 days and includes leap years... that I could use as I need this calculation to be exact? For example, number of days between April 1st, 2010 and August 31, 2012 is 884, but the days360 function returns 870.
View Replies!
View Related
Lottery Number Generation: Create A Single Line Function
You must install "Analysis TookPakVBA" before doing this; check in your Excel's Tools/AddIns menu. Paste this long function (see below) in an Excel's worksheet (any cell) and press F9 (calculate) until you see at least 2 identical numbers. Real life's lottery does not have repeating numbers. I can disable this repetition by using cells relation but I want to use just one (1) cell. Any expert wants to challenge? If not, you can use VBA. But it still requires one (1) line of codes. =RANDBETWEEN(1,50)&" "&RANDBETWEEN(1,50)&" "&RANDBETWEEN(1,50)&" "&RANDBETWEEN(1,50)&" "&RANDBETWEEN(1,50)&" "&RANDBETWEEN(1,50)
View Replies!
View Related
Index Function (isolate One Number At A Time And Evaluate Usage)
The value that is returned is off by 2 rows everytime. When I evaluate the formula, it shows the correct row just before the indexing function does it's thing. I have a cell phone bill for 20 or so phones and am trying to isolate one number at a time and evaluate usage. The first sheet is my data, the second is sheet ("Breakdown") is where I enter the number in A2 that I want to look at. When I do, it misses the first 2 rows and picks up 2 extra from the following phone number.
View Replies!
View Related
Counts The Number Of Hours That This Person Works & Lookup Function
I'm trying to create a work Rota and I'm having a bit of a problem with a certain section. In Worksheet 1 I have the following headings: Cell A  Name Cell B  Monday Cell C  Tuesday Cell D  Wednesday etc Under these headings is each member of staff and the hours they work, IE L (Late), E (Early), SD (Short Day) etc. In the final Column, it counts the number of hours that this person works (Early is 7.5). In Rows 46, there is a section here to work out how many people are working earlys, which is where I have the problem. The hours are worked out by doing a lookup function on the cell that says E, L etc and goes to a CODES sheet and pulls the value of that letter. What I am wanting to do is lookup that Letter, which and look in the cell next to it and count how many people would be on an early etc.
View Replies!
View Related
Create A User Defined Function To Search A Column Of Data For A Part Number
I want to create a user defined function to search a column of data for a part number. If it exists I want to have a the UDf returna "fail" otherwise "pass" Here is the code I was trying to use Function firstpass(SN As String) As String ws = Worksheets("Defects") c = "" With ws.Range("a1:a9999") Set c = .Find(SN, LookIn:=xlValues, lookat:=xlWhole) End With If Not c Is Nothing Then firstpass = "Pass" Else firstpass = "Fail" End If End Function This function only returns a "#value" and I don't quite know how to troubleshoot it.
View Replies!
View Related
Function To Mirroring Cell Relative To Function's Location
I have created a List in excel of various tasks. (See attached example.) Each row contains one cell with a function which equals the cell above it and to the right. So the function for cell C3 would be D2. The only importance is that the cell mirrored is always the cell one above and to the right. However, now I would like to be able to rearrange this List, but the “one up and to the right” function will now be all over the place. Is someone aware of a function which will target a relative location instead of an exact cell?
View Replies!
View Related
Force Function To Use Row Relative To Cell Housing Function
I have been looking around have this much code from this site, modified. What I am trying to do is calculate numbers by a position with cells D through K having numeric values. I have 'hardcoded' the cells (D2, E2, etc in the code below), but in reality I only want the current row (so if the formula is on the 2nd row, I want D2, if it is on the 3rd row, I want D3). My problem is obviously the formula isn't working because I am not correctly tying back to the spreadsheet (Positioncalc.xls). When I put the formula in the spreadsheet it works, but in my script I get 0 everytime. My Script: Function Position(rCell As Range, Optional RightPosition As Boolean) Dim vResult Select Case rCell.Text Case "QB" vResult = (2*D2) + (2*E2) + (2*F2) + (4*G2) + (2*H2) + (1*I2) + (4*J2) + (3*K2) Case Else vResult = "Invalid Position" End Select If RightPosition = True Then Position = vResult Else Position = "Position not valid" End If End Function So, when I put =Position(A2,True) I expect to see the formula results of those cells calculated based on the position (QB, HB, etc with their unique formulas). My next challenge after this is to highlight certain cells based on the Position. So if A2 = QB, I want cell D2 boldface and Red, etc. I have seen some scripts on colors and such here, so I might be able to figure it out.
View Replies!
View Related
2007 Right Function With Embeded Find Function
I have a range of cells, for this example I will use 2. Cell E17 = 77/170 Cell E18 = 8/9 Using the following formula: =SUM(RIGHT(E17,FIND("/",E17)))+SUM(RIGHT(E18,FIND("/",E18))) This bring back an #VALUE! Error as the second part of the formula keeps picking up "/9" however the first part works fine, displaying "170" Now if I use: =SUM(RIGHT(E17,FIND("/",E17)))+SUM(RIGHT(E18,FIND("/",E18)1)) It all works. The problem is that I need this to be automatic using the above way means having to add a "1" to every formula for a cell with only 1 char to be added. Using the formula: =SUM(RIGHT(E17,FIND("/",E17)1))+SUM(RIGHT(E18,FIND("/",E18)1)).....
View Replies!
View Related
Concatif Function Breaks Beforedoubleclick Function Just By Being There?
I have a tracking sheet (attached) that has many functions, but I'm having trouble with two of them. First of all, I have a Worksheet_Change event set that when a cell is cleared, it fills the cell with a formula to use the record above it as a default value if another cell is equivalent. This worked when I first wrote it, but now it seems to break every other time I use it. When assigning the formula it returns a Method Default/FormulaR1C1/Offset failed error message. It seems to be different for each one, each time and I can't figure out what the problem is. The other problem I'm having is that I wrote a BeforeDoubleClick event to expand or contract any given record, or series of records. this too worked when I initially wrote it, but now is only hiding one row when it should be hiding eight or more. I'm not terribly versed in VBA and totally baffled as to why I'm having these problems. As I side note, any ideas to clean up any of the code and make this run smoother (as ther will be many more series added when complete).
View Replies!
View Related
AVERAGE Function Embedded Within An IF Function
TPR, DISPLAY and FEATURE columns generate a rating based off of an IF function. In the Executed column, I need TPR, FEATURE, DISPLAY to be averaged together...BUT....In I want the average only include columns where there are numbers. For example in row one the eqn would be (1+3+2)/3, but in row 2 the eqn would be (1+1)/2...can I state an average function within an if function? Or what would be the best way to create an eqn for this?? I have thousands of rows to complete and doing it manually is not an option. 0 Did not meet expectations 1 Below expectations 2 Met expectations 3 Exceeded expectationsTPRDISPLAYFEATUREExecuted?Effective?Comments132Coming off of a Dec promotion113111111221
View Replies!
View Related
