Looking for help with three complex formulas

[Raffy1944]

Hello from Australia. I am desperately seeking some assistance with a complex formula. (Complex to me at least)

Problem 1: I need the girl's tab (second tab) to bring across limited columns (so only some of the details and only the rows that have 'Girls' in it)

....

Problem 2: Same as above but for boys. I need the boys tab (second tab) to bring across limited columns (so only some of the details and only the rows that have 'Boys' in it)

....

Problem 3: I need the third tab to calculate the total school points from each school and show the school with the highest points to the lowest points (like a ladder)

....

All these formulas need to work in both excel and google sheets. Please email me raff@agostino.net.au I am happy to pay but I will need to be shown how the formulas work.

So three problems to solve working in both google sheet and excel.

....

The files can be found here, once you have it working please email me with the details of where I can see it working.

Here is the link: https://drive.google.com/drive/u/0/folders/1U8ArqHicP8m7WtBNXeR4zk5RG2IzLbh0?fbclid=IwAR2FmnRJEHRYJa...

[#2]

@Raffy1944 

What you want to do can be done quite easily with the FILTER function within SORT. However, because Google Sheets doesn't seem to help with the syntax, I think you'd find it a LOT easier to do in Excel first, and then export to Google. I've created this formula in the attached revision of your posted Excel sheet. It delivers the results you wanted for the boys. All that's needed to adapt it for the girls is to change one word.

=SORT(SORT(FILTER(MASTERSHEET!A2:U9,MASTERSHEET!F2:F9="MALE"),8),5)

=SORT(SORT(FILTER(MASTERSHEET!A2:U9,MASTERSHEET!F2:F9="FEMALE"),8),5)

And I want to draw to your attention the fact that this single formula, in cell A2 in each case, fills ALL of the adjacent rows and columns with the data specified by the selection criteria. This is called "spilling" in that the results spill into adjacent rows/columns.

I also highly recommend eliminating the blank columns from your master sheet (which I've also done). They don't contribute to a clean data table. Use that kind of thing, if you must, with your output results. A master data table should be simply rows and columns with data in each cell (except for those where, for example, somebody didn't have a tennis ball result, or whatever).

All the columns because that was easier than doing it again and again for only the blue. If you only want those, then hide the columns delivered by FILTER that you don't need. Otherwise, you'll be setting yourself up for the possibility of errors by needing to repeat the formula with a smaller subset of columns in that first argument.

"Good on you, mate!" It looks like you've done the right thing here and used fake names for each person.

What you're asking is relatively easy with FILTER and SORT. And google sheets does support these functions as well. As noted above, though, Google doesn't give as much help with syntax when you're writing the formulas.

Here's a YouTube video that should help you with understanding the Dynamic Array functions that I've used here. https://www.youtube.com/watch?v=9I9DtFOVPIg

Feel free to come back here with further questions as you need to.

[#3]

@Raffy1944 , Assuming you have the latest version of Excel (office 365) with the new dynamic array functions enabled. Please see attached..