Forum Discussion

PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Nov 29, 2022

Patrick2788 

Hi Patrick

I have played with the challenge and achieved a different solution by with no claim of having made it better!

The idea of creating a doubled-up scale appeared to be a good idea, so I created 

altStandard
= LET(
    scale,     {"A","B","C","D","E","F","G"}&{"";"#"},
    deselect,  IF((scale<>"E#")*(scale<>"B#"),scale,NA()),
    notes,     TOROW(deselect,2,TRUE),
    HSTACK(notes,notes)
  )

The formula replaces B# and E# by errors and then uses TOROW to reorder and filter out the errors.  Then I wrote a Lambda function to select values by fret for a single string choice

 Fretλ
= TAKE(DROP(altStandard, , XMATCH(string,altStandard)),,12)

 I used the REDUCE/VSTACK combo to generate the full output array

WorksheetFormula
= REDUCE(fretLabels, Strings, Buildλ)

Buildλ
= LAMBDA(tuning,string,
     VSTACK(tuning, 
        IF(string <> "", Fretλ(string), EXPAND("", 1, 12, ""))
     )
  )

For me, it provided an excuse to use some of the new array shaping formulas as an integral part of the calculation.  That is not yet part of a process that comes naturally!

Patrick2788's avatar
Patrick2788
Silver Contributor
Nov 29, 2022

PeterBartholomew1 

I do like the use of TOCOL/TOROW to 'filter' data without using FILTER. Helpful to remove 0s and errors and probably quicker than FILTER.

 

You seem to be getting really good at creating a function and then calling the function within another LAMBDA as is the case with Buildλ inside of REDUCE.  I've gotten REDUCE to spill here and there by accident, but this is a method that I'm going to study.

 

TAKE-DROP is a clever swap for INDEX and probably faster, too.  It's interesting that TAKE/DROP are accepted within legacy menus like Data Validation and are a better option than OFFSET/COUNT(A) for dynamic ranges.

  • mtarler's avatar
    mtarler
    Silver Contributor
    Nov 29, 2022

    Patrick2788 as long as you are doing speed tests I'm curious how a string based solution fares:

    Std = "E,F,F#,G,G#,A,A#,B,C,C#,D,D#,"
Scale = Std & Std
=DROP( TEXTSPLIT( REDUCE( "", Strings, 
       LAMBDA( p, s,
          p&IF(s<>"",LEFT(TEXTAFTER(Scale,s&","),28),REPT(",",11))&";")
    ),",",";"),-1)
    • Patrick2788's avatar
      Patrick2788
      Silver Contributor
      Nov 30, 2022
      The average calc time was .00081 for this version.

      Re: the previous version
      I did some testing with MATCH/XMATCH with the lookup vector being about 95,000 unique elements. At first, I did a simple match of one number that was in the first position in the array - no difference in calc times. Next, I picked the last element and again no real difference in calc times. Finally, I pulled 100 items from the lookup array to be matched and spilled XMATCH/MATCH and again no real difference in calc times.
      • mtarler's avatar
        mtarler
        Silver Contributor
        Nov 30, 2022

        Patrick2788OK let's see if we can trim some time by removing LAMBDA:

        using textjoin/textsplit:

        =TEXTSPLIT(
      TEXTJOIN(";",FALSE,
         IF(Strings<>"",
             MID(Scale,SEARCH(Strings&",",Scale)+LEN(Strings&","),28),
             REPT(",",11))),
      ",",";")

        going 'old school' except SEQUENCE:

        =TRIM(IF(Strings<>"",
                  MID(
                     SUBSTITUTE(MID(Scale,SEARCH(Strings&",",Scale)+LEN(Strings&","),28),",",REPT(" ",28)),
                     SEQUENCE(,12,,28),28),
                  MID("",SEQUENCE(,12),1)))
    • PeterBartholomew1's avatar
      PeterBartholomew1
      Silver Contributor
      Nov 30, 2022

      mtarler 

      Matt, I had thought of using text strings rather than arrays and had gone the array route, but I must admit the text can be simpler.  What I have now tried, based upon your solution, is to use fixed length rather than comma separated sub-strings (I used 'underscore' in place of a 'space' simply to make the character visible).  

      The text string that represents the tuning of a single guitar string is given by the Lambda function

      Tuneλ(s)
= IF(s <> "", 
     LEFT(TEXTAFTER(Scale, s & "_"), 24), 
     REPT(" ", 24)
  )

      Note: the Lambda call and its parameters are inserted by the AFE

      Whereas the Lambda helper functions are all crippled to prevent nested arrays in the interest of maintaining compatibility with some mythical past, the MID function suffers from no such inhibition, and will use lifting to generate arrays of arrays, no questions asked.

       

      As part of my drive to turn every formula to a Lambda function, I also used

      SplitByFretλ(StringTuning)
= MID(StringTuning, SEQUENCE(1,12,1,2), 2)

"// giving the worksheet formula //"

= SplitByFretλ( MAP(Strings, Tuneλ) )

       

Resources