3

I have two columns, the first with a description of an item, the second with the quantity of the item. I'd like to turn this into a list of the described items. I need the solution to work on Windows/Mac/Web, so FILTERXML or VBA won't work. In Google Sheets I can use REPT and SPLIT in an ARRAYFORMULA, but I'm not seeing an Excel equivalent to SPLIT. Any ideas on how to make this happen?

Start with this:

Item Quantity
Apples 4
Oranges 3

End with this:

Items
Apples
Apples
Apples
Apples
Oranges
Oranges
Oranges

My example source data above is a simple 2x2 array {"Apples", 4; "Oranges", 3} but it should be noted that my actual data will be much larger. I am developing a shared template for budget items and don't know in advance the actual number of rows (could be hundreds), the length of the strings, nor the quantities of each item (could also be in the hundreds).

I found I can created an array from one row to using any of the following:

=MAKEARRAY(B1,1,LAMBDA(r,c,A1))

=TEXTSPLIT(LAMBDA(type,count,REPT(type & "|",count))(A1,B1),,"|",TRUE)

=LAMBDA(item,quantity, SUBSTITUTE(item," ","",SEQUENCE(quantity)))(A1,B1)

All the above produce

Items
Apples
Apples
Apples
Apples

But, if I try to adapt any to multiple rows using one of the following, I get a #CALC error that "Nested arrays are not supported":

=MAP(A1:A2,B1:B2, LAMBDA(item,quantity,MAKEARRAY(quantity,1,LAMBDA(r,c,item))))

=BYROW(A1:A2,LAMBDA(rows,TEXTSPLIT( MAP(A1:A2,B1:B2,LAMBDA(type,count,REPT(type & "|",count))),,"|",TRUE)))

=MAP(A1:A2,B1:B2, LAMBDA(item,quantity, SUBSTITUTE(item," ","",SEQUENCE(quantity))))

I just don't know how to append the next row's results to combine the arrays.
 

2 Answers 2

0

In MS Excel, use the TEXTSPLIT function to achieve the same result.

Syntax

TEXTSPLIT function

=TEXTSPLIT(text,col_delimiter,[row_delimiter],[ignore_empty], [match_mode], [pad_with])

LAMBDA Formula: Option 1

With or without VSTACK, this is the simpler of the two approaches.

=TEXTSPLIT(TEXTJOIN(", ", 1,
      BYROW(A2:B4,
         LAMBDA(x, ARRAYTOTEXT(MAKEARRAY(INDEX(x,1,2),1,
            LAMBDA(r,c, INDEX(x, 1, 1))))))),,", ")

LAMBDA Formula: Option 1

#CALC! errors

This formula uses ARRAYTOTEXT to avoid the nested array #CALC! errors. This workaround limits the number of terms that can be processed. You should be able to process about 3,500-4,000 words based on your current word length. If you are processing more than that you could VSTACK formulas to resolve text too long #CALC! errors, or you will need a different approach that addresses both issues.

Example of #CALC! Error

Example of Stacked Formulas

LAMBDA Formula: Option 2

In order to avoid nested array errors without converting arrays to text, you can employ a thunk [wikipedia]   A lambda thunk is "a lambda function with one parameter containing a lambda function with no parameters" [flexyourdata.com] This approach is more resource intensive and the formula is more complicated to understand.

Here is an excellent article about lambda thunks from flexyourdata.com that includes an example formula that I adapted for this answer: What is a thunk in an Excel lambda function?

The formula below also filters out many types of errors that might be generated from the source range, in particular missing values. See the colorized example image at the bottom of the page for more information.

=LET( 
   _range, A:B,
   _rng, FILTER(_range,INDEX(_range,,2)<>""),
   _thunk, LAMBDA(x,LAMBDA(x)),
   _thunks, BYROW(_rng, LAMBDA(a, 
   _thunk(MAKEARRAY(1,INDEX(a,1,2),LAMBDA(r,c,INDEX(a,1,1)))))),
   TOCOL(MAKEARRAY(ROWS(_thunks),MAX(INDEX(_rng,,2)),LAMBDA(r,c,INDEX(INDEX(_thunks,r,1)(),1,c))),3)
   )

Or, wrap it in a lambda and add it as a Named Formula to your template.

# example named formula

=LAMBDA(_range,
 LET( 
   _rng, FILTER(_range,INDEX(_range,,2)<>""),
   _thunk, LAMBDA(x,LAMBDA(x)),
   _thunks, BYROW(_rng, LAMBDA(a, 
   _thunk(MAKEARRAY(1,INDEX(a,1,2),LAMBDA(r,c,INDEX(a,1,1)))))),
   TOCOL(MAKEARRAY(ROWS(_thunks),MAX(INDEX(_rng,,2)),LAMBDA(r,c,INDEX(INDEX(_thunks,r,1)(),1,c))),3)
   ))

