Transposing data from columns to rows

[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!

[Peo Sjoblom]

Why don't you use data>import external data>web query

--
Regards,

Peo Sjoblom

(No private emails please)


"Annalise Vogel" <[email protected]> wrote in message
news:BFACEB64.11A6%[email protected]...
> 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!
>
>
>

[Annalise Vogel]

On 25/11/05 4:35 PM, in article [email protected], "Peo
Sjoblom" <[email protected]> wrote:

> Why don't you use data>import external data>web query
Because the database doesn't download into excel, and I don't have
ODBC......anyone else have an easy explanation.

[Max]

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
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

[Alan Beban]

Annalise Vogel wrote:
> On 25/11/05 4:35 PM, in article [email protected], "Peo
> Sjoblom" <[email protected]> wrote:
>
>
>>Why don't you use data>import external data>web query
>
> Because the database doesn't download into excel, and I don't have
> ODBC......anyone else have an easy explanation.
>
Copy the entire column, and Paste Special into the entire row using the
Transpose option.

Alan Beban

[Alan Beban]

Alan Beban wrote:
> Annalise Vogel wrote:
>
>> On 25/11/05 4:35 PM, in article [email protected],
>> "Peo
>> Sjoblom" <[email protected]> wrote:
>>
>>
>>> Why don't you use data>import external data>web query
>>
>>
>> Because the database doesn't download into excel, and I don't have
>> ODBC......anyone else have an easy explanation.
>>
> Copy the entire column, and Paste Special into the entire row using the
> Transpose option.
>
> Alan Beban
But you can't do 300 because you only have 256 columns.

Alan Beban