Tag: LAMBDA

excel-lambda-ARRAYUNION: Join Two Ranges Or Arrays Of Data Together Using This LAMBDA Function

Posted on by OP 
=LAMBDA(
    array_a,
    array_b,
    keep_duplicates,
    LET(
        arr,
        MAKEARRAY(
            ROWS(array_a)+ROWS(array_b),
            1,
            LAMBDA(r,
                   c,
                   IF(r<=ROWS(array_a),
                      INDEX(array_a,r),
                      INDEX(array_b,r-ROWS(array_a))
					  )
				   )
			     ),
        IF(keep_duplicates,arr,UNIQUE(arr))
		)
		)

You may have found yourself wanting to join two arrays together in Excel. This function will quickly append and optionally deduplicate two single-column arrays or ranges of data. This is done with the LAMBDA shown above, which you can define in Excel’s Name Manager with the name ARRAYUNION.

It accepts 3 arguments:

  1. array_a – a single-column array or range
  2. array_b – a single-column array or range
  3. keep_duplicates – a TRUE/FALSE value indicating whether to keep or remove duplicate values after appending array_b to array_a

It couldn’t be simpler to use:

 

The approach uses MAKEARRAY to create an array that has ROWS(array_a)+ROWS(array_b) rows and one column.

The elements of the array to be made are provided by the LAMBDA shown starting on line 10.

The first two arguments of that LAMBDA are r and c. Within the MAKEARRAY function, these represent row and column positions in the array being made.

Here we are saying if the row of the new array is less than or equal to the number of rows in array_a, then populate that row with the value from the same position in array_a (as retrieved by the INDEX function).

If the row of the new array is greater than the number of rows in array_a, then we will populate it with a value from array_b.

Let ‘arr’ be the array created by MAKEARRAY as described above. Then, if keep_duplicates is TRUE, return ‘arr’ unmodified. Otherwise, apply the UNIQUE function to ‘arr’ and return the result of that function call. This has the effect of removing duplicates from the joined arrays. This can be particularly useful if you have two lists of people on different sheets and you think there might be some people in both lists.

Or perhaps you have two reports with lists of products your company sells and you want to quickly create a combined report or check that the right products are included.

excel-lambda-SQLVALUES: Format A Row Of Data For SQL INSERT

Posted on by OP

The gist for this lambda function can be found here.

If you sometimes need to quickly put some Excel data into a SQL table or use the data in a CTE, you may have found yourself doing something like this:

Here’s a LAMBDA I’ve called SQLVALUES:

