Vehicle Identification Number Function
Oct 17, 2009
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 9 Replies
ADVERTISEMENT
Nov 26, 2008
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 2 Replies
View Related
Jun 11, 2014
I'm looking for a function which will check if the cell value is a valid UK registration number plate format.
True if it is, False if it isn't.
I've tried myself and had a look around but am unable to find a solution to my problem.
If also possible, if false, to make to necessary amendments to make it a valid format...ie If some lower case values, make them upper case, if there is a space entered, remove it, if a O is entered instead of a 0, correct it and show the corrected value when the function is run. For all that it can't do that with, then leave as False.
I understand the second part may not be possible, just identifying whether or not the value is a valid format. I have something similar which does this for UK postcodes, but am hitting a brick wall trying to do the same for UK registration number plates.
View 9 Replies
View Related
May 8, 2009
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 9 Replies
View Related
Nov 16, 2006
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 6 Replies
View Related
Dec 23, 2009
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 Digits-0 & Last 3- From 101-998 SILVER 9280000101-998
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 9280000001-9
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 9 Replies
View Related
Sep 14, 2007
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 9 Replies
View Related
Oct 15, 2007
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 13 Replies
View Related
Sep 30, 2013
My vlookups gives mostly #N/A in my full column, one or twice match with other sheet, so difficult to identify which one match in my lookup, i should watch carefully every time it will be matching or not, IS there any options when match it with formula then it appear highlight or color?
Or any options vlookup matching with original color and format?
See excel first sheet and second sheet : vlookup with identification.xlsx‎
View 6 Replies
View Related
Feb 26, 2014
Mega master.xlsx
On the attached excel sheet I have a 5 week period, and two overall pages (one for the drivers, one for the trucks). My question is this. Drivers may drive different vehicles throughout the week, how, on the Truck Overall page do I calculate the formula so it deciphers each truck and puts the correct information into the right boxes?
View 5 Replies
View Related
Jan 15, 2013
I have a problem regarding making a report in excel.
My problem is :
I have a excel file where columns are
Collapse | Copy Code
S.no. Date. From To Mode_of_Transport Depart. Arrival NameRemarks
So my job is to find the persons who arrived at interval of 30 min so that vehicle can be arranged for them.
View 5 Replies
View Related
Nov 26, 2006
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 9 Replies
View Related
Jul 9, 2014
Whenever or not I should use VBA or ordinary formulas in Excel in order to do Vehicle Routing With Two-Dimensional Loading Constraints case. What I need is when I have coordinates of four bottom-right corners:
X Y
24 0
30 0
10 24
8 38
2L-CVRP, what I want to know is: In reality I have set of 200 coordinates (x,y) and I need to search for lowest y (its (24,0) and (30,0)) then I choose one with maximum x ((30,0) in this case). Then if for some reason item cannot be packed there, then I shall go to next lowest y, which is 24 giving coordinate (10,24), if I cannot use this corner, I search for next lowest y with as high x as possible, etc. BTW I use Evolver in order to find satisfying solution.
View 3 Replies
View Related
Jun 3, 2014
Is this is just an example, but the logic should hold true. Data for example is in A1:C5. I want to use a SUMIFS statement that will look at type of Vehicle and Dates. So for the example in cell D1 (I wanted to add only Cars sold between May 1-2), but my current equation = 0, where I am expecting 20
=SUMIFS(C1:C5,B1:B5,B3:B4,A1:A5,A1)
View 5 Replies
View Related
Jul 9, 2014
I am trying to workout a formula to look-up and categorize data from a bank statement, without having to complete manually monthly.
So far I have a formula that returns the amount spent, but would need an extra column and separate table for each category.
IF(OR(ISNUMBER(SEARCH(" "&$G$3:$G$11&" "," "&A2&" "))),B2,0) where
Instead of returning B2, I would like to return a value from the table in column H3:H11 that corresponds with G2:G11, as shown below.
ColumnG ColumnH
Aldi Food
woolworths Food
Coles Food
saint Nicholas School
Blooms Medicines, toiletries, hairdressing, personal items
vodafone telephone
So for example if Aldi is in the text in A2, then I would like to return food ( from a lookup table similar to above) into the column where my formulae is ( say F2).
View 9 Replies
View Related
Apr 11, 2014
How do I use a function that gives me a number with the thousands separator and no decimals?
View 1 Replies
View Related
Sep 6, 2005
=1/25%
is it that you want a UDF
Function MyFun(a,b)
MyFun = a / b
End Function
Usage:
=MyFun(1, 25%)
Mangesh
"Gonecoastal1" <Gonecoastal1@discussions.microsoft.com> wrote in message
news:B257DADD-0042-4CF8-88DF-1D17AEAFF627@microsoft.com...
View 14 Replies
View Related
Jan 24, 2006
I have a 2500+ line document with different years indicated in column D. How
would I use the count (??) function to count the number of occurances for
1998, 1999, 2000, etc?
View 10 Replies
View Related
Feb 5, 2010
I need a formulat that will ensure that a cell has 12 numeric characters,a dash, and then 3 more numeric characters. I thought this was a simple formula but I cannot get anything to work.
############-###
View 9 Replies
View Related
Mar 21, 2009
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 4 Replies
View Related
Mar 16, 2009
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(today-90,AD5:AE3143, 2):$E$VLOOKUP(today,AD5:AE3143, 2),$E$VLOOKUP(today-90,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 3 Replies
View Related
Jan 6, 2013
I've written this code to find a value in the worksheet chosen from the combo box (I think it works as its doesnt error), but I now need too write some code to pull out the row number from my code and put data into the same row (but different columns). This is my code so far:
Code:
Private Sub BookButton_Click()
Dim rFound As Range[code].....
View 2 Replies
View Related
Jul 31, 2013
How can I get the largest number in this list by using a function?
View 4 Replies
View Related
May 27, 2014
I need to have a spell number function such that if I have a collumns with a figure and abbreviated currency as below
USD 953,681.67
EUR 953,681.67
GBP 953,681.67
The function SpellNumber would display as
US Dollars Nine Hundred and Fifty Three Thousand Six Hundred and Eighty One and Sixty Seven Cents Only
Euros Nine Hundred and Fifty Three Thousand Six Hundred and Eighty One and Sixty Seven Cents Only
Great British Pounds Nine Hundred and Fifty Three Thousand Six Hundred and Eighty One and Sixty Seven Cents Only
Any function that I can append as Macro in Excel and output depends on Currency.I should also be able to edit Macro to add additional currencies as I wish.
View 4 Replies
View Related
Oct 18, 2008
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 9 Replies
View Related
Dec 3, 2009
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 9 Replies
View Related
Jun 23, 2014
I have the following data; B1 and B2 are entered by the user and are named 'heat' and 'race' respectively. Conditional Array Formula Example.xlsx I'm after the ROW number that matches the minimum time (col C) for the heat and race combination shown - in the example below (heat: A, race: 100m), I'd like row '5' returned (or, better still, row '2' of the array A4:D11), as that's the lowest time for Heat A, 100m.
A
B
C
D
[Code].....
but this dosesn't exactly seem elegant, and doesn't work!
ie I simply want the row number (ideally relative to the range) for the lowest time for any given inputs 'Race' and 'Heat'.
View 3 Replies
View Related
Dec 11, 2013
Suppose I have a column filled with numbers in fraction. And correspondingly I have a specific name for every 10 numbers. I want to find out which is the maximum among the 10. For this I could use the "MAX" function in excel. Similarly I have to find the maximum among the next 10 numbers. Since I have about 200 such numbers under 20 different names and eight such sheets (10 numbers per name), it becomes difficult to type the formula and select the range everytime. I would like to know if there is any shortcut for getting this highest number among the 10 numbers.
Eg.
Name Value Number
TU 489.662.272
TU 380.450.0379
TU 331.970.0475
TU 309.641.004
TU 300.890.1215
TU 288.840.0501
TU 280.090.022
TU 278.580.0115
TU 270.160.0397
[code]....
View 6 Replies
View Related
Feb 5, 2014
I am working on an excel master marksheet which will be distributed to many teachers to fill in their students marks. How can I enter an average function that can work out the mean for those different classes. For example I'd like the main average function to be like this =average(d5:d50) but if a teacher has thirty students in his list the mean will be only limited to the marks of those thirty students. Same thing I want for the mode function and the median function.
View 7 Replies
View Related
Feb 5, 2014
I have been trying to get the row number of a cell to be decided by the integer result of a function. My thought was to do something like this .........(D(MATCHxxxxxx))....... With "D" being the column and "MATCHxxxxx" representing some function that results in an integer.
View 3 Replies
View Related