Sequential Check Digit Validation

Nov 14, 2009

I have a spreadsheet that pulls in a log file after running a macro with 100000eight digit numbers which may or may not have a record in the system. These numbers are produced by using any seven digits and adding a check digit at the end for an eight digit number using the mod function specifically Mod7...=MOD(A1,7) , for example, if cell A1 has 1234567 the mod7 function will produce the check digit 5 then concatenating for the eight digit number 12345675. the next number will be produced by adding 1 to the end of the 7 digit number 1234567+1=1234568 then concatenating again the number with the check digit 6, 12345686. this would be sequential order.

my spreadsheet gets all the eight digit numbers with no record and sorts them in MOD7 sequential order in one column. since the spreadsheet ignores the eight digit numbers with records. my compiled list of numbers have non sequential numbers in the list.

i need a way in VB to loop through 100000 lines (column A) and find the first and last number of a MOD7 sequential range and place the first number in (column B) and the last in (column C).

I have figured a way to do this in a spreadsheet but it takes too many functions and calculations in 100000 lines so the spreadsheet takes too long to open and sometimes crashes!

View 9 Replies


ADVERTISEMENT

Check Digit For 8 Digit Number

Jun 13, 2014

How to calculate a check digit in excel. The details to calculate this are as follows:

All variables in the calculation are positive integers.

We take each integer of the pro number and multiply it by a value and sum them to get a total.

An Example: 8 Digit Pro Number: 66988757
Pro Number: 6 6 9 8 8 7 5 7
Position in the Pro Number: 1 2 3 4 5 6 7 8
Multiply each digit in the pro number by (10-position) in the number, to achieve a sum.

Using our example pro again:
6*(10-1) + 6*(10-2) + 9*(10-3) + 8*(10-4) + 8*(10-5) + 7*(10-6) + 5*(10-7) + 7*(10-8)
6*9 + 6*8 + 9*7 + 8*6 + 8*5 + 7*4 + 5*3 + 7*2
54+ 48+ 63+48+40+28+15+14 = 310
Take the Sum of the previous calculation and divide it by 11
310/11 = 28

(Actually, it's 28.181818, but since we're working with integers, we truncate everything behind the decimal).

Figure a remainder by multiplying the quotient by 11 and subtracting from the sum.
Remainder = 310 - (28*11) ---> Remainder = 310 - 308 ----> Remainder = 2
Check Digit = 11- Remainder
Check Digit = 11 - 2 ---> Check Digit = 9
Note: if the check digit is 10 or 11, need to subtract 10 from the Check Digit.

View 3 Replies View Related

Macro To Force Format And Check Barcode Check Digit

Nov 27, 2009

My company uses 4 types of barcodes 8, 12, 13, & 14 number barcodes for our products my problem is that I can't figure out how to force the barcode to format properly no matter how somebody enters it if they don't use spaces or put them in the wrong spots, I can't use custom formats because there is 4 different layouts

8 digit should be "#### ####"
12 digit should be "###### ######"
13 digit should be "# ###### ######"
14 digit should be "# ## ##### ######"

these barcodes are in columns L, M, & N also right now 'm using a formula in another cell to verify the barcodes by calculating the check digit and comparing it to the check digit typed the formula i'm using is

View 9 Replies View Related

Check & Add Sequential Numbers In Column

Jan 26, 2008

I have two columns A and B filled with numbers. Column A has the identifier number and Column B has number that represents intensity for the data set.

Column A can be anything from 75-1000, however, sometimes the adjacent rows are not sequential (i.e. 101, 102, 104).

1) Is there a way to automatically check for skipping numbers in Column A.
2) When it finds a skipped number, insert a row for column A and B.
3) Then put the correct number in Column A (i.e. 103) then put 0 in for column B

View 8 Replies View Related

Data Validation Dynamic Sequential Numbers List

Jul 21, 2007

I am looking for a way to define a 'Data Validated' Cell to hold a List of Dynamic Sequential Numbers - from 1 to the Value of cell A4. For example:

If cell A4 has a value of 5 the 'Data Validated' List will be 1,2,3,4,5

I have tried to 'name' the Array-Formula {=ROW(INDIRECT("1:"&A4))} and put the 'name' as the List source but without success.

View 8 Replies View Related

Calculate The Check Digit

Nov 1, 2009

who can provide me a full formula enabling me to calculate the check digit(MOD 10) of the a given number?

View 9 Replies View Related

How To Calculate Check Digit (MOD 10)

Aug 19, 2003

How do I create a formula that will calculate a check digit for a 9-digit number using MOD 10? The weighting is 212121212. The problem I am having is that if the sum of the product is in double digits, they must be treated as individual digits.

Example:

Number To Be Calculated: 0 0 4 4 5 5 5 2 5

Weighting: 2 1 2 1 2 1 2 1 2

Sum: 0+0+8+4+1+0+5+1+0+2+1+0

22/10 = 2 r2
10-2 = 8 (Check Digit)

