Vba  Converting A Column Number Into Column Letter 


Vba  Converting A Column Number Into Column Letter  Excel 
View Answers 
I have over 2000 columns in my sheet  I am importing data from external binary random acces files  I bring these in as arrays of numbers  so how can I convert column 60 to a letter BH  column 186 to GD and so on.
Similar Excel Tutorials
Select Cells in Excel using Macros and VBA
This is actually a very easy thing to do and only requires a couple lines of code. Below I will show you how to sel ...
This is actually a very easy thing to do and only requires a couple lines of code. Below I will show you how to sel ...
Make Column Headings Numbers instead of Letters  Make R1C1 Style References in Excel 2007
In Microsoft Excel you reference columns as letters by default  A1, B3, C5, etc. But you can also reference the co ...
In Microsoft Excel you reference columns as letters by default  A1, B3, C5, etc. But you can also reference the co ...
How to import Text Files (CSV) into Excel
Text files in CSV format are one of the easiest ways to store and transfer data as it is one of the most compatible ...
Text files in CSV format are one of the easiest ways to store and transfer data as it is one of the most compatible ...
Display Numbers, Dates, & Times in the Correct Format in Excel
In this tutorial I am going to look at Number formatting. Number formatting allows you to change how numbers are di ...
In this tutorial I am going to look at Number formatting. Number formatting allows you to change how numbers are di ...
Helpful Excel Macros
Reverse Row or Column Order in a Worksheet
 This macro reverses the order of a selected row or column in excel. It will completely reverse the data.
 This macro reverses the order of a selected row or column in excel. It will completely reverse the data.
Sort Data that Doesn't Have Headers in Descending Order in Excel
 Sort data in Excel that doesn't have headers using a macro. This Excel macro assumes that your data set does not have a
 Sort data in Excel that doesn't have headers using a macro. This Excel macro assumes that your data set does not have a
Generate a NonRepeating List of Random Numbers in Excel  UDF
 Generate a series of nonrepeating random numbers in Excel with this UDF (user defined function). This is a great funct
 Generate a series of nonrepeating random numbers in Excel with this UDF (user defined function). This is a great funct
Vlookup Macro to Return All Matching Results and Stack them with Previous Results
 This is very similar to the other Vlookup type Macro in that it returns all of the results that match a particular se
 This is very similar to the other Vlookup type Macro in that it returns all of the results that match a particular se
Filter Data Sets on Multiple Columns with Multiple Criteria at Once in Excel  AutoFilter
 This Excel macro allows you to filter a data set on multiple columns and criteria at once. This means that you can filt
 This Excel macro allows you to filter a data set on multiple columns and criteria at once. This means that you can filt
