The attached file shows how you can do this with 4 basic formulae. Put this in G2:
then copy it down to beyond the bottom of your data (until you get hyphens). It is important to put zero in cell G1. This formula gives you a cumulative sum of the number of chances.
Then in cell I2 you can have this formula:
which determines the row for the next record to be fetched across to the new table. This formula in J2:
gives you the sample number for each record, and this formula in K2:
brings the data from column A for the first record. This formula is then copied across to N2 to fetch the data from the other columns, and then the formulae in I2:N2 can be copied down as far as yo think you need them (i.e. until you get hyphens in column I).
Hope this helps.
Pete
Bookmarks