Converting Table to useful database format

[alfordtp]

I am scraping a lot of data from the interwebs and exporting it to a CSV file. The issue is the data needs to be converted to another format so that it is more useful. As exported, it looks like this:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Unit[/TD]
[TD]Characteristic[/TD]
[TD]Value[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD]Color[/TD]
[TD]Red[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD]Size[/TD]
[TD]Large[/TD]
[/TR]
[TR]
[TD]Grape[/TD]
[TD]Color[/TD]
[TD]Green[/TD]
[/TR]
[TR]
[TD]Grape[/TD]
[TD]Size[/TD]
[TD]Small[/TD]
[/TR]
</tbody>[/TABLE]

While this data can work, I would like to convert it to another format, such as:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Unit[/TD]
[TD]Color[/TD]
[TD]Size[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD]Red[/TD]
[TD]Large[/TD]
[/TR]
[TR]
[TD]Grape[/TD]
[TD]Green[/TD]
[TD]Small[/TD]
[/TR]
</tbody>[/TABLE]

Any thoughts on a relatively easy (not resource intensive) way to do this? I have about 60,000 records to apply this to.

Thanks in advance!

 

[MrJoosten]

Hi alfordTP,

Are the units, characteristics and values always in the same order?

If so, I guess this will work considering the first data is in column A2:

formula cell D2: =IF(B2="Color";C2;"")
formula cell E2: =IF(B3="Size";C3;"")

Afterwards, filter empty rows and delete them.

Maybe someone has an better way of doing it, but this is easy and will work I guess