Excel row numbering based a condition?

Question

I have a spreadsheet which the user would enter a list of data points to be eventually plotted to a graph. There are 100 rows with the left most column containing a tick box for each row. i want the user to be able to tick 10 of the 100 boxes and have this data plotted to a graph in order from top of the list to bottom.

The tick boxes are linked to a column which display true of false. Ultimately i will have a column with 90 'FALSE' and 10 'TRUE' rows. The rows that are true could be in different places every time the sheet is used, including the first row containing true.

I want the data from the 10 true rows to be copied to a 10 row table (on the same sheet) and be plotted to a graph.

I had thought to number the true rows 1-10 from the top to the bottom which would make copying the data to the 10 row table simple.

However, i cannot get the numbering of these 'true' rows to work with a different start row each time. The closest i've managed was using the COUNTIF formula.

Can anyone help?

Answer 1

Whilst there are more elegant solutions, the formulas are somewhat more complex, but this will give you a very simple solution that is easy to implement.

Let's assume that your TRUE/FALSE values (and I'm assuming these translate to 1s and 0s in your cells) are in the range D3:D103, and lets further assume that the text associated with each of those potential 100 TRUE/FALSES is in the range E3:E103.

In cell C3 put the following formula and just copy down to C103:
=SUM(D$3:D3)

This will give you something like 0,0,0,1,1,1,1,1,1,2,2,2,2,2,3,3,3,3,3 etc going downwards, depending on where the 1s are in Column D.

(If you don't have 1s and 0s, and you actually have the TRUE/FALSE entries straight from the Form Control, just use =SUMPRODUCT(--(D$3:D3)) instead)

Now in say cells J6:J15, put the values 1,2,3,4,5,6,7,8,9,10, and then in cell K6 put the following formula and copy down to K15:
=VLOOKUP(J6,$C$3:$E$103,3,0)

What you will now see in column K is the associated text with each of the 10 options that have been ticked, in the order that they appear in the list.

If you wanted to know what number in the list they actually were, then in say cell L6, out the following formula and copy down to L15:
=MATCH(J6,$C$3:$C$103,0)

If you wanted to know the actual row that each entry appeared on, then in say cell M6, out the following formula and copy down to M15:
=MATCH(J6,$C$3:$C$103,0)+ROW($C$3)-1

Answer 2

Ok, i've managed to get it working, its a bit convoluted but it does the job nicely. Had to increase my range to 150 rows to cover the data that would be entered.

I had all of my true/falses in column I, so in column J i put the row numbers of only the rows containing true...

=IF(I7=FALSE, "", ROW())

Then in column K i used the RANK function to assign the 1-10 numbers i wanted. The row numbering will only ever be increasing going down the list so the rank function in descending order works fine.

=IF(J7="", "", RANK(J7,$J$7:$J$156, 1))

Then i was able to use the VLOOKUP function to match those 1-10 numbers to the data i wanted to extract to populate the 10 row table i have elsewhere on the sheet for plotting to a graph.

I'm sure there are many better ways to do this but unfortunately i'm on a deadline to have this completed so i can revisit this later to see if i can improve it.

Many thanks for the posts as it gave me others way to try which ultimately led to getting it working.