CODEWITHSUNDEEP
excel
akraines
akraines

Reputation: 2045

How to get nth non-blank column in Excel

I want to use an excel spreadsheet to lottery off a set of items amongst multiple contenders. I made a sheet with a column for the item and one for each contender. On each row, the first column has the items name and each user puts a "y" in his cell if he has interest in that item:

Item -A-B-C-D-E-F-G-
Foo  - -Y- -Y- -Y- -
Bar  -Y-Y-Y- - - - -
Roll -Y- -Y-Y- - -Y-

I then added a column which counted the amount of contenders with a formula like:

COUNTA(Table2[@[A]:[G]])

And another column to choose a random number amongst the contenders with a formula like:

=CEILING.MATH( RAND()*[Amount of contenders cell])

The issue is: Once I have the index of the contender who won, how can I display the name of the winning contender based on the index of which Y was selected?

Upvotes: 1

Views: 385

Answers (1)

akraines
akraines

Reputation: 2045

I solved this by making another table right next to the first table with a column for each contender. The value of each cell = how many "Y" (or non-blank cells) occur upto and including the current contender. The formula for each cell is:

The first column simply is equal to if the corresponding cell is blank: 0 otherwise: 1

=IF(ISBLANK(Table2[@A]),0,1)

And the subsequent columns equal to the previous column + the formula above:

=IF(ISBLANK(Table2[@B]),0,1)+K2

I now have a soon-to-be-hidden table corresponding my original table which looks like this;

A-B-C-D-E-F-G
0-1-1-2-2-3-3
1-2-3-3-3-3-3
1-1-2-3-3-3-4

Once I have this table, I can successfully use match and index to get the name of the 3rd contender for the given row. First I get the absolute index of the n-th non-blank column (the last parameter: 0 indicates that I want an exact match, which gives me the first occurrence of "n" in my helper-table for that row:

=MATCH(Table2[@[Snapshot of lottery results]],K2:P2,0)

And then using that index, I can get the name of the winning contestant (where B1:H1 contains the column names and the cell at column J:current row contains the decision index:

=INDEX($B$1:$H$1,1,IF(J2>0,J2,""))

Upvotes: 1

Related Questions