0

Apologies if I'm not clear on this...

I have a problem with an Excel spreadsheet and would be very grateful for advice on how to deal with it. I should add that I'm no expert with scripting and have a reasonable (but not expert) working knowledge of Excel basics.

At present, I have a spreadsheet which is laid out as follows:

Excel layout

I need to convert it so that each name in column 1 has just one value in the next cell, and the name is then repeated on the following row with the next value, as follows, until all the values in the proceeding cells are used up, at which point it moves onto the next row:

john    23
john    54
john    45
paul    654
paul    6

And so on. Is there a quick and simple way to achieve this? The list is very, very long, and I don't want to have to do this by hand - we're talking 1000+ rows, with up to 20 values in each row!

Grovelling thanks in advance.

CallumDA
  • 12,025
  • 6
  • 30
  • 52
TrentSC
  • 3
  • 3
  • see here: http://stackoverflow.com/questions/20541905/convert-matrix-to-3-column-table-reverse-pivot-unpivot-flatten-normal – Scott Craner Feb 10 '17 at 16:19

1 Answers1

1

Yep, as @Scott Craner pointed out, you need to use multiple consolidated ranges

Set up your data with headings (any will do)

    Item1   Item2   Item3   Item4   Item5   Item6
John    23  54  45          
Paul    654 6   1   6781        
George  68  6   987         
Ringo   135 6   456         
Jane    684 1               
Mary    1   87  357 618 687 210

Then press Alt+D+P. Select multiple consolidated ranges then next and create a single page field for me. Select your entire range (including the headings). Click Add, then Finish

That will create a pivot table. Double click on the bottom right value (the grand-grand total) and something like this will appear. Remove the blanks and you're done!

Row Column  Value   Page1
George  Item1   68  Item1
George  Item2   6   Item1
George  Item3   987 Item1
George  Item4       Item1
George  Item5       Item1
George  Item6       Item1
Jane    Item1   684 Item1
Jane    Item2   1   Item1
Jane    Item3       Item1
Jane    Item4       Item1
Jane    Item5       Item1
Jane    Item6       Item1
John    Item1   23  Item1
John    Item2   54  Item1
John    Item3   45  Item1
John    Item4       Item1
John    Item5       Item1
John    Item6       Item1
Mary    Item1   1   Item1
Mary    Item2   87  Item1
Mary    Item3   357 Item1
Mary    Item4   618 Item1
Mary    Item5   687 Item1
Mary    Item6   210 Item1
Paul    Item1   654 Item1
Paul    Item2   6   Item1
Paul    Item3   1   Item1
Paul    Item4   6781    Item1
Paul    Item5       Item1
Paul    Item6       Item1
Ringo   Item1   135 Item1
Ringo   Item2   6   Item1
Ringo   Item3   456 Item1
Ringo   Item4       Item1
Ringo   Item5       Item1
Ringo   Item6       Item1
CallumDA
  • 12,025
  • 6
  • 30
  • 52
  • Neat (I had to double-click on the grand total to get the table though) – Tom Sharpe Feb 10 '17 at 16:52
  • Ah yes - I going through it too quickly! Thanks – CallumDA Feb 10 '17 at 16:54
  • 1
    That did it - huge and pathetically grovelling thanks. I had a momentary panic when I looked at the results without reading your last note about double clicking on the grand total cell, at which point everything dropped into place. – TrentSC Feb 10 '17 at 17:02