Help with Macro---transposing data

Hi,

I have been working on a macro and have been stuck. I am pulling in a list of fields into excel from an xml file. The excel file is basically a list of people, their dependents and some benefit inormation for them. When I pull in the xml, it is duplicating every person. The reason is, I am pulling in a field 'CarrierNumberType.' This field has two values for every person, so it creates a duplicate line for every person. The two values are the same for each person and just repeat down the spreadsheet. The column to the right 'CarrierNumberType' contains a value that corresponds to each 'CarrierNumberType' that is displayed.


I would like to transpose the values in the column 'CategoryNumbertype' up into the header column and then delete the 'CategoryNumbertype' column. So, for instance, there are two repeating value for the column 'CategoryNumbertype,' each value would now be transposed to a column header. Under the new Column header, I would like to transpose the associated values in the 'CarrierNumberValue' field, directly under the newly transposed columns from 'CategoryNumberType.'

Since the information is now on one like, I would then like to delete all of the duplicate lines that were created for each person. I have attached a spreadsheet. Sheet 1 shows the excel sheet when I extract it from an xml. Sheet shows how I would like it to appear after transposing the columns and removing the duplicate.

I have tried on a formula, but I am very new to VB and am having trouble, getting bugs in my formula, etc.

Would anyone be able to help? This wouuld be a huge help, I have been working on it for over a week and can't get it. If this is confusing, please let me know so I can answer any questions.

Thanks!

Why can't you filter on the zeros and then delete them?

The zero's on the SSN? Those numbers are made up, I didn't want to put real SSN's on this sheet for everyone to see. All of the data is fictional, but represents what I am doing.

My main thing is transposing those two columns. Is it possible to do all of this, especially transposing based on the original column name from when I pull the data in from the xml.

Anyone know if a macro can help?