# Five Digit Numbers That Represent A Persons Code

Dec 1, 2009
In 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.

View 9 Replies
ADVERTISEMENT
Mar 19, 2013

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

View 2 Replies
View Related
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
Jun 30, 2008

how to make letter represent numbers what i am trying to do. i am trying to put a sheet together to make my life easyer. i am a lectuer and need to keep trake of studes progress how this is done is that over the year they are graded my Pass Merit Distinction in using offic 2007 (Excel)and i was to make it so that if i put in a P it will =6 points ,M=12 points and D=18 points and for the total points to for each student to come up on the side after the units if there a rule for this and how would i put the rule into the sheet ?

View 3 Replies
View Related
Oct 8, 2007

The following percentages equal these numbers:

100% = 5

93-99% = 3

80-92% = 1

<80% = 0

From F5 to AR5 percentages scores will be inserted for each row.

So percentages scores for the first three entries (F5-H5) should yield the following result:

100% + 95% + 85% = 5 + 3 + 1 = 9

View 3 Replies
View Related
Mar 13, 2014

IF key of C has notes CDEFGABCDEFGABC progression, then notes

1-2-3-4 : 2-3-4-5 : 3-4-5-6 will result in C D E F D E F G E F G A respectively

I want to create note patterns regardless of scale type: Major, minor, modal etc in each of 12 keys. Music software exists but simple EXCEL formulas should be available or easily created.

View 2 Replies
View Related
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
Mar 12, 2013

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

View 1 Replies
View Related
Feb 21, 2014

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.

View 1 Replies
View Related
Jul 16, 2008

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 Related
Dec 9, 2008

I 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?

View 3 Replies
View Related
Mar 22, 2009

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?

View 9 Replies
View Related
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
Mar 25, 2004

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?

View 9 Replies
View Related
Jul 16, 2007

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.

View 7 Replies
View Related
Feb 19, 2014

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?

View 8 Replies
View Related
May 31, 2014

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 Related
Oct 10, 2008

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

View 5 Replies
View Related
Aug 5, 2013

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)

View 1 Replies
View Related
Apr 21, 2009

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 Related
Dec 8, 2009

I have many dates in a column in a 6 digit format such as:

950223

950224

950225

These dates are Feb 23, 1995; Feb 24, 1995 etc.

How can I convert these dates in the column to 2/23/1995, 2/24/1995 etc?

View 3 Replies
View Related
May 6, 2013

I have a table where

Then I have a set of codes that look like 1ZGM.BA, where the first number/letter relates to a chart

The chart contains approx. 34 different codes. numbers 1-8 and letters A-Z.

Codes look like 1ZGM.BA or A1QL.AA ...I'm only interested in the first character (does not matter if its a letter or number) to decode the product.

The chart looks like

1=Widget Smooth

2=Widget Rough

...and so on.

A=Widget Thick

B=Widget Thin

...and so on to letter Z.

I want to decode the first letter/number of a code that looks like 1ZGM.BA, where the 1 would mean the product is a smooth Widget, and A1QL.AA would equal Widget Thick product.

View 1 Replies
View Related
Jun 22, 2014

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.

View 3 Replies
View Related
Nov 15, 2006

I have a list of data that is copied from another application. Once data is pasted into Excel it is one cell. My challenge is following. I need to extract only the rows that appear below particular 9 characther alfa numeric code which is the first character in a row. This character however only appears sporadically so there is a whole list of data underneath it. See below....

View 9 Replies
View Related
Jun 21, 2007

I have a spreadsheet with a row for a company name, their offer price underneath that, and a 1,2, or 3 underneath that. In a seperate tab I have the offer prices in order of rank with the company name next to the corresponding price using the Index function. I also need to be able to color code the background of the offer price based upon the 1,2 or 3 associated with each name. I am new to the Index stuff so I need some help putting this one together.

View 3 Replies
View Related
Jun 20, 2008

I would like to have a list of names in column A1 Smith, A2 Jones, A3 Johnson etc. Everytime someones name appears in E1-E20 I would like the adjacent column next to that persons name to turn red. Example: I type Smith in E14 -> B1 turns red. If it's not too complicated it would be good if excel could recognize the name in any font,capitols etc.

Lastly if I could have a pop up appear if a persons name appears more than once in column E that would be great. Example type Smith in E1 and then when I type smith in E4 pop appears--Name already used once do you want to continue?

View 9 Replies
View Related
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
Jul 16, 2013

Is it possible for 2 different persons to lock different cells on the same worksheet?

View 3 Replies
View Related
Mar 11, 2008

I want to count how many times a name is listed in the first 5 rows of 36 columns.

View 9 Replies
View Related
Jul 28, 2009

If I type the name Joe Bloggs How can I make whenever i type that name the computer knows to retreive a number or formula from a cell on another or same sheet.

View 9 Replies
View Related