(Retrying to send this for the 3rd time; this time I trimmed the bottom)
I've got a need to know which Excel column a value will be placed using an integer number. I came up with this routine below but it seems klunky, and although it works, I was wondering if someone had a better way?
FUNCTION GetColumn(tiNumber as Integer) as String LOCAL liMultiples as Integer, lcColumn as String, liLeftover as Integer liMultiples = INT((tiNumber-1)/26) liLeftover = MOD(tiNumber,26) IF liLeftover <> 0 THEN lcColumn = CHR(liLeftover+64) ELSE lcColumn = "Z" ENDIF IF liMultiples >= 1 THEN lcColumn = CHR(liMultiples + 64) + lcColumn ENDIF && liMultiples >= 1 RETURN lcColumn ENDFUNC && GetColumnLetter(tiNumber as Integer) as String
I'm trying to produce meta-code that will create the Excel outputs from a fixed width input file, and hence my need to know which column I will be entering the information per record.
Here's the resulting output of code that uses this routine to generate the fields to process. The routine above determined the value for the "Column" value:
Num Column Range Length Description 1 A 01-06 6 PROVIDER NUMBER 2 B 07-17 11 MEDICAL RECORD NUMBER (STANDARDIZED) 3 C 18-19 2 ADMIT MONTH (MM) 4 D 20-21 2 ADMIT DATE (DD) 5 E 22-25 4 ADMIT YEAR (CCYY) 6 F 26-27 2 DISCHARGE MONTH (MM) 7 G 28-29 2 DISCHARGE DATE (DD) 8 H 30-33 4 DISCHARGE YEAR (CCYY) 9 I 34-34 1 RECORD TYPE 1 10 J 35-36 2 ADMIT HOUR 11 K 37-37 1 NATURE OF ADMISSION 1=DELIVERY 12 L 38-39 2 SOURCE OF ADMISSION 13 M 40-40 1 ADMIT FROM EMERGENCY ROOM 1=ADMITTED FROM 14 N 41-42 2 BIRTHDATE MONTH (MM) 15 O 43-44 2 BIRTHDATE DAY (DD) 16 P 45-48 4 BIRTHDATE YEAR (CCYY) 17 Q 49-49 1 SEX 1=MALE 18 R 50-50 1 RACE 1=WHITE 19 S 51-51 1 ETHNICITY 1=SPANISH/HISPANIC 20 T 52-52 1 MARTIAL STATUS 1=SINGLE 21 U 53-54 2 AREA OF RESIDENCE COUNTY CODE 22 V 55-59 5 RESIDENCE ZIP CODE XXXXX ZIP CODE 23 W 60-61 2 PRINCIPAL PAYER SOURCE 24 X 62-63 2 SECONDARY PAYER SOURCE 25 Y 64-69 6 CENSUS TRACT 26 Z 70-71 2 DISPOSITION OF PATIENT 27 AA 72-74 3 ALTERNATIVE RATE METHOD ARM CODE 28 AB 75-76 2 SOURCE OF PAYMENT EXPECTED PAYOR FOR MOST
etc.
This is what I use. It can be refined for sure but it does work.
PROCEDURE spnCol2cCol
LPARAMETERS vnCol
LOCAL lnInt1, lnInt2, lnInt3, lcColumn
STORE "" TO lcColumn
lcColumn = ""
DO CASE
CASE m.vnCol > 1378
STORE m.vnCol - 703 TO vnCol
m.lnInt1 = INT(m.vnCol / 676) + 1 && Plus 1 for the 702 component
STORE m.vnCol - (m.lnInt1 - 1) * 676 TO m.vnCol
STORE INT(m.vnCol / 26) + 1 TO lnInt2 STORE mod(m.vnCol, 26) + 1 TO lnInt3
lcColumn = CHR(64 + m.lnInt1) + CHR(64 + m.lnInt2) + CHR(64 + m.lnInt3)
CASE m.vnCol > 702
lnInt1 = 1 && "A" lnInt2 = CEILING((m.vnCol - 702) / 26) lnInt3 = m.vnCol - 702 - (m.lnInt2 - 1) * 26
lcColumn = CHR(64 + m.lnInt1) + CHR(64 + m.lnInt2) + CHR(64 + m.lnInt3)
CASE m.vnCol > 26
lnInt1 = INT((m.vnCol - 1) / 26) lnInt2 = m.vnCol - m.lnInt1 * 26 lcColumn = CHR(64 + m.lnInt1) + CHR(64 + m.lnInt2)
OTHERWISE
lcColumn = CHR(64 + m.vnCol)
ENDCASE
RETURN lcColumn
ENDPROC
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of mbsoftwaresolutions@mbsoftwaresolutions.com Sent: Wednesday, 17 August 2016 6:27 AM To: profoxtech@leafe.com Subject: Better way of determining resulting Excel column from integer?
(Retrying to send this for the 3rd time; this time I trimmed the bottom)
I've got a need to know which Excel column a value will be placed using an integer number. I came up with this routine below but it seems klunky, and although it works, I was wondering if someone had a better way?
FUNCTION GetColumn(tiNumber as Integer) as String LOCAL liMultiples as Integer, lcColumn as String, liLeftover as Integer liMultiples = INT((tiNumber-1)/26) liLeftover = MOD(tiNumber,26) IF liLeftover <> 0 THEN lcColumn = CHR(liLeftover+64) ELSE lcColumn = "Z" ENDIF IF liMultiples >= 1 THEN lcColumn = CHR(liMultiples + 64) + lcColumn ENDIF && liMultiples >= 1 RETURN lcColumn ENDFUNC && GetColumnLetter(tiNumber as Integer) as String
I'm trying to produce meta-code that will create the Excel outputs from a fixed width input file, and hence my need to know which column I will be entering the information per record.
Here's the resulting output of code that uses this routine to generate the fields to process. The routine above determined the value for the "Column" value:
Num Column Range Length Description 1 A 01-06 6 PROVIDER NUMBER 2 B 07-17 11 MEDICAL RECORD NUMBER (STANDARDIZED) 3 C 18-19 2 ADMIT MONTH (MM) 4 D 20-21 2 ADMIT DATE (DD) 5 E 22-25 4 ADMIT YEAR (CCYY) 6 F 26-27 2 DISCHARGE MONTH (MM) 7 G 28-29 2 DISCHARGE DATE (DD) 8 H 30-33 4 DISCHARGE YEAR (CCYY) 9 I 34-34 1 RECORD TYPE 1 10 J 35-36 2 ADMIT HOUR 11 K 37-37 1 NATURE OF ADMISSION
1=DELIVERY 12 L 38-39 2 SOURCE OF ADMISSION 13 M 40-40 1 ADMIT FROM EMERGENCY ROOM 1=ADMITTED FROM 14 N 41-42 2 BIRTHDATE MONTH (MM) 15 O 43-44 2 BIRTHDATE DAY (DD) 16 P 45-48 4 BIRTHDATE YEAR (CCYY) 17 Q 49-49 1 SEX
1=MALE 18 R 50-50 1 RACE
1=WHITE 19 S 51-51 1 ETHNICITY
1=SPANISH/HISPANIC 20 T 52-52 1 MARTIAL STATUS
1=SINGLE 21 U 53-54 2 AREA OF RESIDENCE
COUNTY CODE 22 V 55-59 5 RESIDENCE ZIP CODE
XXXXX ZIP CODE 23 W 60-61 2 PRINCIPAL PAYER SOURCE 24 X 62-63 2 SECONDARY PAYER SOURCE 25 Y 64-69 6 CENSUS TRACT 26 Z 70-71 2 DISPOSITION OF PATIENT 27 AA 72-74 3 ALTERNATIVE RATE METHOD ARM CODE 28 AB 75-76 2 SOURCE OF PAYMENT
EXPECTED PAYOR FOR MOST
etc.
[excessive quoting removed by server]
On 2016-08-16 17:25, Darren wrote:
This is what I use. It can be refined for sure but it does work.
PROCEDURE spnCol2cCol
LPARAMETERS vnCol
LOCAL lnInt1, lnInt2, lnInt3, lcColumn
STORE "" TO lcColumn
lcColumn = ""
DO CASE
CASE m.vnCol > 1378 STORE m.vnCol - 703 TO vnCol m.lnInt1 = INT(m.vnCol / 676) + 1 && Plus 1 for the 702component
STORE m.vnCol - (m.lnInt1 - 1) * 676 TO m.vnCol STORE INT(m.vnCol / 26) + 1 TO lnInt2 STORE mod(m.vnCol, 26) + 1 TO lnInt3 lcColumn = CHR(64 + m.lnInt1) + CHR(64 + m.lnInt2) + CHR(64 +m.lnInt3)
CASE m.vnCol > 702 lnInt1 = 1 && "A" lnInt2 = CEILING((m.vnCol - 702) / 26) lnInt3 = m.vnCol - 702 - (m.lnInt2 - 1) * 26 lcColumn = CHR(64 + m.lnInt1) + CHR(64 + m.lnInt2) + CHR(64 +m.lnInt3)
CASE m.vnCol > 26 lnInt1 = INT((m.vnCol - 1) / 26) lnInt2 = m.vnCol - m.lnInt1 * 26 lcColumn = CHR(64 + m.lnInt1) + CHR(64 + m.lnInt2) OTHERWISE lcColumn = CHR(64 + m.vnCol)ENDCASE
RETURN lcColumn
ENDPROC
Hmmm....thanks, but I think I'll stick with mine or the ADDRESS function in Excel.
FUNCTION GetColumn(tiNumber as Integer) as String LOCAL liMultiples as Integer, lcColumn as String, liLeftover as Integer liMultiples = INT((tiNumber-1)/26) liLeftover = MOD(tiNumber,26) IF liLeftover <> 0 THEN lcColumn = CHR(liLeftover+64) ELSE lcColumn = "Z" ENDIF IF liMultiples >= 1 THEN lcColumn = CHR(liMultiples + 64) + lcColumn ENDIF && liMultiples >= 1 RETURN lcColumn ENDFUNC && GetColumnLetter(tiNumber as Integer) as String
Or, you could ask Excel to do it for you:
http://access-excel.tips/convert-column-letter-to-column-number/
and
http://access-excel.tips/address-convert-column-number-letter/
On Tue, Aug 16, 2016 at 4:26 PM, mbsoftwaresolutions@mbsoftwaresolutions.com wrote:
(Retrying to send this for the 3rd time; this time I trimmed the bottom)
I've got a need to know which Excel column a value will be placed using an integer number. I came up with this routine below but it seems klunky, and although it works, I was wondering if someone had a better way?
FUNCTION GetColumn(tiNumber as Integer) as String LOCAL liMultiples as Integer, lcColumn as String, liLeftover as Integer liMultiples = INT((tiNumber-1)/26) liLeftover = MOD(tiNumber,26) IF liLeftover <> 0 THEN lcColumn = CHR(liLeftover+64) ELSE lcColumn = "Z" ENDIF IF liMultiples >= 1 THEN lcColumn = CHR(liMultiples + 64) + lcColumn ENDIF && liMultiples >= 1 RETURN lcColumn ENDFUNC && GetColumnLetter(tiNumber as Integer) as String
I'm trying to produce meta-code that will create the Excel outputs from a fixed width input file, and hence my need to know which column I will be entering the information per record.
Here's the resulting output of code that uses this routine to generate the fields to process. The routine above determined the value for the "Column" value:
Num Column Range Length Description 1 A 01-06 6 PROVIDER NUMBER 2 B 07-17 11 MEDICAL RECORD NUMBER (STANDARDIZED) 3 C 18-19 2 ADMIT MONTH (MM) 4 D 20-21 2 ADMIT DATE (DD) 5 E 22-25 4 ADMIT YEAR (CCYY) 6 F 26-27 2 DISCHARGE MONTH (MM) 7 G 28-29 2 DISCHARGE DATE (DD) 8 H 30-33 4 DISCHARGE YEAR (CCYY) 9 I 34-34 1 RECORD TYPE 1 10 J 35-36 2 ADMIT HOUR 11 K 37-37 1 NATURE OF ADMISSION 1=DELIVERY 12 L 38-39 2 SOURCE OF ADMISSION 13 M 40-40 1 ADMIT FROM EMERGENCY ROOM 1=ADMITTED FROM 14 N 41-42 2 BIRTHDATE MONTH (MM) 15 O 43-44 2 BIRTHDATE DAY (DD) 16 P 45-48 4 BIRTHDATE YEAR (CCYY) 17 Q 49-49 1 SEX 1=MALE 18 R 50-50 1 RACE 1=WHITE 19 S 51-51 1 ETHNICITY 1=SPANISH/HISPANIC 20 T 52-52 1 MARTIAL STATUS 1=SINGLE 21 U 53-54 2 AREA OF RESIDENCE COUNTY CODE 22 V 55-59 5 RESIDENCE ZIP CODE XXXXX ZIP CODE 23 W 60-61 2 PRINCIPAL PAYER SOURCE 24 X 62-63 2 SECONDARY PAYER SOURCE 25 Y 64-69 6 CENSUS TRACT 26 Z 70-71 2 DISPOSITION OF PATIENT 27 AA 72-74 3 ALTERNATIVE RATE METHOD ARM CODE 28 AB 75-76 2 SOURCE OF PAYMENT EXPECTED PAYOR FOR MOST
etc.
[excessive quoting removed by server]
Look at the Excel Address() Function
Dave
-----Original Message----- From: ProFox [mailto:profox-bounces@leafe.com] On Behalf Of mbsoftwaresolutions@mbsoftwaresolutions.com Sent: 16 August 2016 21:27 To: profox@leafe.com; profoxtech@leafe.com Subject: Better way of determining resulting Excel column from integer?
(Retrying to send this for the 3rd time; this time I trimmed the bottom)
I've got a need to know which Excel column a value will be placed using an integer number. I came up with this routine below but it seems klunky, and although it works, I was wondering if someone had a better way?
FUNCTION GetColumn(tiNumber as Integer) as String LOCAL liMultiples as Integer, lcColumn as String, liLeftover as Integer liMultiples = INT((tiNumber-1)/26) liLeftover = MOD(tiNumber,26) IF liLeftover <> 0 THEN lcColumn = CHR(liLeftover+64) ELSE lcColumn = "Z" ENDIF IF liMultiples >= 1 THEN lcColumn = CHR(liMultiples + 64) + lcColumn ENDIF && liMultiples >= 1 RETURN lcColumn ENDFUNC && GetColumnLetter(tiNumber as Integer) as String
I'm trying to produce meta-code that will create the Excel outputs from a fixed width input file, and hence my need to know which column I will be entering the information per record.
Here's the resulting output of code that uses this routine to generate the fields to process. The routine above determined the value for the "Column" value:
Num Column Range Length Description 1 A 01-06 6 PROVIDER NUMBER 2 B 07-17 11 MEDICAL RECORD NUMBER (STANDARDIZED) 3 C 18-19 2 ADMIT MONTH (MM) 4 D 20-21 2 ADMIT DATE (DD) 5 E 22-25 4 ADMIT YEAR (CCYY) 6 F 26-27 2 DISCHARGE MONTH (MM) 7 G 28-29 2 DISCHARGE DATE (DD) 8 H 30-33 4 DISCHARGE YEAR (CCYY) 9 I 34-34 1 RECORD TYPE 1 10 J 35-36 2 ADMIT HOUR 11 K 37-37 1 NATURE OF ADMISSION 1=DELIVERY 12 L 38-39 2 SOURCE OF ADMISSION 13 M 40-40 1 ADMIT FROM EMERGENCY ROOM 1=ADMITTED FROM 14 N 41-42 2 BIRTHDATE MONTH (MM) 15 O 43-44 2 BIRTHDATE DAY (DD) 16 P 45-48 4 BIRTHDATE YEAR (CCYY) 17 Q 49-49 1 SEX 1=MALE 18 R 50-50 1 RACE 1=WHITE 19 S 51-51 1 ETHNICITY 1=SPANISH/HISPANIC 20 T 52-52 1 MARTIAL STATUS 1=SINGLE 21 U 53-54 2 AREA OF RESIDENCE COUNTY CODE 22 V 55-59 5 RESIDENCE ZIP CODE XXXXX ZIP CODE 23 W 60-61 2 PRINCIPAL PAYER SOURCE 24 X 62-63 2 SECONDARY PAYER SOURCE 25 Y 64-69 6 CENSUS TRACT 26 Z 70-71 2 DISPOSITION OF PATIENT 27 AA 72-74 3 ALTERNATIVE RATE METHOD ARM CODE 28 AB 75-76 2 SOURCE OF PAYMENT EXPECTED PAYOR FOR MOST
etc.
[excessive quoting removed by server]
-
Darren -
Dave Crozier -
mbsoftwaresolutions＠mbsoftwaresolutions.com -
Ted Roche