Hi Everyone,
I have a report which I generate on a regular basis and need to convert from a tabular view to a matrix view for readability.
I use Excel 2013
I would appreciate if anyone could assist me with some VBA code to accomplish the following:
Some of the remaining column headers from the original report will be used as the column headers for the new report with the exception of column "F", which will be the 6 different records, or course names, listed in the Original Report and will be column headers for columns "B" to "G" in the new report. These 6 headers will always remain the same.
Below is a sample data set from the Original Report, which have hundreds more records. The non-required columns, as listed above, have already been removed for a better view, the remaining columns in " ":
Original Report:
<tbody>
</tbody>
Converted Report: (This is what the new report will look like)
<tbody>
</tbody>
Hopefully I have explained the requirement in such a way that it makes sense. Please let me know should you require more information.
I have a report which I generate on a regular basis and need to convert from a tabular view to a matrix view for readability.
I use Excel 2013
I would appreciate if anyone could assist me with some VBA code to accomplish the following:
- Delete all non-required columns, in this case " B to E, G, H, I, K to U, W to AB, AD to AG, AI to AO (35 of the original 41 to be deleted) The same columns will always be deleted.
- Then change the layout and view to a matrix, please see "Converted Report" below.
Some of the remaining column headers from the original report will be used as the column headers for the new report with the exception of column "F", which will be the 6 different records, or course names, listed in the Original Report and will be column headers for columns "B" to "G" in the new report. These 6 headers will always remain the same.
Below is a sample data set from the Original Report, which have hundreds more records. The non-required columns, as listed above, have already been removed for a better view, the remaining columns in " ":
Original Report:
<tbody> </tbody> | "F"
<tbody> </tbody> | "J"
<tbody> </tbody> | "V"
<tbody> </tbody> | "AC"
<tbody> </tbody> | "AH"
<tbody> </tbody> | |||||||
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> | |||||||
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> | |||||||
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> | |||||||
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> | |||||||
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> | |||||||
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> | |||||||
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> | |||||||
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> | |||||||
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> | |||||||
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> | |||||||
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> | |||||||
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> | |||||||
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> | Care Worker | |||||||
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> | |||||||
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> | |||||||
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> | |||||||
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> |
<tbody>
</tbody>
Converted Report: (This is what the new report will look like)
"A" Learner | "B"
<tbody> </tbody> | "C"
<tbody> </tbody> | "D"
<tbody> </tbody> | "E"
<tbody> </tbody> | "F"
<tbody> </tbody> | "G"
<tbody> </tbody> | "H" LOCATION CODE | "I"
<tbody> </tbody> | "J"
<tbody> </tbody> | |||||||||
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> | MHH |
<tbody> </tbody> |
<tbody> </tbody> | |||||||||
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> | MHH |
<tbody> </tbody> |
<tbody> </tbody> | ||||||||||||
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> | MHH |
<tbody> </tbody> |
<tbody> </tbody> | ||||||||||||
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> | MHH |
<tbody> </tbody> |
<tbody> </tbody> |
<tbody>
</tbody>
Hopefully I have explained the requirement in such a way that it makes sense. Please let me know should you require more information.
Last edited: