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.

Resources