formula or vba to extract complex specific data in transpose mode on summary sheet

I have an extract sheet in which specific data has to be extracted in transpose mode.

But the issue some are alpha numerical data and data are are uneven .

The extract sheets has headers and each reference data are scattered on 7 rows and 8 columns which are always same format.

the first page has only two reference and last page also

As example on the actual sheet it starts with 5 reference for ease to understand

One page has 5 reference data and is separated by 3 blank rows which represents the second and so on .

The yellow cells are those that need to be extracted and sent to summary sheet


the data report can be 40 pages but are always two reference data on first and last page and the full pages 5 reference data with 3 blanks row to represent the next page

Using the sample sheet EXTRACT as your source file, the attached is how I would approach this with formulas only.

The yellow cells are array formulas, so edits to those must be confirmed by pressing CTRL+SHIFT+ENTER, you will see curly braces {} appear around the formula to confirm the array is active.

The blue cells are regular formulas.

VBA would have no problem with this either. Both approaches would be fine since the data format is static.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.