I have an extract file that is in excel and lists contract numbers with a payment amount to the right. Each contract has multiple payments by period and thus there are multiple rows for each contract representing the multiple periods. The number of periods for each contract varies. The extract file contains multiple contracts. What I would like to do is have the contract number listed in one row and copy, paste, transpose the payments to the right of the contract number. The resulting file or sheet will have only one row per contract. The issue is that the code will have to recognize when the file number changes. I know I can do this manually but with 10 files and over 450 contracts this is a bit time consuming.
Attached is a sample file with two tabs titled "Extract File" and "Desired Format".
Extract File - is a sample of the main file that contains the data.
Desired Format - is a sample of what I would like the final result to be. It can be in another sheet or another file.
Thank you in advance for any help you can provide.