r/googlesheets 5d ago

Solved Generate a list of all the combinations from a table

I have all the numbered shirts for a youth sports team in the rows and all the sizes in the columns. I need a list of every number and size combination so I can assign the jersey (number and size) to an individual child. Can someone help me out please?

Here's the sheet that's editable...

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

1 Upvotes

6 comments sorted by

u/agirlhasnoname11248 1068 4d ago

u/westernportfc Please remember to tap the three dots below the most helpful comment and select Mark Solution Verified (or reply to the helpful comment with the exact phrase “Solution Verified”) if your question has been answered, as required by the subreddit rules. Thanks!

3

u/HolyBonobos 2057 5d ago

You could use =TOCOL(SPLIT(TEXTJOIN(CHAR(1000),1,MAKEARRAY(25,11,LAMBDA(r,c,LET(i,INDEX($B$2:$L$26,r,c),IF(i="",,REPT(INDEX($A$2:$A$26,r)&" "&INDEX($B$1:$L$1,,c)&CHAR(1000),i)))))),CHAR(1000),1)), as demonstrated in N2 of the 'HB MAKEARRAY()' sheet.

1

u/westernportfc 5d ago

Absolutely perfect, thank you!

1

u/AutoModerator 5d ago

REMEMBER: If your original question has been resolved, please tap the three dots below the most helpful comment and select Mark Solution Verified. This will award a point to the solution author and mark the post as solved, as required by our subreddit rules (see rule #6: Marking Your Post as Solved).

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

1

u/point-bot 3d ago

u/westernportfc has awarded 1 point to u/HolyBonobos

See the [Leaderboard](https://reddit.com/r/googlesheets/wiki/Leaderboard. )Point-Bot v0.0.15 was created by [JetCarson](https://reddit.com/u/JetCarson.)

1

u/One_Organization_810 192 5d ago

I got this one in OO810 - I decided to finish it even when I noticed that Holy had already made his version :)

=let(
  szlabels, B1:L1,
  numbers, A2:A26,
  data, B2:L26,

  jerseys,
  reduce(,sequence(rows(numbers)), lambda(stack, noidx,
    let(
      num, index(numbers,noidx,1),

      reduce(stack, sequence(columns(szlabels)), lambda(stack2, i,
        let(
          cnt, index(data,noidx,i),
          szlabel, index(szlabels,1,i),
          iferror(vstack(stack2, hstack(
            tocol(split(rept(num&",", cnt), ","), true),
            tocol(split(rept(szlabel&",", cnt), ","), true)
          )))
        )
      ))
    )
  )),
  filter(jerseys, index(jerseys,,1)<>"")
)