Similar Topics
I have six columns, Column A titled "Cooperation", Column B titled "Work Habits", and Column C titled "Total". Column D titled "Letter Grade", Column E titled "Cooperation Grade", and Column F titled "Work Habits Grade". I want the number that is entered into columns A, B, and C to automatically enter a corresponding letter grade into columns D, E, and F. Column A corresponds with column E, Column B with column F, and column C with column D.
If the number in column A is less than or equal to 2 i need the letter "E" to appear in column E, if the number is 34 then the letter "S" should appear, if the number is 5 or higher the letter "U" should appear.
If the number in column B is less than or equal to 2 i need the letter "E" to appear in column F, if the number is 34 then the letter "S" should appear, if the number is 5 or higher the letter "U" should appear.
If the number in column C is less than or equal to 3 i need the letter "A" to appear in column D, if the number is 46 then the letter "B" should appear, if the number is 78 the letter "C" should appear, if the number is 910 the letter "D" should appear, if the number is greater than 10 the letter "F" should appear.
I hope this makes sense. Thanks for looking
If the number in column A is less than or equal to 2 i need the letter "E" to appear in column E, if the number is 34 then the letter "S" should appear, if the number is 5 or higher the letter "U" should appear.
If the number in column B is less than or equal to 2 i need the letter "E" to appear in column F, if the number is 34 then the letter "S" should appear, if the number is 5 or higher the letter "U" should appear.
If the number in column C is less than or equal to 3 i need the letter "A" to appear in column D, if the number is 46 then the letter "B" should appear, if the number is 78 the letter "C" should appear, if the number is 910 the letter "D" should appear, if the number is greater than 10 the letter "F" should appear.
I hope this makes sense. Thanks for looking
I just know this one is going to be obvious...
how do I convert a column number to its equivalent letter heading?
I am moving data from one sheet to another and manipulating the arrays using variables like row_num & col_num, but want to convert the col_num to the letter heading.
i.e. column 93 is "CO"
I could probably write a sub to do it but surely this exists already?
Ian
how do I convert a column number to its equivalent letter heading?
I am moving data from one sheet to another and manipulating the arrays using variables like row_num & col_num, but want to convert the col_num to the letter heading.
i.e. column 93 is "CO"
I could probably write a sub to do it but surely this exists already?
Ian
Hi there,
In column "A" I have names. I am after a way to search that column for a certain letter and display a number corresponding to how many of that letter were found throughout column "A"
Maybe something like in column "B" rows 126 represent the corrisponding letter eg
B1 = all the a's as a number value from column "A"
B2 = all the b's as a number value from column "A"
etc etc
So if
A1 = Tim
A2 = Tom
A3 = Tina
Then
B1 = 1 (cause there is only one "a"
while
B9 = 2 (cause i=9th letter of alphabet and there are 2)
etc etc
Hope that makes sense.
Cheers
Tim
In column "A" I have names. I am after a way to search that column for a certain letter and display a number corresponding to how many of that letter were found throughout column "A"
Maybe something like in column "B" rows 126 represent the corrisponding letter eg
B1 = all the a's as a number value from column "A"
B2 = all the b's as a number value from column "A"
etc etc
So if
A1 = Tim
A2 = Tom
A3 = Tina
Then
B1 = 1 (cause there is only one "a"
while
B9 = 2 (cause i=9th letter of alphabet and there are 2)
etc etc
Hope that makes sense.
Cheers
Tim
Hi there,
I've seen several posts about converting column numbers into column letters but I can't work out how to to it within my existing formula:
=SUMPRODUCT(($K$5:$CH$5="Yes")*(COLUMN($K$5:$CH$5)))
I'd rather convert it via formula than macro.
Any help greatly appreciated!
I've seen several posts about converting column numbers into column letters but I can't work out how to to it within my existing formula:
=SUMPRODUCT(($K$5:$CH$5="Yes")*(COLUMN($K$5:$CH$5)))
I'd rather convert it via formula than macro.
Any help greatly appreciated!
Hi there,
I'm trying to create a formula to get Excel 2003 to do basically this:
if the value of column i = 0 to 5 the letter value shown is L
if the value of column i  6 to 11 the letter value shown is M
if the value of column i  12 to 17 the letter value shown is H
basically i have columns F, G, H that have numerical values and a formula that adds those together to come up with a number 0 to 17. I want that number, based on the idea above, to visualize a letter instead of the number
for example:
F = 4
G = 4
H = 7
I = 15 so it would show up as H in the cell.
something like this:
=SUM(F12:H12)  THEN add the other part where if the answer is 05 it displays L, etc.
Thanks for any help and input!
Stephanie
I'm trying to create a formula to get Excel 2003 to do basically this:
if the value of column i = 0 to 5 the letter value shown is L
if the value of column i  6 to 11 the letter value shown is M
if the value of column i  12 to 17 the letter value shown is H
basically i have columns F, G, H that have numerical values and a formula that adds those together to come up with a number 0 to 17. I want that number, based on the idea above, to visualize a letter instead of the number
for example:
F = 4
G = 4
H = 7
I = 15 so it would show up as H in the cell.
something like this:
=SUM(F12:H12)  THEN add the other part where if the answer is 05 it displays L, etc.
Thanks for any help and input!
Stephanie
I hope my title explains it all.
I have a spreadsheet that has a list of letters in column A and numbers in column B. I would like to add column B together but only the numbers from the rows that have the letter D in column A.
On another note, I will need to count the number of 'N' in column C  also if column A had the letter 'D'.
I hope this makes sense!
Many thanks
Jimmi
I need to add one column into another..
i have a Column of numbers that all need to have a letter in front of them;
right now: 1, 10, 13, 14...etc
needs to be PS1, PS10, PS13, PS14..etc
The column does not go in numerical order so i cannot just drag the letter down. Is there a way to add these letter w/o having to copy and paste it into every row? Please help, i am trying to make a deadline!
i have a Column of numbers that all need to have a letter in front of them;
right now: 1, 10, 13, 14...etc
needs to be PS1, PS10, PS13, PS14..etc
The column does not go in numerical order so i cannot just drag the letter down. Is there a way to add these letter w/o having to copy and paste it into every row? Please help, i am trying to make a deadline!
Hi!
I want to convert the following:
8 to A*
7 to A
6 to B
5 to C
I have a column of pupil results in numbers but need to convert them a Grade. Can anyone help?
Many thanks,
Russ
I want to convert the following:
8 to A*
7 to A
6 to B
5 to C
I have a column of pupil results in numbers but need to convert them a Grade. Can anyone help?
Many thanks,
Russ
Hi
How can I get the "Letter" of the column instead of the number of the column?
For intance, when using:
LastColumn = ActiveSheet.UsedRange.Column.Count it returns the Number of the last column with data instead of the column letter.
Thanks
Ed.
How can I get the "Letter" of the column instead of the number of the column?
For intance, when using:
LastColumn = ActiveSheet.UsedRange.Column.Count it returns the Number of the last column with data instead of the column letter.
Thanks
Ed.
Hi Guys,
I need you're assistance for the following;
I am trying to devise a spreadsheet in which I need to enter a letter from a drop box into a cell then this letter to convert to a number in another cell to aid calculation.
I.e. cell a1 = M to convert to the figure 8 in cell b1 or if another letter is selected i.e. cell a1 = A to convert to the figure 9.5 in cell b1.
If this is possible I would emphasise this has to be from a validation dropbox.
As always  appreciate whatever help you care to give.
Thanks = dustyv
I need you're assistance for the following;
I am trying to devise a spreadsheet in which I need to enter a letter from a drop box into a cell then this letter to convert to a number in another cell to aid calculation.
I.e. cell a1 = M to convert to the figure 8 in cell b1 or if another letter is selected i.e. cell a1 = A to convert to the figure 9.5 in cell b1.
If this is possible I would emphasise this has to be from a validation dropbox.
As always  appreciate whatever help you care to give.
Thanks = dustyv
I have look for this on google for almost a week . And I have found nothing.
I'm Importing Columns from one sheet to another in the same workbook. imports good But:
1 Some columns with first records/rows blank do not import at all.
2 I have a column with some letter/numbers combinations and single numbers at times. Those single numbers are not imported. And half my data in that column are single numbers.
Can anyone help me please ?
I'm Importing Columns from one sheet to another in the same workbook. imports good But:
1 Some columns with first records/rows blank do not import at all.
2 I have a column with some letter/numbers combinations and single numbers at times. Those single numbers are not imported. And half my data in that column are single numbers.
Can anyone help me please ?
Hi there,
I'm new to the group as you've probably guessed, I'm a little stuck and have trawled all over the web seeking an answer to this problem, to now i've found nothing, I thought you guys may be able to help.
Say I have the following in excel:
Column 1 I Column 2 I Column 3
I I
One I Number I
A I Letter I
One I Number I
A I Letter I
One I Number I
Is there a way to make Excel say "okay there are three One's which are numbers S i'm going to delete 2 of them and them put 3 in Column 3, there's also 2 A's so I'll do the same leaving:"
Column 1 I Column 2 I Column 3
I I
One I Number I 3
A I Letter I 2
Any help that could be offered would be really appreciated.
Many thanks
Erm, it keeps taking my spaces out so it doesn't look more spreadsheety.
I'm new to the group as you've probably guessed, I'm a little stuck and have trawled all over the web seeking an answer to this problem, to now i've found nothing, I thought you guys may be able to help.
Say I have the following in excel:
Column 1 I Column 2 I Column 3
I I
One I Number I
A I Letter I
One I Number I
A I Letter I
One I Number I
Is there a way to make Excel say "okay there are three One's which are numbers S i'm going to delete 2 of them and them put 3 in Column 3, there's also 2 A's so I'll do the same leaving:"
Column 1 I Column 2 I Column 3
I I
One I Number I 3
A I Letter I 2
Any help that could be offered would be really appreciated.
Many thanks
Erm, it keeps taking my spaces out so it doesn't look more spreadsheety.
What is the efficient way to convert a known column number to its corresponding letter  with acknowledgements to Aladin for the R1C1 formula...
Lastcol is the known column number, iStr a string variable
Code:
TIA GT
Lastcol is the known column number, iStr a string variable
Code:
Cells(1, LastCol).FormulaR1C1 = "=SUBSTITUTE(ADDRESS(1,COLUMN(),4),""1"","""")" iStr = Cells(1, LastCol).Value Cells(1, LastCol).ClearContents 'just to tidy up the sheet Columns("E" & iStr).Copy etc...
TIA GT
I need help with learning how to execute a command in Excel. It might involve the VLOOKUP function or macros or neither. I'm not sure.
I have 2 columns of 10 rows (for this example in reality there are 100+ rows)
Column A: 1, 2, 3, 4, 5, 6, 7, 8, 9, 10
Column B: a, b, c, c, c, c, d, e, f, f
When rows in Column B have matching data (like the 4 c's in B3, B4, B5, B6 or the 2 f's in B9, B10), I need the numbers in Column A in those rows (3,4,5,6 and 9,10) to change to a random number/letter and the two groups have to have a different number/letter from each other.
So 3,4,5,6 in Column A need to become X,X,X,X and 9,10 in Column A need to become Y,Y.
(In other words, any instance in which the data in a row in Column B matches other data in a row in Column B, the data in the corresponding rows in Column A need to change to match each other AND different groups of matching data in Column B need to produce different groups of matching data in Column A.)
From the original:
Column A: 1, 2, 3, 4, 5, 6, 7, 8, 9, 10
Column B: a, b, c, c, c, c, d, e, f, f
The result needs to be:
Column A: 1, 2, X, X, X, X, 7, 8, Y, Y
Column B: a, b, c, c, c, c, d, e, f, f
or:
Column A: 1, 2, D28, D28, D28, D28, 7, 8, D29, D29
Column B: a, b, c, c, c, c, d, e, f, f
Data in Column B doesn't change, and data in Column A ONLY changes if the data in the corresponding row in Column B matches another row in Column B. It really doesn't matter what the values in Column A change to as long as the groups are different from one another.
How do I accomplish this? Simplest way is best, but up for learning something more complex if it's the only way.
Thanks Excel gurus!
I have a huge list of car models inventory. About 15,000+ entries in an Excel spreadsheet. I am trying to find out the first letter of the name of the car.
I have the following four formats:
Mercedes CLS 500 (i.e., Name begins with a letter)
2000 Chevrolet Corvette (i.e., 4 numbers + space then the letter)
4x4 Jeep (i.e., number + x + number + space then the letter)
8Wheel Tipper Truck (i.e., number plus a dash then the letter
It is very easy if the first character is a letter. I used the "Left(a1,1)" command to find the first letter. However, I am not sure how to find the first letter if the first character is a number.
Is there any easier way of find this without going through VBA?
Any help is greatly appreciated.
I have the following four formats:
Mercedes CLS 500 (i.e., Name begins with a letter)
2000 Chevrolet Corvette (i.e., 4 numbers + space then the letter)
4x4 Jeep (i.e., number + x + number + space then the letter)
8Wheel Tipper Truck (i.e., number plus a dash then the letter
It is very easy if the first character is a letter. I used the "Left(a1,1)" command to find the first letter. However, I am not sure how to find the first letter if the first character is a number.
Is there any easier way of find this without going through VBA?
Any help is greatly appreciated.
ok
Columns
A .... B ................ C ...... E .. F .. G .... H
A....Miami.........17......A..B..C...40
B....Detroit........06.....E..A..D...27
C....Dallas.........17.....C..E..B...33
D....Tampa Bay..00.....D..A..B...23
E....Kansas City..10.....A..C..E...44
What I am trying to get is a formula in Column H,to add the values of Column E,F & G, in each column of course the Letters A,B,C,D & E represent the numbers in column c, Letter A=17,Letter B=6,Letter C=17,etc ,Im not that savvy in excel,but Im sure Im missing something minor to get this to work,I can get it to work for one column but not all 3,any suggestions
Thanks
Steve
Columns
A .... B ................ C ...... E .. F .. G .... H
A....Miami.........17......A..B..C...40
B....Detroit........06.....E..A..D...27
C....Dallas.........17.....C..E..B...33
D....Tampa Bay..00.....D..A..B...23
E....Kansas City..10.....A..C..E...44
What I am trying to get is a formula in Column H,to add the values of Column E,F & G, in each column of course the Letters A,B,C,D & E represent the numbers in column c, Letter A=17,Letter B=6,Letter C=17,etc ,Im not that savvy in excel,but Im sure Im missing something minor to get this to work,I can get it to work for one column but not all 3,any suggestions
Thanks
Steve
Ultimate goal: Insert a page break at each blank row. I have the macro to insert page breaks already.
I run a report several times a day that needs to be printed and handed out to different people for specific tasks to be taken care of. The issue is that I get the data in one lump and I have to manually insert page breaks each time. There are a large number of 'Locations' that are grouped together to be handed to different people. The bulk of this can be separated just by the first letter of each location. Below is an example of what my data looks like, it only needs to insert a row based on what's in column A.
Code:
Code:
I run a report several times a day that needs to be printed and handed out to different people for specific tasks to be taken care of. The issue is that I get the data in one lump and I have to manually insert page breaks each time. There are a large number of 'Locations' that are grouped together to be handed to different people. The bulk of this can be separated just by the first letter of each location. Below is an example of what my data looks like, it only needs to insert a row based on what's in column A.
Code:
Original data AA0 random data random data AA1 random data random data AA2 random data random data AB0 random data random data AB1 random data random data AB2 random data random data AC0 random data random data AC1 random data random data AC2 random data random data BA0 random data random data BA1 random data random data BA2 random data random data BB0 random data random data BB1 random data random data BB2 random data random data BC0 random data random data BC1 random data random data BC2 random data random data BD0 random data random data BD1 random data random data BD2 random data random data BE0 random data random data BE1 random data random data BE2 random data random data BF0 random data random data BF1 random data random data BF2 random data random data CA0 random data random data CA1 random data random data CA2 random data random data CB0 random data random data CB1 random data random data CB2 random data random data CC0 random data random data CC1 random data random data CC2 random data random data CD0 random data random data CD1 random data random data CD2 random data random data CE0 random data random data CE1 random data random data CE2 random data random data CF0 random data random data
Code:
Needs to look like AA0 random data random data AA1 random data random data AA2 random data random data AB0 random data random data AB1 random data random data AB2 random data random data AC0 random data random data AC1 random data random data AC2 random data random data BA0 random data random data BA1 random data random data BA2 random data random data BB0 random data random data BB1 random data random data BB2 random data random data BC0 random data random data BC1 random data random data BC2 random data random data BD0 random data random data BD1 random data random data BD2 random data random data BE0 random data random data BE1 random data random data BE2 random data random data BF0 random data random data BF1 random data random data BF2 random data random data CA0 random data random data CA1 random data random data CA2 random data random data
Is there a coresponding formula that column numbers can be used instead of column letters?
Columns("O:R").Select
If not what is the best way to get the column letter if I know the column number?
Columns("O:R").Select
If not what is the best way to get the column letter if I know the column number?
I would like to be able to read and interpret and convert the data in columns E, F, & G and create colum H using the interpreted data. I have included a sample table, the "correct" interpretations, the constants and the "method" of interpretation. I would greatly appreciate any help as I have to do this daily and for many processors...Thanks in advance! cliff
CONSTANTS: COLUMN E = SEX SEX: M = M F = F COLUMN F = ED CODE ED CODES: 109 = C 1EE = N 111 = N 11E = H 11S = S 12 or greater and a letter or third number = G (i.e. 14L = G) (i.e. 146 = G) COLUMN G = AFQT 0 = T 1  30 = 4 31  49 = B 50  100 = A COLUMN H = CAT (CORRECT RESULT OF INTERPRETATION) FIRST LETTER OF CORRECT RESULT: comes from COLUMN F (Ed code) SECOND LETTER OF CORRECT RESULT: comes from COLUMN E (Sex) THIRD LETTER OF CORRECT RESULT comes from COLUMN G (AFQT) need to "read" COLUMNS E, F, & G interpret the columns using the constants above and place the results into column H would prefer 1. using columns as constants 2. start macro on cell D2 3. stop macro on first empty cell in Column A
SEX ED CODE AFQT CAT CORRECT
RESULT M 12L 0 GMT M 12L 46 GMA M 12L 97 GMA M 12L 36 GMB M 12L 57 GMA M 11S 66 SMA F 12L 75 GFA M 12L 41 GMB M 12L 54 GMA M 109 49 CMB M 109 33 CMB M 109 87 CMA M 111 38 NMA M 11S 34 SMB M 12L 78 GMA M 12J 61 GMA M 16K 95 GMA F 109 74 CFA M 109 61 CMA M 109 62 CMA M 12L 75 GMA M 109 56 CMA M 11S 34 SMB M 109 70 CMA M 109 43 CMB F 109 54 CFA M 109 84 CMA M 12J 60 GMA M 109 48 CMB M 109 53 CMA M 109 32 CMB M 109 45 CMB M 12J 35 GMB M 109 31 CMB F 11S 55 SFA M 109 32 CMB M 12L 38 GMB M 14L 53 GMA M 12L 0 GMT F 12L 48 GFB F 12L 82 GFA M 12L 46 GMB M 146 78 GMA
CONSTANTS: COLUMN E = SEX SEX: M = M F = F COLUMN F = ED CODE ED CODES: 109 = C 1EE = N 111 = N 11E = H 11S = S 12 or greater and a letter or third number = G (i.e. 14L = G) (i.e. 146 = G) COLUMN G = AFQT 0 = T 1  30 = 4 31  49 = B 50  100 = A COLUMN H = CAT (CORRECT RESULT OF INTERPRETATION) FIRST LETTER OF CORRECT RESULT: comes from COLUMN F (Ed code) SECOND LETTER OF CORRECT RESULT: comes from COLUMN E (Sex) THIRD LETTER OF CORRECT RESULT comes from COLUMN G (AFQT) need to "read" COLUMNS E, F, & G interpret the columns using the constants above and place the results into column H would prefer 1. using columns as constants 2. start macro on cell D2 3. stop macro on first empty cell in Column A
SEX ED CODE AFQT CAT CORRECT
RESULT M 12L 0 GMT M 12L 46 GMA M 12L 97 GMA M 12L 36 GMB M 12L 57 GMA M 11S 66 SMA F 12L 75 GFA M 12L 41 GMB M 12L 54 GMA M 109 49 CMB M 109 33 CMB M 109 87 CMA M 111 38 NMA M 11S 34 SMB M 12L 78 GMA M 12J 61 GMA M 16K 95 GMA F 109 74 CFA M 109 61 CMA M 109 62 CMA M 12L 75 GMA M 109 56 CMA M 11S 34 SMB M 109 70 CMA M 109 43 CMB F 109 54 CFA M 109 84 CMA M 12J 60 GMA M 109 48 CMB M 109 53 CMA M 109 32 CMB M 109 45 CMB M 12J 35 GMB M 109 31 CMB F 11S 55 SFA M 109 32 CMB M 12L 38 GMB M 14L 53 GMA M 12L 0 GMT F 12L 48 GFB F 12L 82 GFA M 12L 46 GMB M 146 78 GMA
I would like to be able to read and interpret and convert the data in columns E, F, & G and create colum H using the interpreted data. I have included a sample table, the "correct" interpretations, the constants and the "method" of interpretation. I would greatly appreciate any help as I have to do this daily and for many processors...Thanks in advance! cliff
CONSTANTS: COLUMN E = SEX SEX: M = M F = F COLUMN F = ED CODE ED CODES: 109 = C 1EE = N 111 = N 11E = H 11S = S 12 or greater and a letter or third number = G (i.e. 14L = G) (i.e. 146 = G) COLUMN G = AFQT 0 = T 1  30 = 4 31  49 = B 50  100 = A COLUMN H = CAT (CORRECT RESULT OF INTERPRETATION) FIRST LETTER OF CORRECT RESULT: comes from COLUMN F (Ed code) SECOND LETTER OF CORRECT RESULT: comes from COLUMN E (Sex) THIRD LETTER OF CORRECT RESULT comes from COLUMN G (AFQT) need to "read" COLUMNS E, F, & G interpret the columns using the constants above and place the results into column H would prefer 1. using columns as constants 2. start macro on cell D2 3. stop macro on first empty cell in Column A
SEX ED CODE AFQT CAT CORRECT
RESULT M 12L 0 GMT M 12L 46 GMA M 12L 97 GMA M 12L 36 GMB M 12L 57 GMA M 11S 66 SMA F 12L 75 GFA M 12L 41 GMB M 12L 54 GMA M 109 49 CMB M 109 33 CMB M 109 87 CMA M 111 38 NMA M 11S 34 SMB M 12L 78 GMA M 12J 61 GMA M 16K 95 GMA F 109 74 CFA M 109 61 CMA M 109 62 CMA M 12L 75 GMA M 109 56 CMA M 11S 34 SMB M 109 70 CMA M 109 43 CMB F 109 54 CFA M 109 84 CMA M 12J 60 GMA M 109 48 CMB M 109 53 CMA M 109 32 CMB M 109 45 CMB M 12J 35 GMB M 109 31 CMB F 11S 55 SFA M 109 32 CMB M 12L 38 GMB M 14L 53 GMA M 12L 0 GMT F 12L 48 GFB F 12L 82 GFA M 12L 46 GMB M 146 78 GMA
CONSTANTS: COLUMN E = SEX SEX: M = M F = F COLUMN F = ED CODE ED CODES: 109 = C 1EE = N 111 = N 11E = H 11S = S 12 or greater and a letter or third number = G (i.e. 14L = G) (i.e. 146 = G) COLUMN G = AFQT 0 = T 1  30 = 4 31  49 = B 50  100 = A COLUMN H = CAT (CORRECT RESULT OF INTERPRETATION) FIRST LETTER OF CORRECT RESULT: comes from COLUMN F (Ed code) SECOND LETTER OF CORRECT RESULT: comes from COLUMN E (Sex) THIRD LETTER OF CORRECT RESULT comes from COLUMN G (AFQT) need to "read" COLUMNS E, F, & G interpret the columns using the constants above and place the results into column H would prefer 1. using columns as constants 2. start macro on cell D2 3. stop macro on first empty cell in Column A
SEX ED CODE AFQT CAT CORRECT
RESULT M 12L 0 GMT M 12L 46 GMA M 12L 97 GMA M 12L 36 GMB M 12L 57 GMA M 11S 66 SMA F 12L 75 GFA M 12L 41 GMB M 12L 54 GMA M 109 49 CMB M 109 33 CMB M 109 87 CMA M 111 38 NMA M 11S 34 SMB M 12L 78 GMA M 12J 61 GMA M 16K 95 GMA F 109 74 CFA M 109 61 CMA M 109 62 CMA M 12L 75 GMA M 109 56 CMA M 11S 34 SMB M 109 70 CMA M 109 43 CMB F 109 54 CFA M 109 84 CMA M 12J 60 GMA M 109 48 CMB M 109 53 CMA M 109 32 CMB M 109 45 CMB M 12J 35 GMB M 109 31 CMB F 11S 55 SFA M 109 32 CMB M 12L 38 GMB M 14L 53 GMA M 12L 0 GMT F 12L 48 GFB F 12L 82 GFA M 12L 46 GMB M 146 78 GMA
Hi
Would this be possible to do as a formula??
In a column, if A1 contains the letter 'a' and A10 contains the letter 'a' also, then fill all the cells in between A1 and A10 with the letter 'a' thus forming a column of 10 cells containing the letter 'a' ???? The formula I guess would need to know that there is an 'a' above and an 'a' below so that it can fill all the cells in between with the letter 'a'.
Basically, there will be many columns with the same letter at the top and bottom, thus i would need the cells in between the top and bottom letter filled with the same letter in the column to form something like the following:
a
abc
abc
abc
abc
ab
ab
ab
a
a
Replies would be much appreciated.
Thanks.
Would this be possible to do as a formula??
In a column, if A1 contains the letter 'a' and A10 contains the letter 'a' also, then fill all the cells in between A1 and A10 with the letter 'a' thus forming a column of 10 cells containing the letter 'a' ???? The formula I guess would need to know that there is an 'a' above and an 'a' below so that it can fill all the cells in between with the letter 'a'.
Basically, there will be many columns with the same letter at the top and bottom, thus i would need the cells in between the top and bottom letter filled with the same letter in the column to form something like the following:
a
abc
abc
abc
abc
ab
ab
ab
a
a
Replies would be much appreciated.
Thanks.
I have a chart with the alpabet in the first column in the next four columns are numbers that correspond to the letters in the 1st column. How can I use MS Word to reference my chart and convert these numbers to letter and vise versa. Also since I have 4 seperate columns of numbers. I need to be able to specify which column of numbers I want to be used to deciper my alphbet. Does anyone have any ideas on the best way to do this? Basically it is making 4 seperate decoder wheels/rings but just automated so it will decipher what is typed in Word (alpha or numeric). Thanks for you help.
Hi,
I need help with either a Conditional format of a cell or a vba code that does the same.
I want a cell to reject an entry if, the following format is not met
letter letter number space number letter letter
i would like it to have variables of,
letter letter number number space number letter letter
I have found a VBA code to match the following,
letter letter number number number number number number letter.
that has worked perfectly but I would like to solve the format highlighted in red above.
Due to the operating size of the file and its linking data to 130 other files i am tryng where possible not to have mulitiple formula's if functions etc across to solve this one riddle.
urgent help most welcome
I need help with either a Conditional format of a cell or a vba code that does the same.
I want a cell to reject an entry if, the following format is not met
letter letter number space number letter letter
i would like it to have variables of,
letter letter number number space number letter letter
I have found a VBA code to match the following,
letter letter number number number number number number letter.
that has worked perfectly but I would like to solve the format highlighted in red above.
Due to the operating size of the file and its linking data to 130 other files i am tryng where possible not to have mulitiple formula's if functions etc across to solve this one riddle.
urgent help most welcome
Is it possible to subtract a number from a column letter?
I'm trying to create a sheet that does some helpful calculations. In order for me to finish it without a macro i need to be able to subtract a number in a cell from a column letter.
Example F5=A, The end i'm trying to achieve is to get a sum range to move left and right in this data set based on that number.
Any ideas?
Thanks All,
Chris
I'm trying to create a sheet that does some helpful calculations. In order for me to finish it without a macro i need to be able to subtract a number in a cell from a column letter.
Example F5=A, The end i'm trying to achieve is to get a sum range to move left and right in this data set based on that number.
Any ideas?
Thanks All,
Chris
Hello All,
I don't even know if this is possible... I'm on Excel 2000.
Here is the image link: http://img84.imageshack.us/my.php?image=examplelw7.jpg
What I want to accomplish:
When the number in Columns B and D are above the "Goal" number in row 8 for each individual column...highlight in blue.
BUT only if the letters in columns C and E are not "U, UJ, NJ, or JN"
Only highlight numbers that are attached to no letter at all or a J.
While we are at it....also if the numbers in Columns B and D are above the "Background" number in row 9 for each individual column...highlight the text in red.
and also if only the letters in columns C and E are not "U, UJ, NJ, or JN"
Only highlight numbers that are attached to no letter at all or a J.
Thank you in advance!!
I don't even know if this is possible... I'm on Excel 2000.
Here is the image link: http://img84.imageshack.us/my.php?image=examplelw7.jpg
What I want to accomplish:
When the number in Columns B and D are above the "Goal" number in row 8 for each individual column...highlight in blue.
BUT only if the letters in columns C and E are not "U, UJ, NJ, or JN"
Only highlight numbers that are attached to no letter at all or a J.
While we are at it....also if the numbers in Columns B and D are above the "Background" number in row 9 for each individual column...highlight the text in red.
and also if only the letters in columns C and E are not "U, UJ, NJ, or JN"
Only highlight numbers that are attached to no letter at all or a J.
Thank you in advance!!