0

I've exported data in XML format and at the moment it's pretty unusable after I converted to Excel.

The dataset is pretty big (and I expect I'll be running the same exercise more times, with potentially larger sets).

The data is currently in two columns (one column is the titles repeating every 6 rows and the other is the data related to each title).

I need to transform this into 6 columns with the results in the rows underneath (PICTURE, from Blue to Red)

Example

2

Place the header row manually. Then, in the cell below the first header enter:

=OFFSET($C$2,COLUMNS($A:A)-1+(ROWS($1:1)-1)*6,0)

copy this cell both across and down:

enter image description here

|improve this answer|||||
  • Thanks for your response! However, I can't seem to get it to work (even after using the equivalent equations in Spanish Excel). I've even tried to use the equation wizard for the OFFSET function (or DESREF) and I can't seem to get anything. – Toleil May 27 '17 at 6:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.