Generating a simple table from variable locations and number of values

[samdan87153]

Hi, my friend helps to run a fairly complicated Super Bowl Squares payout scheme for her boss. Tallying up who won and how much they won takes multiple hours each set of squares and they have 3 sets of squares. I thought it would be fun to use this as an opportunity to stretch my Excel legs to automate it, and I'm about 99% there but I am completely stumped on one last component.

One of the win conditions is that all of the squares AROUND the winner at half time and at the end of the game also get a share of the prize pool (I've dubbed them "Splash Winners"). So, depending on where the winning square is, there are 3, 5, or 8 "splash winners" for a winning square at a corner, edge, or in the middle area, respectively. To finish tallying up all of the winners and their combined winnings, I need to generate a table of the splash winners and how much their splash is worth. I've attached a clip of what the sheet looks like currently and what I want to generate. Because of the rules of the prize pool, the splash winners are in variable rows and there are between 6 and 16 "splash winners" to tabulate. The 0's all get whited out with conditional formatting, I just use them as the empty/false values in the sheet.

Can anyone help me with a formula for generating a table like this? Even if it has blank spaces in the list, as long as the names have the right win values attached I already have the formulas written to remove blank values, remove duplicates, and sum winnings.

If you want to see everything that goes into this, here is a Link to the full spreadsheet.

 

[Fluff]

Hi & welcome to MrExcel.
How about

=LET(t,TOCOL(H4:O53),f,FILTER(P4:Q53,P4:P53<>0),HSTACK(FILTER(t,t<>0),TOCOL(IF(SEQUENCE(,MAX(TAKE(f,,1)))<=TAKE(f,,1),DROP(f,,1),1/0),2)))

 

[samdan87153]

I cannot even begin to understand what you just did, but it does exactly what I want it to so thank you so much!

 

[Fluff]

You're welcome & thanks for the feedback.