While the WRAPROWS function can be great to unstack a list with an even number of items, we'll need a custom formula to wrap, or unstack uneven lists.
Contents:
How to Unstack an Uneven List with Dynamic Arrays
Formula to Unstack or Wrap an Uneven List:
To unstack or wrap uneven data, you can use the following formula. It may look complicated, but all you have to do is change the range "A2:A25" to match your data range and change the delimiter "Item" to better fit your list.
In this case, "Item" is being used, as it denotes the start of a new item in our example, and everything below that should be unstacked next to that item number.
=LET(
list, A2:A25,
delimiter, "Item",
item_scan, SCAN(0,IF(SEARCH(delimiter,list)=1,list),LAMBDA(x,y,
IF(IFERROR(y,"")="",x,y))),
stack_list, HSTACK(list,item_scan),
filtered_list, FILTER(stack_list,NOT(ISNUMBER(SEARCH(
delimiter,CHOOSECOLS(stack_list,1))))),
items, CHOOSECOLS(filtered_list,2),
unique_items, UNIQUE(items),
attributes, CHOOSECOLS(filtered_list,1),
HSTACK(unique_items,DROP(REDUCE("",unique_items,LAMBDA(x,y,
IFNA(VSTACK(x,TOROW(FILTER(attributes,items=y))),""))),1)))*Note that this formula requires the latest version of Excel
Example: How to Use the Formula
Here we have a list of items with an unequal number of related descriptors below each item. This range is B3:B21.
First, copy and paste the above formula somewhere in your workbook wherever you would like the output to be.
Next, you will need to change two things within this formula in order to fit it to your data.
1. Update the range: Update this range to match up with wherever your stacked list is being held in your workbook.
2. Update the delimiter: In this case we're using "Item" to deliminate between different items and signify when the formula should begin a new row.
For example, if you wanted to wrap the rows on each product number and product numbers were something like "US-116B", if each product contained "US-", then that would make a great choice for the delimiter.
The delimiter doesn't need to be an exact match as the formula will search the cells for partial matches.
Explanation: Formula Breakdown
This formula can be broken up into a few major steps.
Step 1: Create Identifier Column
The first thing we need is a way to identify which part of the list belongs to each item. I'll use the word item here to describe the main item we're separating by, and descriptors as the additional cells underneath them.
This formula will take our list and create an index column of sorts. If the delimiter is found in a cell, it will return that cell. If not, it will return the cell above, which is the previous item number.
=LET(
list,B3:B21,
delimiter, "Item",
SCAN(0,IF(SEARCH(delimiter,list)=1,list),LAMBDA(x,y,IF(IFERROR(y,"")="",x,y))))Step 2: Horizontally Stack Array and Identifier Column
The next step is to horizontally stack our original list with our identifer column created in the first step.
This just lets us use the entire dynamic array in the rest of the steps and simplifies the formula.
=LET(
list,B3:B21,
delimiter, "Item",
item_scan, SCAN(0,IF(SEARCH(delimiter,list)=1,list),LAMBDA(x,y,IF(IFERROR(y,"")="",x,y))),
HSTACK(list,item_scan))Step 3: Remove Extra Item Rows from List
Now that we know what descriptor belongs to each item, we can remove the extra, unneeded rows like the first green row that has "Item 1" "Item 1".
=LET(
list,B3:B21,
delimiter, "Item",
item_scan, SCAN(0,IF(SEARCH(delimiter,list)=1,list),LAMBDA(x,y,IF(IFERROR(y,"")="",x,y))),
stack_list, HSTACK(list,item_scan),
FILTER(stack_list,NOT(ISNUMBER(SEARCH(delimiter,CHOOSECOLS(stack_list,1))))))Step 4: Filter and Transpose Rows
The next part in the function involves a few different parts all working at once inside of a LAMBDA. But basically, for each "Item" found in the list, we're going to filter the array, and transpose it into a row using the TOROW function.
To demonstrate this, let's just use the filter function, and filter our list by "Item 1". We get the output of "Green" and Round". If we do the same filter on "Item 2", we get the output of "Blue", "Square", and "Large".
Since these are both vertically spilled lists, we need to rotate them to spill to the right. This can be done with the TOROW function or the TRANSPOSE function, either works but in this case, we're using TOROW.
Step 5: Vertically Stack Transposed Rows
The TOROW function is used to take our vertically spilled results and spill them to the right instead.
As an example, here is one filtered result. In the actual formula, this is being done using a LAMBDA which iterates through all of the results and does this for each "chunk" of items and descriptors.
Step 6: Append Filtered and Transposed Results
The next step is to vertically stack all of these filtered and transposed results together. Into one list.
We can use the VSTACK function coupled with a LAMBDA (shown in full formula) to iterate through our spilled arrays and stack them together.
The IFNA function is used in conjunction with the "" to leave a space wherever a gap in the wrapped array is. This can be substituted with any other symbol.
IFNA(VSTACK(x,TOROW(FILTER(attributes,items=y))),"")Step 7: Combine Unique Items and Stacked Arrays
The final step is to combine everything together. We're combining a list of each unique item (from our unique_items variable) and the corresponding spilled arrays of each descriptor for every item.
HSTACK(unique_items,DROP(REDUCE("",unique_items,LAMBDA(x,y,
IFNA(VSTACK(x,TOROW(FILTER(attributes,items=y))),""))),1)))This gives us our final formula and output, a dynamically spilled array that unstacks un-even data.
=LET(
list, B3:B21,
delimiter, "Item",
item_scan, SCAN(0,IF(SEARCH(delimiter,list)=1,list),LAMBDA(x,y,
IF(IFERROR(y,"")="",x,y))),
stack_list, HSTACK(list,item_scan),
filtered_list, FILTER(stack_list,NOT(ISNUMBER(SEARCH(
delimiter,CHOOSECOLS(stack_list,1))))),
items, CHOOSECOLS(filtered_list,2),
unique_items, UNIQUE(items),
attributes, CHOOSECOLS(filtered_list,1),
HSTACK(unique_items,DROP(REDUCE("",unique_items,LAMBDA(x,y,
IFNA(VSTACK(x,TOROW(FILTER(attributes,items=y))),""))),1)))