I have tidied up Form 1 on Sheet1 and used this as the controlling template for the other "Forms"
It is essential that the headers in Sheet2 Row 1 match these exactly, no leading ,trailing, or extra spaces.
The changes are highlighted with red text.
The Form numbers in Sheet1 Column A must be numbers, not numbers stored as text.
If they are text the formulae used will skip that form.
So in Sheet2 A2, Drag/Fill Down until blanks are returned.
In B2, Drag Across to Column AE then Drag Down.
In AF2, Drag Across to Column AH then Drag Down.
See the Names Manager for the Dynamic Named Range definitions, don't be tempted to change these to refer to whole Columns, you'll cripple the sheet, the ranges will automatically adjust to suit your datas' exact size.
In this sample workbook I have only filled the first ten forms,to complete select A2:AH2 and Drag/Fill down some 300 rows, get out a few bottles of "Kalyani Black Label" and sit back, it will take a while to crunch through the data ...
If you have similar large problems in the future, you might be best to use the new pay forum, I doubt if many members will be willing to do this size of problem for free. ...