The following is not complicated - it is just that there is quite a bit of descriptive text.
Set up a 'test' version of the workbook as follows:
1. Create four worksheets with the names Payments, Helper1, Helper2 and MM Data. The only space in the worksheet names is in the last one (MM Data).
Helper1 and Helper2 can later be hidden.
2. In worksheet 'Payments', enter data as follows:
A1: Student
A2: Fred
A3: Bill
A4: Bert
A5: Harry
B1: Amount
B2: Enter the formula =SUM(C2:L2) and copy this down to the 3 cells below.
C1: T1
D2: T2
Continue this with T3, T4 etc. up to T10 in column L.
Enter some values in cells C2, D2, D3, E3, F2, F4, F5 and G2 (keep it simple with values such as 5, 10, 20 and 30).
Save the workbook.
3. In worksheet 'Helper1', enter formulas as follows:
A1: =Payments!A1
Copy this formula down to A5.
Copy the formula in A1 across to L1.
Copy the formula in B1 down to B5.
C2: =IF(Payments!C2<>"",$A2,"")
(Note that in the formula above there are no spaces - the "" is a null-string)
The formula in C2 now needs to be copied across to L2
and then C2:L2 needs to be copied down to C5:L5.
The content of worksheet 'Helper1' should be identical to worksheet 'Payments' except that the numberic values (payments) will have been replaced by the appropriate student's name.
Save the workbook.
4. On worksheet 'Helper2':
Leave columns A and B blank as they are not needed and keeping the worksheets' data "aligned" will make it easier to maintain.
Enter formulas as follows:
C1: =Helper1!C1
and copy this formula across to L1
C2: =IFERROR(INDEX(Helper1!$C$2:$L$5,SMALL(IF(Helper1!C$2:C$5="","",ROW(Helper1!C$2:C$5)-1),ROW()-1),COLUMN()-2),"")
but, and this is important, the formula must be entered as an 'array formula' by using Ctrl+Shift+Enter instead of just 'Enter'.
Copy the formula in C2 across to L2 and then copy the formulas in C2:L2 down to C5:L5.
You will see the same names as in worksheet 'Helper1' but they will have been moved up so that there are no blank cells.
Save the workbook.
5. There are two methods to create the data in worksheet 'MM DAta' - one manual and the other using an array-formula.
My preference would be the manual method, particularly when dealing with many rows and columns because Method 2 requires you to select all the (about to be transposed) cells before entering the array-formula.
Method 1:
On worksheet Helper2, select C1:L5 and copy (Ctrl+C).
On worksheet MM Data, select cell A1 and then use Paste | Paste Special | Values Transpose (there are two boxes to tick in the Paste Special dialog box) and click OK.
Method 2:
On worksheet MMData, select A1:E10 and enter the following formula:
=TRANSPOSE(Helper2!C1:L5)
but by using Ctrl+Shift+Enter to make it an array-formula.
This method can be frustrating because (a) you need to select the same size of data but transposed, so 800 rows and 50 columns in Helper2 would become 50 rows and 800 columns in MM Data and (b) you cannot delete individual cells, rows, columns in an array - only the complete array.
For the larger number of Teachers and Students that you need in your workbook, it is just a case of changing the 'L' and '5' in the formulas to the appropriate values.
ROW() and COLUMN() return the current row and column numbers and, as another example, ROW(C5) would return 5 and COLUMN(C5) would return 3. SMALL returns the 'nth' smallest value in the argument.
IFERROR is needed in the formula to allow for blank cells.
Save the workbook.
I hope that I have understood the requirement correctly and that this gives you what you want. Any problems/questions just update this post.