Is it possible to create such a formula? I need to calculate several hundred and do not want to do this manually!

View 9 Replies View Related

Check Digit For Container Numbers

Oct 24, 2009

I wanna create a buildin check digit for container numbers

so it will exists out of 11 cells. ( GATU 021097-9 )

is it possible when u type any letter/number in the first cell on a row it automaticly jumps to the next cell, repeating this for the full container number ?

View 9 Replies View Related

Data Validation For 5 Digit Alpha Entry Only

Dec 3, 2008

I require a formula for custom data validation to permit entry 5 digit aplha characters only.

No spaces, or numbers permitted.

View 2 Replies View Related

Combining Two Equations With MOD Function For UPC Check Digit Calculation.

Mar 26, 2009

Combining two equations with MOD function for UPC check digit calculation.
I wanted to combine these if possible,

View 4 Replies View Related

Check 4 Digit Fiscal Year Is Entered In A Cell

May 6, 2009

This is my code. It is for checking that only a 4 digit fiscal year is entered in a cell. The code works fine except it only checks a max of three time. So if the user enters xxx it will prompt for a correct response three times, if xxx is entere a 4th time then that value appears in the cell. I was wondering why this is taking place.

Here is my

View 7 Replies View Related

Data Validation List - Country Names Are Selections But 2 Digit Codes Need To Be Result

May 7, 2014

I've written a procedure that loads a list of country selections in a dropdown (see attached/vb editor/ThisWorkbook).

The problem is the valid values for this column need to be just the two digit country codes, not the country code +dash+country name. The reason the dropdown selections include both the two digit country code and the country name is because most users will not know the two digit codes. Presenting the selections in this fashion will make t much easier for the user to make the proper selection.

So here is what I need: When the user makes a selection from the dropdown list, the resulting value needs to be the two digit country code only. Is there a way to incorporate this formula .... =MID(B2|1|2) ... in the vba procedure which runs after the user makes a selection? Or, is there another way to accomplish this?

Example_COO.xlsm‎

View 10 Replies View Related

Copy / Drag From Sequential Column To Non-Sequential Ones While Retaining Sequence

Jan 29, 2014

I have a form made that needs to copy a value from another sheet. The Form is 10 rows 5 columns and and in format for easy print. On sheet2 I have a column where each row is filled with a name.

I need to put this name into sheet1, so in the the appropriate cell I put =Sheet2!B2 and it gives me the value (name) from the other sheet.

Then I want to copy the form bellow the 1st one so that I have the same form but with the next name, which means I want =Sheet2!B3 to appear when I copy, but since my form is 10 rows when I copy it the formula copies to =Sheet2!B12 instead of B3.

I need to make close to a thousand of this forms ready for printing and I would like to avoid having to manually set the formula for the next cell.

View 3 Replies View Related

Seperate 2 Digit Number Into 2 Single Digit Numbers

Apr 3, 2008

I know you can take a number from one cell and combine it with number from another cell and make it one number. What I need to do is the reverse. Take a two digit number in a cell and separate it into single digits in two cells. If you have the number 50 in a cell, then is there a formula that will take the 5 and put it in cell and take the 0 and put it in the cell beside it?

View 4 Replies View Related

Print Validation Check

Mar 21, 2007

I need to disable the normal Print Icon and Print function from the menu bar in excel. I did find an article for this but it disabled the function for all excel spreadsheets! How can I overcome this so that its only relevant to this particular worksheet.

The reason I want to disable the normal printing functionality is so that the end user clicks my own print button which in turn before printing will check if 2 cells in my worksheet match. If they do not match a message will appear and they won't be able to print the worksheet(in essence a certificate). So I need to validate cell a1 against cell a2

View 9 Replies View Related

Date Validation Check With VBA

Apr 11, 2007

How i can create a Validation rule on my form (that i made using Microsoft Visual Basic) to make sure that they key in a date later than that of todays date

View 9 Replies View Related

Data Validation - Check For One Or More Spaces

Sep 1, 2013

I want to use the Validation function under the Data menu to check a cell for a number range, a blank cell, or for one or more spaces. I have a formula to do the first two but not to check for one or more spaces. I'm running Excel for Mac 2011.

View 5 Replies View Related

Check If Cell Has Data Validation With VBA

Nov 15, 2008

How can I check if a cell has data validation with a VBA procedure?

I want to do this because I am using a procedure to add data validation and I get an error if the cell already contains data validation.

View 9 Replies View Related

Data Validation Allow Text Drop And Check?

Feb 5, 2014

i need some formula to data validation with this criteria :

allow only text "DROP" and "check" ...

View 6 Replies View Related

Vehicle Number Plate Validation Check

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

Macro To Check Validation Dropdowns Left Blank

Oct 27, 2008

Got a table where I've placed Data validation - List on them.

Placed a macro on a button so that the data on the table where the validation list is placed is transfered to another sheet (same workbook).

