How to apply ArrayFormula on a RegexReplace Query formula so that it autopopulates for all values in Column A?

Question

I'm trying to create a single string for each product that includes different unit prices tied to purchase quantity. For example, a product might have a unit price of £10, but the unit price drops to £8 in multiples of 10; and £6 in multiples of 30.

The string to represent this would be price_breaks_quantity_10_discount_20_quantity_30_discount_40 where 20 and 40 are percentage discounts to be applied.

My source sheet contains four columns, sku, qty, price, and discount. I'm looking to use a combination of query and arrayformula to generate the above string for every unique sku in the source tab.

I have a second sheet called discounts that contains a list of unique SKU's from source, a column with the word "Merge" (required for the import step outside the scope of this question) and a final column "Tags" which is where my current formula is located.

The formula in column A:

={
  unique(source!A2:A),
  transpose(split(rept("MERGE,",countunique(source!A:A)),","))
}

This is grabbing all the SKUs from source and returning a two-column array (column A & B), where the first column is unique SKUs, and the second column is fully populated with the word "WHERE".

Column C contains the following behemoth of a formula:

=REGEXREPLACE(concatenate(QUERY(source!A:E, "SELECT '_quantity_',C,'_discount_', E where A='" & A2 & "' AND C>1 ORDER BY C ASC", 0)), char(34)&"_quantity_"&char(34)&"\(\)"&char(34)&"_discount_"&char(34)&"\(\)","price_break")

This formula performs a Query of the source tab, where the SKU (column A) matches a value in column A of the source tab. Then it does some regex on it to make it look pretty.

The issue with the above formula is that I need to drag it down for every cell SKU. This will inevitably come back to hurt me later, creating errors when importing the CSV if the number of rows of SKUs is more or less than the number of rows with the copied formula.

For example:

• There may be content in columns A and B but no formula copied in column C
• There may be no values in columns A and B, but the formula is copied column C

How do I change the formula so that I can place it into C2 only and have it auto-generate my chosen string for all values in Column A without dragging the formula down?

Alternate solutions that include a formula in the array of formula in A2 would also be acceptable.

Sample Sheet: https://docs.google.com/spreadsheets/d/1vMM6dL8ovzzIGUy99IF1jT0HVay4yCp9soZUh4jq014/edit?usp=sharing

Answer 1

1. The LET function allows storing intermediary calculations
2. TOCOL is used to remove the UNIQUE blank value
3. IFERROR is used to generate the column of "MERGED" strings
4. MAP and BYROW are used to pass values into LAMBA's where the same LAMBDA formula is reused for different sets of values by representing the values with named variables.

=LET(unq,UNIQUE(TOCOL(source!A2:A,1)), ARRAYFORMULA(
 { unq, IFERROR(unq/0, "MERGED"),
   MAP(unq, LAMBDA(u, JOIN(, BYROW(
     QUERY(source!A:E, "SELECT C, E where A='"& u &
           "' AND C>1 ORDER BY C ASC", 0),
     LAMBDA(r, "_quantity_"& INDEX(r,,1)&
            "_discount_"& INDEX(r,,2))))))}))