I used Power Query (Get & Transform Data) on the Data tab of the ribbon.
First I named the range you want to process A6:L1130 and called it myData (I did this to prevent Power Query converting the data into a proper Excel table).
Then with that range selected went into Power Query by clicking the From Table/Range button on the ribbon:

2024-07-17_140736.jpg

which brings up the Power Query window:

2024-07-17_143508.jpg

where I went through a few steps (on the right in the picture):

• Promoted the first row into headers,
• removed entirely blank rows,
• added a new column called IsHdrRow which looks to see if the MODULE NUMBER AND PREFIX column is blank or not, if it is then put TRUE else put FALSE,
• filled down the MODULE NUMBER AND PREFIX column,
• removed the header rows by filtering the IsHdrRow to leave only the FALSE rows,
• got rid of the blank rows in the WORK CARD NUMBER column so that merged cells are represented by a single row (this also got rid of Place Holder rows),
• then I grouped the rows according to column MODULE NUMBER AND PREFIX and got it to give me a count of rows in each group, which is the results table you see.

You need to make sure that when you change the data in your source data that (a) the named range myData covers it all and (b) you'll need to refresh the result table by right-clicking somewhere on it and choosing Refresh.