LAMBDA Formula: Option 2 LAMBDA Formula: Option 2

Named Formula

Named Formula Example

Colorized Example: Various Input Values Colorized Example: Various Input Values

8
  • 1
    Thanks, wasn't aware of TEXTSPLIT. Unfortunately, I tried it and haven't been able to get anything working, getting errors about nested arrays.
    – jpfieber
    Commented Dec 5, 2022 at 20:56
  • =MAP(A1:A2,B1:B2,LAMBDA(type,count,REPT(type & "|",count))) works to give me an initial array of apples|apples|apples|apples, etc. I then tried to wrap that in another LAMBDA (I'm new to LAMBDA): =BYROW(A1:A2,LAMBDA(rows,TEXTSPLIT( MAP(A1:A2,B1:B2,LAMBDA(type,count,REPT(type & "|",count))),,"|",TRUE))) and I get a #CALC error that "Nested arrays are not supported".
    – jpfieber
    Commented Dec 6, 2022 at 1:06
  • I also tried =MAP(A1:A2,B1:B2,LAMBDA(item,quantity,MAKEARRAY(quantity,1,LAMBDA(r,c,item)))) but had the same result (nested array)
    – jpfieber
    Commented Dec 22, 2022 at 20:12
  • I also tried =MAP(A1:A2,B1:B2,LAMBDA(item,quantity,SUBSTITUTE(item," ","",SEQUENCE(1,quantity)))) but got the same nested array error
    – jpfieber
    Commented Dec 23, 2022 at 18:59
  • 1
    I added some filtering to allow for ranges that are not the same height as the dataset (forgot that you are using this in a template without knowing the exact dimensions in advance). Showed example of using a "named formula" to simplify usage e.g. =namedFormula(range)
    – Blindspots
    Commented Dec 29, 2022 at 23:26
1

Here's my solution without thunks or TEXTSPLIT.

=LAMBDA(in,
    LET(
        dupl, LAMBDA(str, mult, str & T(SEQUENCE(mult))),
        DROP(
            REDUCE("", SEQUENCE(ROWS(in)), 
                LAMBDA(a, b, 
                    VSTACK(a, dupl(INDEX(in, b, 1), INDEX(in, b, 2)))
                )
            ),
            1
        )
    )
)

The LAMBDA function dupl creates a column array containing mult copies of the string str, while the REDUCE function is applied to an array SEQUENCE(ROWS(in)), so that the argument b of the LAMBDA function provides the means to access the rows in the table with strings and multiplicities in. VSTACK stitches everything together, and the DROP function removes the first row that REDUCE always adds (would be nice if Microsoft would streamline this so that this cleanup isn't necessary). Of course the LET function isn't needed, but I thought the code would be a bit more clear by defining dupl explicitly, even if it is only used once in the rest of the code.

4
  • Nice! Your answer would be improved by showing the steps the average user could use to employ the formula. Less experienced users will copy and paste it in and not understand why it doesn't work. Also, including empty row handling would be in line with one of the question's parameters: "I don't know in advance the actual number of rows" Great solution. I encourage you to make your answer more complete, and thank you for teaching me something 👍
    – Blindspots
    Commented Nov 12, 2023 at 7:04
  • Agreed, seems like nice solution, but I'm not sure how to apply it to the example I gave above.
    – jpfieber
    Commented Nov 12, 2023 at 17:28
  • To use my lambda function, one would give it a name in the name manager as explained in Blind Spot's post above (see the part under LAMBDA Formula: Option 2, in particular the screenshot labeled Named Formula). I'll use the name ExpandTable. So if the table with strings and multiplicities is contained in the range A2:B4, use the formula =ExpandTable(A2:B4) in cell D1 to obtain the result depicted in column D of Blind Spot's examples.
    – HGCoder
    Commented Nov 25, 2023 at 4:03
  • To handle empty (or otherwise invalid) rows, it's preferable to filter those out before applying the ExpandTable function. E.g., to recreate D1:D13 in Blind Spots' Colorized Example, enter =ExpandTable(FILTER(A:B,ISNUMBER(B:B)*(A:A<>""))) into cell D1 (of course after defining ExpandTable as a named function). It's not a priori clear what requirements an input row needs to satisfy to be included. E.g., a negative value in the second input column causes an error in ExpandTable, but by filtering those rows out first this can be handled without editing the ExpandTable function.
    – HGCoder
    Commented Nov 25, 2023 at 4:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.