# 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
Jun 23, 2014

As you can see in the attached excel doc, I have calculated a column for "Number of Claims Per week" and also "Dollar amount Per claim". This was done using VLOOKUP and distributions. That is not the issue.

The issue I am having is creating the "TOTAL" for the week. So for example, if week one generates 4 claims, I need to be able to add up claim amount #1-#4.

It seems as though this would be a basic function, but I can seem to find a way to add up a certain number of cells, based on a variable generated from a random number.

If you take a look a my excel sheet, I am trying to solve for the "Claim Total" column on the far right. This column should be the sum of (Number of claims * The individual amount per claim). Note that if there is 4 claims in that week, I would have to add up claim 1-4 to get that weeks total.

Question2.xlsxâ€Ž

View 9 Replies
View Related
Apr 10, 2009

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,{1-70})

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 9 Replies
View Related
Jul 29, 2009

Basically I have a phone number in column A:

123-456-1234

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: 123-456-1234

View 3 Replies
View Related
Aug 11, 2009

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 8 Replies
View Related
May 28, 2008

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 14 Replies
View Related
Apr 20, 2009

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 14 Replies
View Related
Sep 17, 2008

This might be really simple but i don't get it. I have a column with country names (strings). There would be 5 instances of "USA", 10 of "UK", etc, etc.

I made a column next to it, where i want to count the number occurances ....

View 9 Replies
View Related
Mar 23, 2009

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 filled-13chars)"

"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 2 Replies
View Related
Dec 3, 2009

I want the A4 cell contains the calculation of B4 (but the number gained from the funtion row and if the B1 cell contains the number 10 the K(B1)=K10

[A4]=B(row())*K(B1)

View 4 Replies
View Related
Dec 27, 2011

I need a function to find the number of decimal places of a certain number (in this specific case doubles)

The first solution would be something like this:

Code:

'returns the number of decimal places within a double

Public Function getDecPlaces(inputNum As Double) As Long

Dim ndx As Long

ndx = InStr(1, inputNum, ".")

If ndx > 0 Then

getDecPlaces = Len$(CStr(inputNum)) - ndx

End If

End Function

But i feel there is likely a much better way of doing this..

View 9 Replies
View Related