Hello,
I am trying to write a macro for my excel file. I am bringing in data from an xml. The data is a list a people and some categories that relate to each person. Each person can be linked to multiple categories, so each person is linked on mutlple lines. One column has a category name and the column next to it has a category value. The file can have 5 category names, and they are the same and repeat for each person. The category value is associted with the category name, but they can be different for each person. I need to transpose the category name to accross the top row as a header and the correlating category value listed below each category name, while removing the duplicate rows for each person. Below is an example of the view of the data when I first bring it in the Excel from the XML. Below that, is how I want it to look after I run the Macro. Any help would be greatly appreciated!
How data displays after brought in from xml
Person SSN Category Name Category Value
Smith 111-11-1111 Location Main Office
Smith 111-11-1111 Type Full time
Smith 111-11-1111 Role Executive
Jones 121-11-1111 Location Satelite Office
Jones 121-11-1111 Type Part time
Jones 121-11-1111 Role Assistant
Below is how I want it to look after using the macro
Person SSN Location Type Role
Smith 111-11-1111 Main Office Full Time Executive
Jones 121-11-1111 Satelite Office Part time Assistant
I am trying to write a macro for my excel file. I am bringing in data from an xml. The data is a list a people and some categories that relate to each person. Each person can be linked to multiple categories, so each person is linked on mutlple lines. One column has a category name and the column next to it has a category value. The file can have 5 category names, and they are the same and repeat for each person. The category value is associted with the category name, but they can be different for each person. I need to transpose the category name to accross the top row as a header and the correlating category value listed below each category name, while removing the duplicate rows for each person. Below is an example of the view of the data when I first bring it in the Excel from the XML. Below that, is how I want it to look after I run the Macro. Any help would be greatly appreciated!
How data displays after brought in from xml
Person SSN Category Name Category Value
Smith 111-11-1111 Location Main Office
Smith 111-11-1111 Type Full time
Smith 111-11-1111 Role Executive
Jones 121-11-1111 Location Satelite Office
Jones 121-11-1111 Type Part time
Jones 121-11-1111 Role Assistant
Below is how I want it to look after using the macro
Person SSN Location Type Role
Smith 111-11-1111 Main Office Full Time Executive
Jones 121-11-1111 Satelite Office Part time Assistant
