Hi All, We have excel sheet in which data needs to be rearranged a bit, to start with if you open a Raw report there are 3 sections 1. Summary by CP (on top), 2. Independent by CP , then 3. Trade-Level Reconciliation.
Nothing needs to be done with 1st and 2nd section, 3rd section has data one below the other in groups and range of headers differing from each one , and they differ when you compare the headers of each group for example - Row 31 has header till column A to X, if you check row 35 has header till column A to Z, & if you check row 56 has header till column A to AA ,So we need a vba macro that helps in getting the headers put in a specific format along with data below it.
We can have a standard header format(Static format) created in a excel template and copy the raw data to the template that has VBA to rearrange data as per the (Static format).
Note:
Nothing needs to be done with 1st and 2nd section, 3rd section has data one below the other in groups and range of headers differing from each one , and they differ when you compare the headers of each group for example - Row 31 has header till column A to X, if you check row 35 has header till column A to Z, & if you check row 56 has header till column A to AA ,So we need a vba macro that helps in getting the headers put in a specific format along with data below it.
We can have a standard header format(Static format) created in a excel template and copy the raw data to the template that has VBA to rearrange data as per the (Static format).
Note:
- Nothing needs to be done with these sections of the report, because they are good as they are -1. Summary by CP (on top), 2. Independent by CP
- Everyday the range of 3rd section may vary -for Trade-Level Reconciliation, for example if in todays report it starts from row 31, tomorrow it might start from row 26. However, where ever they start, they always start with the word "Match"
- Attached a sample raw report, excel template with a "static data" tab created, also an sample output file for your reference.
- The amount of data in sample raw report varies from the original file, original file will have huge data in the same format as sample file.