Info1 | Info2 | Info3 | Info4 | Data1 | Data2 | Data3 | Data4 |
A | 1 | Blue | Hot | 0.4 | 0.5 | 1.3 | 2.7 |
B | 2 | White | Cold | 2.3 | 0.1 | 1.5 | 8.1 |
C | 3 | Gold | Hot | 2.6 | 7.1 | 6.1 | 0.8 |
D | 4 | Brown | Cold | 0.9 | 2.6 | 2.3 | 1.1 |
E | 1 | White | Hot | 0.1 | 0.6 | 2.3 | 2.0 |
F | 7 | Red | Cold | 5.2 | 1.0 | 8.1 | 5.1 |
G | 2 | Purple | Cold | 1.6 | 0.1 | 4.0 | 3.5 |
<tbody>
</tbody>
I find I need to do this often so I'm looking for a way to automate. I would like to repeat the "Info" rows and move each of the "Data" values to a single column of data. The table below shows what the result would be for the first two "Data" columns.
I get data in this format often and find the converted table to be much more flexible for analysis.
To make matters harder, the number of Info and Data items vary so I would like to account for that.
Any help would be greatly appreciated and would make my life a lot less tedious.
Info1 | Info2 | Info3 | Info4 | Data |
A | 1 | Blue | Hot | 0.4 |
B | 2 | White | Cold | 2.3 |
C | 3 | Gold | Hot | 2.6 |
D | 4 | Brown | Cold | 0.9 |
E | 1 | White | Hot | 0.1 |
F | 7 | Red | Cold | 5.2 |
G | 2 | Purple | Cold | 1.6 |
A | 1 | Blue | Hot | 0.5 |
B | 2 | White | Cold | 0.1 |
C | 3 | Gold | Hot | 7.1 |
D | 4 | Brown | Cold | 2.6 |
E | 1 | White | Hot | 0.6 |
F | 7 | Red | Cold | 1.0 |
G | 2 | Purple | Cold | 0.1 |
<tbody>
</tbody>