XML Column to Multiple Columns
 

I have to parse an XML file to separate a single column of values in the XML
into multiple columns.

The XML schema provides several columns of which two are significant. A
column with a label for each row in the column and the next column has the
dollar value.

Basically it lays out like:
Column 9 Column 10

Death Benefit 1,000,000
Death Benefit 1,000,100
Death Benefit 1,000,200 and so on for up to 121 rows then
Cash Value 0
Cash Value 30
Cash Value 500 and so on for up to 121 rows then continue
through the rest of the column

What I need to do is parse these into seperate columns on a target workshet
so that I end up with

Column B Column C
Death Benefit Cash Value
1,000,000 0
1,000,100 100
1,000,200 500
and so on for up to 121 rows for each column.

Right now I'm using INDIRECT. The number of years will vary from one file to
the next. I would prefer to pair up the column label with the column value in
each year.

Is there a better way to do this?

XML Column to Multiple Columns
 

I have devided a method using an indirect. formula.

"Dkline" wrote:

I have to parse an XML file to separate a single column of values in the XML
into multiple columns.

The XML schema provides several columns of which two are significant. A
column with a label for each row in the column and the next column has the
dollar value.

Basically it lays out like:
Column 9 Column 10

Death Benefit 1,000,000
Death Benefit 1,000,100
Death Benefit 1,000,200 and so on for up to 121 rows then
Cash Value 0
Cash Value 30
Cash Value 500 and so on for up to 121 rows then continue
through the rest of the column

What I need to do is parse these into seperate columns on a target workshet
so that I end up with

Column B Column C
Death Benefit Cash Value
1,000,000 0
1,000,100 100
1,000,200 500
and so on for up to 121 rows for each column.

Right now I'm using INDIRECT. The number of years will vary from one file to
the next. I would prefer to pair up the column label with the column value in
each year.

Is there a better way to do this?