I have a list of data where the cells are rich text values with a link.

Data:

My criteria:

1. extract Fruits with specified Rank (e.g. 1 or 2)
2. transpose the output and split it into rows with a specified number of elements (max) in each row
3. keep the rich text value (link URL) in the output

Example output:

Rank: 1, max in each row: 2

What I have tried:

A link to the sample spreadsheet

1. FILTER

The result keeps the link URL in the rich text value

=FILTER(A:A,B:B=1) in D2

But I do not know how to convert it into a 2d array nor LIMIT or OFFSET the result.

2. QUERY

I can create the desired 2d array with multiple QUERY

=IFNA(TRANSPOSE(QUERY(A:B,CONCATENATE("SELECT A WHERE B=",1," LIMIT ",2," OFFSET ",(ROWS(A$1:A1)-1)*2),0)),) in F2 and applied to F2:F3

But the result does not keep the link URL in the rich text value

3. With helper columns

First, FILTER:

=FILTER(A:A,B:B=1) in I2

Second, convert into 2d array with OFFSET:

=OFFSET($I$2,COLUMNS($I2:I2)-1 (ROWS($2:2)-1)*2,0) in K2 and applied to K2:L3

This outputs what I want but I do not want to use a helper column.

My question:

Is it able and how to achieve with formula and without helper columns?

CodePudding user response：

Here's how you can do that:

=ArrayFormula(LAMBDA(rank,max,IFNA(VLOOKUP(SEQUENCE(ROUNDUP(
COUNTIF(B2:B,rank)/max),max),{SEQUENCE(COUNTIF(B2:B,rank)),
FILTER(A2:A,B2:B=rank)},2,0)))(1,2))

But I suggest creating a Named Function _WRAPROWS from Data > Named functions > Create function and using it like this:

=_WRAPROWS(FILTER(A2:A,B2:B=1),2,)

Where _WRAPROWS is defined as:

=ARRAYFORMULA(IFNA(VLOOKUP(SEQUENCE(CEILING(
ROWS(FLATTEN(vector))/wrap_count),wrap_count),
{SEQUENCE(ROWS(FLATTEN(vector))),FLATTEN(vector)},2,0),pad_with))

Arguments:

• vector - The array to wrap.
• wrap_count - The maximum number of values for each row.
• pad_with - The value with which to pad.

See WRAPROWS in Google Sheets for more information.