Moving records in single column, multiple row format to single row, multiple columns format

[pheidias]

I am working with a system that reports user data into two columns: Column A has the fields (9 total per record) and Column B has the corresponding user data.


Column A looks like this:

• 1. Last Name:
• 2. First Name:
• 3. Middle Name:
• 4. Card Number:
• 5. Employee Ref:
• 6. Personal Details:
• 7. Associate Number:
• 8. Location Name:
• 9. Status:

The formatting on this report has the fields repeated for each record, meaning that row 10 will be blank, and row 11 will be Last Name:, row 12 will be First Name:, etc. This format results in very lengthy reports that are difficult to manage.


Another complication to this report concerns null values. Fields with null values are excluded from the report. For example, a record that does not have a Location Name will exclude the Location Name: field altogether. The result of the missing field is a record that is 8 rows instead of the the normal 9 rows. Some of these records are missing multiple fields, on account of null values.


I am looking for an elegant way to reformat this report. Specifically, I'm looking for...
-The fields in Column A become Column headers (A,B,C,D,E,F,G,H,I).
-The data in Column B is moved onto a single row, aligned with the corresponding field headers.


The report I'm currently looking at has 3730 records. However, the system outputs a file that is 43906 rows (including blank rows). I'd like to get this report to 3731 rows (1 row for field headers + 3730 records).


Any assistance with reformatting this report would be greatly appreciated.


Thank you,
pheidias