I'm looking to transpose a spreadsheet that consists of a column with an ID for the whole row of data, followed by data fields listed in a typical column/row setup, similar to the table below. And for background, my particular data set has thousands of rows and about 20 or so columns.
<tbody>
</tbody>
However, due to a limitation of the system this data will be used in, I need to alter this structure to an alternate format where I list out the ID, the field name, followed by the answer for that field, similar to the following:
<tbody>
</tbody>
So to solve this transformation via VBA I'm thinking I need to do the following:
Is this the right approach? Any thoughts, ideas, or code examples are greatly appreciated.
ID | Name | Field 1 | Field 2 |
1 | A | X | 345 |
2 | B | Y | 123 |
<tbody>
</tbody>
However, due to a limitation of the system this data will be used in, I need to alter this structure to an alternate format where I list out the ID, the field name, followed by the answer for that field, similar to the following:
ID | Field | Answer |
1 | Name | A |
1 | Field 1 | X |
1 | Field 2 | 345 |
2 | Name | B |
2 | Field 1 | Y |
2 | Field 2 | 123 |
<tbody>
</tbody>
So to solve this transformation via VBA I'm thinking I need to do the following:
- transpose the field listing into column 2
- have it fill ID 1 out in front of the transposed field names
- transpose the field data into column 3
- then have it repeat for each subsequent number
Is this the right approach? Any thoughts, ideas, or code examples are greatly appreciated.