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.

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

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.

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?

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.

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

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

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,

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â€Ž

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?

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

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.

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

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

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?

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.

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?

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:

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].....

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.

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?

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.

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'.

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

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.

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.