Formatting issue - Inserting variable rows

Hi, I'm long time lurker who finally got round to joining

I’m trying to build a spreadsheet to sort data to a certain format. Each week I receive a spreadsheet with data in one format (always the same layout) and in order to use that data I need to alter the format substantially; as there are several hundred files on the incoming spreadsheet using Excel to sort the data is ideal.

I’m not too clued up on the terminology but basically what I need to do is insert a variable number of rows after each unique record (based on an account number in column A) to bring the total number of rows with that account number to 10. (I.e. if account 0001 in has one record I need to insert nine rows after it, if account 0002 had four records I need to insert 6 rows after it)

I have no idea how to get excel to identify the number rows with an account number in and then insert the necessary rows afterwards, I’m not sure if what I’m asking is even possible, if it isn’t then I’m sure someone here can tell me!

The new rows must be totally blank except for column A which needs the account number in, I’ve attached an ‘before’ spreadsheet with an example of how the data looks when it comes in and a ‘after’ spreadsheet showing how I need the spreadsheet to look after it’s formatted – ideally I’d like to run the formatting using a macro, that way I can keep the spreadsheet and just copy the incoming data in each week to sort it before copying it out again.

The major problem I have is that although the data comes in the same layout each week the number of rows for each account is variable, obviously this means that one week I might need to insert 4 rows after the first account on the spreadsheet but the next week I might only need to insert 1.

Thanks in advance for your help!