Columns to Rows

[Fiona Messenger]

Hi

Someone a long time ago posted a really helpful macro which creates, from a
spreadsheet of data, columns to rows, e.g. I have

Fred Tea 1 sugar Chelsea Mug
Bill Coffee 2 sugars Disney Mug
Arthur Chocolate no sugar White Mug

and I want it converted to

Fred Tea
Fred 1 sugar
Fred Chelsea Mug
Bill Coffee
Bill 2 sugars
etc etc

The one I had created a new workbook within the spreadsheet, unfortunately
that was two work desktops ago, and I had no use for it again....until now.
Anyone out there recall this or can provide? As I remember, it wasn't a
particularly long macro.

Best Regards

Fiona

 

[Harlan Grove]

...
....

Fred Tea 1 sugar Chelsea Mug
Bill Coffee 2 sugars Disney Mug
Arthur Chocolate no sugar White Mug

and I want it converted to

Fred Tea
Fred 1 sugar
Fred Chelsea Mug
Bill Coffee
Bill 2 sugars
etc etc

....

Don't need macros. If the first range were named TBL, and the top-left
result cell were A1, try these formulas.

A1:
=INDEX(TBL,INT((ROWS(A$1:A1)+COLUMNS(TBL)-2)/(COLUMNS(TBL)-1)),1)

B1:
=VLOOKUP(A1,TBL,2+MOD(ROWS(B$1:B1)-1,COLUMNS(TBL)-1),0)

Select A1:B1 and fill down as far as needed.