Summary list

You can add a column next to your Score column (assume this is S) with a formula like this in T2:

=IF(S2="","",MAX($T$1:$T1)+1)

When copied down to the bottom of your data this will identify the records with a score and give them a simple sequence.

Then in another sheet you could have this formula to determine the rows where the relevant records occur (put in A2):

=IFERROR(MATCH(ROWS($A$1:$A1),Sheet1!$T:$T,0),"-")

and copy this down until you start to get a hyphen showing. You could then bring across the relevant data (i.e. without blank rows) using a formula like this in B2:

=IF(OR($A2="",$A2="-"),"",INDEX(Sheet1!A:A,$A2))

This can be copied across for Date, Chit, Score etc, but you will need to change the column reference from A:A (it will have changed to B:B, C:C, D:D etc) in the INDEX function to suit the column where the data is located. You can then have another column to identify the date and put it in sequence, and your sort can be based on this.

It's a bit difficult to describe all this without having a workbook to try it out on, so if you want a working solution then please post a sample workbook.

Hope this helps.

Pete

Attached is the sample transferred to a workbook.

In the attached file I've copied the data to a new sheet and shown the workings there - all the bits that I've added are shown with a pale yellow background. The formulae are copied down to the bottom of your data, and you end up with a summary list sorted by the new play date. You need to add to the table in column Z:AA if you have more names in your real file - this shows how many extra days that get added for each name.

All the formula can be used as they are for larger sets of data, except for the one in R4 which is currently this:

=IFERROR(SMALL($Q$4:$Q$16,ROWS($4:4)),"-")

You will need to change the $Q$4:$Q$16 part (coloured above) to suit your data.

Hope this helps.

Pete

Hi Pete,
Thanks for your efforts. I can partially follow your logic and appreciate it very much. But it's not working, probably because I made an error in the source list. Sorry for that.
If a player has no score, it is not expressed with a blank cell, as in the sample, but with 0.00, which is the result of a deduction. That 0.00 means no score. That means there can be a numerical score or 0.00 in that column or blank for those rows that have no entry at all. Hope that doesn't entail many changes.
Also, I would like to append Seq, New_date, sorted, match-row and the Name/Days table to the original sheet with the raw data. But the Summary list should come out in a separate workbook, ready for e-mailing.

You can change the formula in P4 to this:

=IF(OR(O4="",O4=0),"",M4+COUNTIF($M$4:$M4,M4)/100)

then copy down. To get the summary table into a new sheet, insert a blank sheet then highlight columns U, V, W and X in the first sheet, CTRL-X to cut those cells, then select the new sheet, cell A1 and press Enter to paste into the new sheet. The attached file shows this.

To email just that sheet, though, you will need to fix the values in the second sheet, delete the first sheet and then save the file with a different name.

Hope this helps.

Pete

Hello Pete,
Sorry for the delay. We had heavy floods here and damage.
Have studied your sample sheet and it works nicely... in the sample.

But in my actual worksheet the O cell referred to in your Seq formula is the result of =IF(K4="","",I4-SUM(L4,P4)), which causes problems.

If I change the formula to =IF(K4="","",I4-L4) and deduct only what is on the left side of O, your Seq formula works fine. The P (on the right side of O) seems to be the trouble.

Can that be corrected with a change in your Seq formula (or my O formula), or do I have to move the P column to N? And then N to P. Or is it something else?

Have gone back to that worksheet and found one more problem. Or maybe the above is not even the real problem.

Although the display in O is 0.00, the Seq formula produces an entry, which should not happen. That is because the number in O only looks like 0.00, but when you display enough digits after the decimal, the value in O looks like this: 0.0000000000291. This must be a machine error, because the result (product) of the Score in O is only based on whole numbers and there is no legitimate reason why a small fraction with 10 zeroes after the decimal point should appear.

The question now is if there is a way to look only at two decimal digits when checking if the value of O is 0?

Could you post a more recent example workbook - I'm not sure what changes you have made following your post #8

Pete

Attached is a sample illustrating the problem.

You can either change the formula you have in column O to this (eg in O8):

=IF(K8="","",ROUND(I8-SUM(L8,P8),2))

or the formula in O13 to this:

=IF(OR(O13="",ROUND(O13,2)=0),"",M13+COUNTIF($M$13:$M13,M13)/100)

Hope this helps.

Pete

Yes, it helps very much. Both of the above work beautifully, and I'm amazed at the simple elegance of the second formula dealing with the rounding problem. But I'm going to use the first one, because it also cleans up that fraction nonsense that always appears there. Thank you very much. I consider you a true master at these things.

Would you know if these fractions are a normal occurrences in all or most computers, or do I just have a lemon here?