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 ?
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.
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 RelatedMy 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
who can provide me a full formula enabling me to calculate the check digit(MOD 10) of the a given number?
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!
This is what I need:
Columns B, C, D & E are all populated with 3 digit numbers.
I would like column F to automatically populate with any of the 3 digit numbers that share two numbers, i.e.
F2 might look like this (using 00 as the pair):
001, 040
F3 might look like this (using 01 as the pair):
701, 051, 110, 001, 120
F4 might look like this (using 12 as the pair):
123, 721, 281, 912, 112, 120
etc...
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!
Combining two equations with MOD function for UPC check digit calculation.
I wanted to combine these if possible,
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
If you look at the attached picture, it is from a workbook.
The frame contains data depending on what tabstrip is chosen.
It is controlled with hiding rows in vba, how on earth do you get a frame like that which contains links to another worksheet?
I have a column that contains 4 digit numbers that I want to convert to a text value. Here is my example
Number Text Value
7004 RBPA
7002 DCVA
7001 PVBA
.....etc.
There is a total of 10 different number values. I want to include the conversion vba into an existing macro that is performing other functions on the spreadsheet.
I need a way to display all 8 digit numbers that have the digits 1-8 in them. (ie. 12345678 but NOT 12345679 OR 12345677) Also If I could somehow divide by 13 then check for whole numbers
View 14 Replies View RelatedI have a spreadsheet with 1000's of 3 digit numbers and each one has a underscore in front of it.
HOw can I remove the underscore from each of these 3 digit numbers without doing a find/replace on each different number?
using Excel 2007, windowsXP, and am trying to find the right formula for the following problem.
I have a column of 3 digit numbers, and want to determine how many times a number occurs in the first position ie: 123 the second position ie: 123 and the third position ie: 123 in the entire column of figures?
I have a spreadsheet, with about 1,500 lines. In column A is a list of numbers that I need to remove the last digit from each number, for example in A22 is 02602726521 - I need this to be 0260272652. The numbers are all uniqe.
How can I remove the last digit from every number, without going in manually to do so?
Is there any way changing general four digit number into hours and minutes?
I have almost 31000 rows of imported data, showing arrival and departure times with four digits (dep 2130, arr 0130) without colons and I need to subtract dep from arr to get block hours. Is there a way to add colon to multiple cells or some other solution to my problem?
Adding colon to 62000 cells one by one does not sound that good to me.
I am trying to write a function that will give me the amount of time a certain number shows up in the same digit place in a large set of numbers.
For example:
111112221233313
111212222233323
111312223233333
111412224233343
111512225233353
111612226233363
111712227233373
111812228233383
111912229233393
given the number set how many times does the number 5 show up in the 4th digit. I know its 3 but for the data size I need to run it on, it is impossible to count.
What function would count how many times a certain number shows up in the same spot?
I have a userform for keeping records and would love to incorporate a new feature. I would want to generate a random serial number for each entry made with the userform.
View 2 Replies View RelatedI've modified this macro I found on this forum, all it does is compares 2 lists of 6 digit serial numbers and and then tells me which numbers are in list 1 that are not in list 2.
However, I want to modify it so that it also gives me the numbers in list 2 that are not in list 1 (put into column F). I then need it to cut the matching serial numbers (in columns A and B) from sheet 1 and paste them into sheet 2.
modifying the following VBA code; this code auto-generates 4-digit unique numbers, using zero as one of the starting digit as well. I need the 4-digit numbers NOT to begin with a zero, the 4-digit numbers should only begin with numbers 1-9.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range
Dim Test As Integer
[Code]....
How do I format numbers so it will show the actual first digit, and the rest as x?
so that $1,234.56
will appear as $1,xxx.xx
or alternatively,
$1,234,567.89
will appear as $1,xxx,xxx.xx
I would prefer to do this with custom formatting rather than VBA,
(this is so I can generate sample reports for prospective clients, without showing the real numbers in the tables)
I have a large list of phone numbers & some of the numbers do not have area codes. The area code is the same for all numbers. Is there a way to add the area codes to all of these numbers without keying all in manually
View 9 Replies View RelatedIn one column (f5:F100) I have five digit numbers that represent a persons code. I have five groups of codes, they are 10000-15000, 16000-19000, 20000-25000, 26000-29000, and 30000-40000. In another column (Column H) I have an "X" saying the are qualified in something.
I am looking for how to write a statement that says answers this question:
How many people are qualified in the codes between 10000-15000.
Ultimately I am going to take this number and divide it by the total number of people in the group to get a percetage of qualified people in the group.
I have a column that has section numbers like 001.002.006.003.010.011.002.
I would like to divide that single column into seven columns with only the single or double digit in it, ie
1 in a cell
2 in a cell
6 in a cell
3 in a cell
10 in a cell
11 in a cell
2 in a cell
Have been using MID and FIND togther, but when I get to the double digits like 10 an 11 I run into problems.
I have 25 random numbers and I would like to get a possible 5 digit combinations of these numbers. Can anybody help me with the possible formula?
View 9 Replies View RelatedI have a list of numbers that I have to concatenate with another number depending on if it falls within the parameters and I don't know how to do a lookup on that. I currently am using a complex if/or/and statement which is too dificult to debug and extremely complex like this: if(OR(AND(a>=1,a<=10),AND(a>=20,a<=30)),concatenate(a,"123"),if(OR(AND(a>10,a<20),(a>30,a<40))),conc atenate(a,"456"),"a is not in the parameter")
is there a way to do a lookup for this?
Something else to throw into the mix, there are some ranges that shouldn't be there and return nothing so from 40-55, there shouldn't be a result.
also, there are some that are just one number (56 gets 789 but then at 57 it goes back to abc)
I want to do a check with one cell that has a list of a maximum of 4 numbers seperated by a comma and space, with 4 cells that have one of these numbers in each of them. The numbers wont be in the same order, and they wont be repeated.
Example
A3 = 4, 12, 19, 34
A4 = 12
A5 = 19
A6 = 4
A7 = 34
I only need to highlight if they dont match. So result could either be a tick or cell changes colour.
how to check if cells contain only whole numbers (cells are in text format)?
I do not know how the code is for the activecell checking
Example Range("A1:A10")
ColumnA
12345
12345
555,10
12345
1.20
A6666
12345
12345
12345
As soon the code hits a cell containing something other then numbers then Msgbox "Error"
Sub Check()
i = 1
Do Until Cells(i, 1).Text = ""
Cells(i, 1).Select
If Not Activecell.Text only numbers then
MsgBox "Error"
End If
i = i + 1
Loop
End Sub
I'm looking for a formula to check whether a given number is between two other numbers.
Example: I have a list of data items which give the following info:
A.......B..........C.........D
ID# Group Low# High#
1001 food 12345 13960
1003 soap 27192 32245
2004 tires 93471 95532
2005 food 71174 76772
2006 soap 33183 36591
Such a list is many hundred of lines long.
Then I have other data which provides a Group ID and a product number, which number is likely to between the Low# and High# in the data list.
What I want is a formula to check whether the product number is between the Low# and the High#, AND that the Group matches.
Example: the data being supplied is Soap 28332. So, I want a formula to check through the data list, and if 28332 is between the Low# and the High# AND if the group SOAP matches, then give back the correct ID number.
Thus for the data Soap 28332, I would want the answer to be 1003, which is the ID # for soap in the given range.
A data listing of Food 73776 should give back the ID 2005, etc, etc. This list of data needing the correct ID# is thousands of lines long.
Thus I need a formula to check that the given number is within the low and high #, and that the group designation matches. If so, then give back the correct ID#.