Formatting data for pivot - single column to multiple.

[OneCurtis]

I have a list of values that I need to format for processing and the format of the data isn't suitable in it's current form.

The values are currently in the following format:

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD]http://www.example.com/subfolder/page1[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD]http://www.example.com/subfolder/page2[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD]Status code: 301[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD]linked from pages:[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD][/TD]
[TD][/TD]
[TD]http://www.example.com/subfolder2/page1[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD][/TD]
[TD][/TD]
[TD]http://www.example.com/subfolder2/page2[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD][/TD]
[TD][/TD]
[TD]http://www.example.com/subfolder2/page3[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD][/TD]
[TD][/TD]
[TD]http://www.example.com/subfolder/page3[/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD][/TD]
[TD][/TD]
[TD]http://www.example.com/subfolder/page4[/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD][/TD]
[TD][/TD]
[TD]Status code: 301[/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD][/TD]
[TD][/TD]
[TD]linked from pages:[/TD]
[/TR]
[TR]
[TD="align: center"]13[/TD]
[TD][/TD]
[TD][/TD]
[TD]http://www.example.com/subfolder3/page1[/TD]
[/TR]
</tbody>[/TABLE]
I need the value from C1 to appear in A5:A7, the value from C2 to appear in B5:B7 and the same again for the next set of records starting from C9. There are far too many records to attempt this manually. What's the best way to automate the process, either with a formula or VBA?

Thanks for your help.

 

[ZVI]

Not sure I've understood task correctly, but
try this formula in A5: =IF($C5="","",IF(A4<>"",A4,IF($C4="linked from pages:",$C1,"")))
and copy it to A5:A100 , where A100 is the bottom cell of the resulting range

Formula for B5 is =IF($C5="","",IF(B4<>"",B4,IF($C4="linked from pages:",$C2,"")))
copy it to B5:B100

 

[ZVI]

Or put this formula into A5 and then copy A5 to A5:B100
=IF($C5="","",IF(A4<>"",A4,IF($C4="linked from pages:",INDEX($C1:$C2,COLUMN()),"")))