2

How would I go about automating the conversion of a very long list in a format like this:

enter image description here

Into a table like this:

enter image description here

I am using Microsoft Excel 2010 on Windows... because of the layout of my data as seen in the image, the pivot table wizard is not recognising the fields.

2 Answers 2

0

There are two general ways to do this, depending on data. If the spacing is exact meaning that the number of rows does not change at all, then the offset command would be easiest.

If the data is on sheet 1, on sheet 2 enter the first two lines of headers.

  • use naming to name sheet1!$a$1 as Spot or any other name you like
  • use naming to name 5 as Step
  • use naming to name Pik as =+((COLUMN()-2)*Step)+(ROW()-1)

then use in b2 and extend as far as needed =+OFFSET(spot,Pik,1)

this pulls in all the data.

There are a couple of flaws with this, the biggest one is it will break if a single extra line or other minor mod occurs midway through data. There is another way of using a search function type approach where we look for a key data (Name, Age, etc.) and return the info from the next cell. It is more complicated. I may write it up later.

3
  • Hi thankyou for your answer. Please could you clarify what you mean by "name 5 as Step" and also "name Pik as...".
    – JBiss
    Nov 19, 2015 at 15:58
  • Naming is primarily used to give names to Cells, constants, and fomula. • Spot => Sheet1!$A$1 {Cell Naming } • Step=5 this is how far the distance between two records is {Constant Naming } • Pik is the equation for finding the relative offset from spot to the data {Formula naming } The equation without using naming is: =+OFFSET(Sheet1!$A$1,((COLUMN()-2)*5)+(ROW()-1),1) I find that form more clumsy and harder to use/troubleshoot. { There are more types than the 3 types of naming I used. For me, they are the ones I heavily use }
    – bvaughn
    Nov 19, 2015 at 17:16
  • Thank you for your explanation. Took a little while of trying and working it out as my data set is a little larger than the example. But I implemented the three names: Spot, Step and Pik in the way that you described. And placed the 'OFFSET' formula on a second sheet and extended it. Worked perfect. Thanks.
    – JBiss
    Nov 20, 2015 at 9:58
0
  1. Copy your data to text editor with RegEx replace (Notepad++).

  2. Replace all: ^Person[\r\n]+Name[\s]+([\w]+)[\r\n]+Age[\s]+([\d]+)[\r\n]+Size[\s]+([\d])+[\r\n]+ to: \1\t\2\t\3\r\n

  3. Copy data back to Excel.

  4. Transpose data and add required headers.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .