Sorting excel records by "page"

.

All those records are in a single sheet only.

Does that mean each student's record is located on an individual worksheet by itself ? So if you have 50 student records, there are 50 worksheets in workbook ?

Or does that mean if you have 50 student records, all 50 records are located on a single worksheet ?

OR ... are you speaking of PAGE VIEW .... The VIEW menu tab, PAGE LAYOUT ?


It would be very helpful for you to post a copy of the workbook here for review, minus any confidential information ... or at least a mockup of what the workbook looks like.

dee-u, I see this is your first thread here. Thank you for adding the cross-post link, which is required by our rules. As a new member, please take the time to review our rules. There aren't many, and they are all important.

Not sure if this approximates what you want.

There are two dynamic named ranges:

Row_Headers	='1st sem'!$A$1:INDEX('1st sem'!Q:Q,MATCH("zzzzzz",'1st sem'!Q:Q,1))
Student_Name	='1st sem'!$B$1:INDEX('1st sem'!R:R,MATCH("zzzzz",'1st sem'!Q:Q,1))

Upload contains one lookup range with names listed alphabetically. It is created by an array formula entered in L1 and filled down until you get blanks. (This list can be put anywhere. I put it there for convenience.) If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Formula:

Please Login or Register  to view this content.

There are two helper columns. In K8 and filled down

Formula:

Please Login or Register  to view this content.

In L8 and filled down.

Formula:

Please Login or Register  to view this content.

In N1:W7 just copy the header range across with this formula.

Formula:

Please Login or Register  to view this content.

You'll need to format to match the source. I also formatted numbers so blanks do not show up as 0s. The same holds true for the next formula in N8:W160.

Formula:

Please Login or Register  to view this content.

Sorting excel records by 'page'.xlsx

Does this help?

You are welcome. Thank you for the feedback.

Are the results what you were expecting?

Originally Posted by dee-u

I'm slowly digesting how it works, one thing I noticed is it is kinda slow when used with a hundred records. It may become more problematic with more records.

I've tried to address possible bottle-necks in the formulas.

1. I failed to mention that array formulas are resource hungry. Too many of them can slow a workbook down. Even though 100 names aren't that many array formulas try replacing the formula in L1 with this (still array entered).
   Formula:

   Please Login or Register  to view this content.

2. Replace the formula in L8 filled down with
   Formula:

   Please Login or Register  to view this content.

3. Then replace the formula in N8:W160 with
   Formula:

   Please Login or Register  to view this content.

1 and 2 above may not result in noticeable improvement. However #3 shows @ 17% improvement in speed at my end. I have found that replacing ROWS($8:8) with the ROW()-ROW($8:$8)+1 construction speeds formulas when they exceed @ 45,000 to 50,000 cells. The more cells the more noticeable the difference. I've never understood why. The sample I uploaded is applied to only 1530 cells (3 records). 100 records would apply to 51,000 cells.

You can try applying the above. If the wb still under performs I suspect that a VBA (macro) solution would be far faster and more efficient. Unfortunately I do not know enough VBA to help with that.

Please keep us posted.

PS By the way. Is the resulting layout of my upload acceptable?