=LAMBDA(t,LET(d,IFS(ISTEXT(t),”‘”&SUBSTITUTE(t,”‘”,”””)&”‘”,ISBLANK(t),”NULL”,LEFT(MAP(t,LAMBDA(x,CELL(“format”,x))),1)=”D”,TEXT(t,”‘YYYY-MM-DD HH:mm:ss'”),TRUE,t),”(“&TEXTJOIN(“,”,FALSE,d)&”)”))

This will:

  1. Wrap the tuple in parentheses
  2. Wrap text and dates in single-quotes
  3. Replace embedded single-quotes with escaped single-quotes
  4. Separate the columns with commas
  5. Format date-formatted cells as YYYY-MM-DD HH:mm:ss

If we’re inserting multiple values and our SQL database supports a list of tuples, we can also do this:

=LET(arr,A2:C6,BYROW(arr,SQLVALUES)&IF(LASTROW(arr),”;”,”,”))

Which is saying “Apply the SQLVALUES lambda to each row in arr. If the row of arr is the last row, put a semi-colon after it. Otherwise, put a comma after the row”.

LASTROW just takes an array and returns an array of TRUE/FALSE the same size as array. Here’s the LAMBDA for LASTROW:

=LAMBDA(d,ROW(d)=(ROWS(d)+MIN(ROW(d))-1))

You can now paste the data from the spreadsheet directly into your SQL editor.

I’m sure SQLVALUES is not perfect. I suspect there are edge cases it won’t cover, but hopefully it demonstrates a way to shortcut a task using array formulas and LAMBDA.

Do you have any suggestions for improvement to the SQLVALUES LAMBDA?

 

excel-lambda-CHARACTERS: Split An Alphanumeric String Into An Array Of Characters

Posted on by OP

=LAMBDA(rng,vertical,LET(chars,MID(rng,SEQUENCE(LEN(rng)),1),IF(vertical,chars,TRANSPOSE(chars))))

This LAMBDA function takes two arguments:

  1. rng – a cell containing a text string
  2. vertical – TRUE/FALSE. If TRUE, the LAMBDA will return a vertical array of the characters in rng. If FALSE, the LAMBDA will return a horizontal array of the characters in rng

In my file, I have named this LAMBDA “CHARACTERS”. You can of course call it whatever you want.

 

So what?

This is useful, because it simplifies things when we want to extract all the numbers or text from a character string.

 

To get all the numbers in a horizontal array:

=LET(c,CHARACTERS($A$1,FALSE),nums,INT(c),FILTER(nums,NOT(ISERR(nums))))

To join the numbers from the array as a single integer:

=INT(CONCAT(LET(c,CHARACTERS($A$1,FALSE),nums,INT(c),FILTER(nums,NOT(ISERR(nums))))))

To get all the non-numbers in a horizontal array:

=LET(c,CHARACTERS($A$1,FALSE),nums,INT(c),FILTER(c,ISERR(nums)))

To get all the non-numbers as a single string:

=CONCAT(LET(c,CHARACTERS($A$1,FALSE),nums,INT(c),FILTER(c,ISERR(nums))))

Of course there are many other uses for this array of characters. We can test for a specific character in the array, or filter out specific sets of characters, or use it in a MAKEARRAY.

The CHARACTERS LAMBDA works principally because of this:

=MID(A1,SEQUENCE(LEN(A1),1)

This is simple but very powerful. SEQUENCE(LEN(A1) gives us a sequence of integers from 1 to the length of the string in A1. By passing this as the second parameter of MID, which is the “start”, and passing 1 as the third parameter, which says “get one character”, we are essentially applying the MID function as many times as there are numbers returned by SEQUENCE, and each of those times it’s applied, it is using one of the numbers in SEQUENCE. So, it’s the same as this:

The rest of the LAMBDA function is just deciding whether to return that array vertically or horizontally, by using the TRANSPOSE function.

In case it’s of use, here is a LAMBDA to get the numbers (you will also need the CHARACTERS LAMBDA defined above). I have called this GETNUMBERS.

=LAMBDA(rng,vertical,LET(c,CHARACTERS(rng,vertical),nums,INT(c),FILTER(nums,NOT(ISERR(nums)))))

And here’s one to get non-numbers, which I’ve called GETNONNUMBERS

=LAMBDA(rng,vertical,LET(c,CHARACTERS(rng,vertical),nums,INT(c),FILTER(c,ISERR(nums))))

excel-lambda-NONEMPTYROWS: Filter Out Rows With Empty Cells

Posted on by OP

If you want to quickly get all rows which don’t have any blanks in any columns, you can combine FILTER, BYROW and AND, like this:

=FILTER(range,BYROW(range,LAMBDA(r,AND(r<>””))))

Here, I’ve defined a LAMBDA function, which is really just a way of applying some logic (in the second parameter) to some data (in the first parameter). I have “r” as the name for my data.

By passing that LAMBDA as the second parameter of BYROW, I’m telling Excel that “r” represents a row of “range” and that I want the function AND(r <> “”) to be applied to that row.

That AND function will check if each column in the row is not empty. If they’re all not empty, it will be TRUE. If any column in that row is empty, it will be FALSE. So, BYROW does this for each row in the range and returns a 1-column array of TRUE/FALSE that has the same number of rows as “range”. I then use that TRUE/FALSE array as the “include” parameter of the FILTER function.

So, for the data in “range”, check if the cells in each row are all non-empty. If they are, then include the row. Otherwise, exclude it.

Where I’ve written “range” above, you would need to select exactly the same cells in both places. So, it may be easier to use LET to only have to select those cells once. Like this:

=LET(rng,A2:E12,FILTER(rng,BYROW(rng,LAMBDA(r,AND(r<>””)))))

LET allows you to give names to functions or ranges so you can re-use the name in several places in a formula instead of having to enter that function or range multiple times.

Further to all this, I think I’ll probably use this kind of thing again, so I can wrap the entire function in a LAMBDA function of its own and define it in the Name Manager. I’ve called it NONEMPTYROWS.

This is the LAMBDA called NONEMPTYROWS:

=LAMBDA(rng,FILTER(rng,BYROW(rng,LAMBDA(r,AND(r<>””)))))

If you wanted to switch this around to return only those rows that have a blank in any column, you would replace the AND(r<>””) with OR(r=””).