Forum Discussion

cswed's avatar
cswed
Copper Contributor
Jul 06, 2021
Solved

Autofill alphabet

PC/Win10/MS365 Subscriber I will skip the frustration and disbelief for the moment. I can drag and auto fill a column of numbers with ease. I must be missing something obvious but I have found no te...
excel
  • PeterBartholomew1's avatar
    PeterBartholomew1
    Jul 07, 2021

    cswed 

    The attached has a legacy formula as well as the dynamic array formula

    = MID(ADDRESS(1, ROW()),2,1+(ROW()>26))

    I could also include a Lambda function version but that is only useful with the beta release versions of Excel.  I am not sure whether APL supported functional programming style; FORTRAN IV certainly didn't!

     

PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Jul 07, 2021

cswed 

The attached has a legacy formula as well as the dynamic array formula

= MID(ADDRESS(1, ROW()),2,1+(ROW()>26))

I could also include a Lambda function version but that is only useful with the beta release versions of Excel.  I am not sure whether APL supported functional programming style; FORTRAN IV certainly didn't!

 

cswed's avatar
cswed
Copper Contributor
Jul 07, 2021
Hi Peter, awesome! Thanks for helping an ole dog learn new tricks. I cut my teeth on Fortran at PSU in the Office of Examination Services in 1968. Was in the first class to receive the B.S. in Comp. Sci. (before that it was a sub-degree in Business or Math programs). Went to Xerox and became System Programmer = midnight shifts ;-). Worked my way up to MBA at UR and gained $M for Xerox from in-sourcing APL to become the 2nd largest user of that platform outside of IBM. APL is an interpretive, algorithmic language. Could write a line of APL code that would take pages of C-code to replicate. Of course, few people could pick up another's program and decipher it. Did wind up doing raw HTML code in it prior to it's demise with the advent of mini-computers and PCs. Was a great career, but I have worked even harder since "retirement" but without pay... 😉 Thanks again, +/Chuck
  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Jul 08, 2021

    cswed 

    Hi Chuck, as a former APL developer you might be more tolerant than I am of expressions that need to be 'decoded' as opposed to 'read'.  A few years ago (2015) I realised that I profoundly disagreed with the proponents of a financial modelling standard as to what constitutes 'best practice' in writing spreadsheet solutions.

    [By 'profoundly disagreed' I mean that, for about 25% of their statements, I would replace the word NEVER by ALWAYS]

     

    If you really want to investigate 'new tricks', I could take you through some of the ideas I am exploring.  By way of warning, the starting point is 'forget what you think you know about spreadsheets' and begin from scratch with 365 dynamic arrays.  No direct cell references and avoid relative referencing wherever possible.

     

    To go from column codes to column numbers I might write

    = LET(
   n,    LEN(string),
   k,    SEQUENCE(n),
   chr,  MID(string, k, 1),
   code, CODE(chr),
   v,    BITAND(code,63),
   p,    26^(n-k),
   SUM(v*p) )

    even though

    = COLUMN(INDIRECT(string&1))

    would be shorter.  The former will accept "XFE" as a string to convert, whereas the latter throws a #REF! error.

Resources