showing resulting data on a another sheet

[rekt57]

My previous two questions have been about ranking numbers and assigning point values to those ranks. But now I want to take some of the data on one sheet and have it show up on another...but there's a catch...

I don't know exactly where I want the data to show up on the second sheet, so I can't just have certain cells on the second sheet point to cells on the first sheet. I have multiple sections on the first sheet that are going to have a different number of entries depending on the test, but I want data from each of the entries from the first sheet to show up on the second sheet in one continuous region that doesn't differentiate from which section the data came from and sorts the data based on one of the columns.

For example: Test #1 has 5 entries in the first section and 12 in the second section. On the second sheet I want 17 rows showing certain data from each entry, that is sorted by the weight of the person. Test #2 has 7 entries in the first section and 15 in the second section. So the second sheet would have 22 rows showing the same data from the new entries, and is also sorted by the weight of the person. And eventually there would be as many as 45 different sections on the first sheet.

Does that make sense?

Thanks for any help.

[Mark@Work]

probably best taken in two stages

first set up a second sheet that brings the test results together, but without trying to arrange them in any particular order.

then set up a third sheet that sorts the data from the second sheet.

[Mark@Work]

The second sheet can be as scrappy as you like, then hide it.

First find the start and end address's of each test in each section.
Probably use MATCH() for this.

Then use INDEX() and/or INDIRECT() to copy the data based on these addresses into contiguous blocks.

Next use RANK() to assign a relative position for each entry.
Next use MATCH(ROW(),RankRange,0) to give a sorted list of the positions.
Finaly use INDEX() to produce a sorted list

Simple...

It can be done, but it is not straight-forward, and the exact implementation will depend on exactly how your data is organised in sheet1.

Mark.

[rekt57]

I just read your second reply, and haven't had a chance to test it. But I'll try that in the next few hours or so. But just in case I didn't explain my idea well enough, I've attached a quick example of what I'm trying to do. I just copied and pasted the values I wanted onto the second sheet, in the layout I want. This was easy enough to do with just two sections on the first page, but with 45 sections it would get a little tedious, but not impossible.

Thanks.