Transposing data from columns to rows

  • Thread starter Thread starter Annalise Vogel
  • Start date Start date
A

Annalise Vogel

Hi,

I have cut and pasted a table from an online database into a spreadsheet.

It has pasted into one column.

How can I format the data so that each entry appears in one row across
various columns.

Currently I am transposing each entry individually by paste special.

Help! I have 300 to do!
 
Why don't you use data>import external data>web query

--
Regards,

Peo Sjoblom

(No private emails please)
 
Why don't you use data>import external data>web query
Click to expand...
Because the database doesn't download into excel, and I don't have
ODBC......anyone else have an easy explanation.
 
Hazarding a shot ...

Assuming your data is in groups of 3 lines each, in A1 down:

Item1
Name1
Descr1
Item2
Name2
Descr2
etc

Put in say, B1: =INDEX($A:$A,ROW(A1)*3-3+COLUMN(A1))

Copy B1 across 3 cols to D1,
fill down until zeros appear signalling exhaustion of data

Cols B to D will return:

Item1 Name1 Descr1
Item2 Name2 Descr2
etc

Freeze the results in cols B to D with an in-place:
Copy > Paste special > Values > OK

Adapt to suit: Change the "3"'s in the formula to suit the # of lines per
group, then copy across from the starting cell by the same # of cols, and
fill down
 
Annalise said:
Because the database doesn't download into excel, and I don't have
ODBC......anyone else have an easy explanation.
Click to expand...
Copy the entire column, and Paste Special into the entire row using the
Transpose option.

Alan Beban
 
Alan said:
Copy the entire column, and Paste Special into the entire row using the
Transpose option.

Alan Beban
Click to expand...
But you can't do 300 because you only have 256 columns.

Alan Beban
 
Back
Top