What I need is a to modify the macro assigned to the button so that when the button is clicked to activate the macro:

a.) Can identify if there are any blanks on specified cells with validation list
b.) Stops the macro from copying the fields to the other sheet
c.) Alert the user of the blanks (an alert stating there is a blank would suffice)
d.) Alert should preferably be similar to a Stop alert from Data Validation.

Can anyone help? Been reading through several threads but don't seem to find anything that could help me - tried reading a Visual basic thread and now i've got a headache....

View 15 Replies View Related

ComboBox Validation: Check Values Before Closing UserForm

Sep 5, 2006

I have a userform with 15 Combo boxes & I would like to make sure all the enabled combo boxes (some may be disabled) have a value > 0 (Zero) before exiting the userform.

View 5 Replies View Related

Remove The Last Digit From Each Number, Turning It Into A 5 Digit Number

Mar 2, 2009

I have a column of 6 digit numbers in excel, and I need to remove the last digit from each number, turning it into a 5 digit number. No rounding, just simply remove the last digit. Each number is different. Does anyone know how to quickly and efficiently remove the last digit from each number? I can convert to alphanumeric string if need be...

View 4 Replies View Related

If / Then In A Macro - Changing 2 Digit Year Into 4 Digit Year

Jul 3, 2014

I need a macro where I can highlight a column and change all of the 2 digit years to a 4 digit year (actually, some of the 2 digit years are only 1 digit, e.g. "9" instead of "09").

I have an if then statement that I can use in the column after it, but I'd rather change the actual numbers in the original column, rather than adding another column (and having to keep the original, too).

The statement I had was:
=if(A1>=50,1900+A1,2000+A1)

I just copy dragged down to get the cell numbers for the rest of the column... but using A1 was just for an example, here, it's not necessarily going to be in that column. It needs to be just whichever cells I select.

It seems like it should be pretty simple, but I don't know how to word it in a macro.

Starting Column Example:
12

13

14

99

11

[Code] ......

For some reason I can't get rid of the borders...

View 6 Replies View Related

Convert 4 Digit Zip Code To 5 Digit Zip Code?

Apr 25, 2014

I have an address (city, state, zip format) in a cell. Some of the zip codes only have a 4-digit zip code which is the way the file was received. I know these zip codes have leading zero. How can I convert these to a five-digit code. Examples are as follows:

Waltham MA 2453
convert to 'Waltham MA 02453'

Boston MA 2210
convert to 'Boston MA 02210'

CEDAR GROVE, NJ 7009-1174
convert to 'CEDAR GROVE, NJ 07009-1174'

TEANECK CITY, NJ 7666
convert to 'TEANECK CITY, NJ 07666'

View 3 Replies View Related

Converting 2 Digit Year Into 4 Digit Year

Apr 15, 2008

I have 2 digit years (98, 99, 00, 01) that I need to convert to 4 digit years (1998, 1999, 2000, 2001). There is one year per cell. If it was simply a matter of adding 19 or 20 to the beginning of each, I could do that. But since there's a combination of both 19 and 20 that needs to be added and there all intermingled, I'm not sure how to do it.

Can a rule be written to add 19 to the beginning except if the current cell starts with a 0, then add 20? The highest year is 2008 (no 2010 to deal with).

Example:

98 --> 1998
99 --> 1999
00 --> 2000
01 --> 2001

View 9 Replies View Related

Using If To Sum() Non-sequential Numbers

Jan 23, 2009

I need a formula to add data in it's respective column, only if the month and year match.

The columns of data will be very long, thousands of lines possibly.

So, basically, in this example.

in O1, if the the data in the columns below match the month(M1) and year (N1), then sum those variables.

View 10 Replies View Related

Using For / Next With Sequential Variable Name

Jan 10, 2013

I'm just looking to step through some variables using for/next

variable1 = 2
variable2 = 7
Variable3 = 9
Variable4 = 15

For xxx = variable1 to variable4

'all sorts of fun code here

Next xxx

-obviously this doesn't work, it steps from variable1 right to variable 4....need a way so the code includes variable 2 and 3.

View 6 Replies View Related

Sequential Months

Feb 8, 2010

I am creating a budget worksheet for non-technical users. They choose a starting month from a drop down menu (already created). I want the remaining 11 months to automatically fill in to the right (in a row). I have created a macro (initiating autofill) that requires them to click the button after they choose the starting month but this copies the drop down menu & Input message from the Data Validation I used to guide them initially.

View 9 Replies View Related

Sequential Numbering

Oct 25, 2009

I have a workbook with two worksheets. Worksheet #1 is a form that will be populated with data and saved as a new worksheet, then cleared and used repeatedly as a master form. Worksheet #2 is a log / register of the unique forms completed and saved from the master each time. I need to assign a unique sequential # to each form when it is saved and record this number in a column on Worksheet #2 (the Log). I am using some macros for the copy work but struggling with the auto-numbering of the forms when completed and saved.

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved