Google Sheets consecutive rows when multiple checkboxes are ticked-CodePudding

I am looking for help with this sheet. I have some item names and images next to those. They belong together.

Whenever I tick the first checkbox in the item sheet, I want the item name and the image to be displayed in Sheet 2. I have managed to do that with the formula in A1 for the image

=Iferror(VLOOKUP(B1,Items!B4:F15,2,FALSE),"")

and for the item name formula in B1 I used

=iferror(QUERY(Items!B4:F15,"select B where D=True"),"") .

My problem is:

When I tick the first checkbox of any item, the item gets normally displayed in Sheet2 with the belonging image. When I tick the second checkbox for that same item (x2), I want the same item to be displayed right again in the second row in Sheet2 with the belonging image again. If I tick the third checkbox, I want it to be displayed three times basically below each other with the belonging image.

Is that possible without scripts?

https://docs.google.com/spreadsheets/d/1tjyBIMDWbM4AxuQby4vmEUeFgqLbxN1EPk1pGqEX_64/edit?usp=sharing

CodePudding user response：

You can try

=sort(filter({
ifna(filter(Items!B4:C,Items!D4:D),{"",""});
ifna(filter(Items!B4:C,Items!E4:E),{"",""});
ifna(filter(Items!B4:C,Items!F4:F),{"",""})},query({
ifna(filter(Items!B4:C,Items!D4:D),{"",""});
ifna(filter(Items!B4:C,Items!E4:E),{"",""});
ifna(filter(Items!B4:C,Items!F4:F),{"",""})},"select Col1")<>""))

UPDATE: Better approach

=sortn({
ifna(filter(Items!A4:B,Items!D4:D),if({1,1},char(999)));
ifna(filter(Items!A4:B,Items!E4:E),if({1,1},char(999)));
ifna(filter(Items!A4:B,Items!F4:F),if({1,1},char(999)))},
countif(Items!D4:F,true),,2,1)

Essentially we are filtering the data three times (one for each checkbox), stacking all the results together and sorting them in ascending order. The if({1,1},char(999)) is used as padding in case there's no match (i.e. no checkbox is ticked) to prevent ARRAY_LITERAL. The idea with char(999) is to have a character that goes to the bottom of the list when the range is sorted so we can exclude it with sortn(). I don't know how Google Sheets sorts when special characters are involved (if anyone does please let me know) but char(999) seems to get the job done.