Further Question about Transpose

[Florence]

Hello everybody,

I have a single column of texts with the following pattern:
Row 1: Company Name
Row 2: Address
Row 3: Tel
Row 4: Fax
Row 5: Website

For example:

ABC Company Ltd
20/F, Abc Building, 1 Abc Street, ABC Country
1111 1111
2222 2222
www.Abc.com.
XYZ Ptd Ltd
Suite 5008 – 5010, 50/F, XYZ Building, 7 XYZ Street, XYZ Country
3333 3333
4444 4444
www.xyz.com
(and so on …)

The length of such column is not fixed.

So can I use “Paste Special -> Transpose” or =TRANSPOSE(array) to transform
the data that column A contains all company names, column B contains all
addresses, column C contains all tel num. and so on?

(The “Paste Special -> Transpose” transformed all data in a single row. But
I want to transform the record line – by – line without moving again …)

Much appreciate if any tips about this.

Thank you!!

[Max]

One way to try ..

Assuming source data is in col A, A1 down,
in groups of 5 rows each as indicated
(w/o any intervening blank rows)

Put in say, B1:

=OFFSET($A$1,ROWS($A$1:A1)*5-5+COLUMNS($A$1:A1)-1,)

Copy B1 across 5 cols to F1, fill down until zeros appear signalling
exhaustion of data from col A

The above will return the desired results in cols B to F
--
Rgds
Max
xl 97
---
GMT+8, 1� 22' N 103� 45' E
xdemechanik <at>yahoo<dot>com
----
"Florence" <[email protected]> wrote in message
news:[email protected]...
> Hello everybody,
>
> I have a single column of texts with the following pattern:
> Row 1: Company Name
> Row 2: Address
> Row 3: Tel
> Row 4: Fax
> Row 5: Website
>
> For example:
>
> ABC Company Ltd
> 20/F, Abc Building, 1 Abc Street, ABC Country
> 1111 1111
> 2222 2222
> www.Abc.com.
> XYZ Ptd Ltd
> Suite 5008 - 5010, 50/F, XYZ Building, 7 XYZ Street, XYZ Country
> 3333 3333
> 4444 4444
> www.xyz.com
> (and so on .)
>
> The length of such column is not fixed.
>
> So can I use "Paste Special -> Transpose" or =TRANSPOSE(array) to
transform
> the data that column A contains all company names, column B contains all
> addresses, column C contains all tel num. and so on?
>
> (The "Paste Special -> Transpose" transformed all data in a single row.
But
> I want to transform the record line - by - line without moving again .)
>
> Much appreciate if any tips about this.
>
> Thank you!!
>

[Florence]

Dear Max,

Thanks a lot for your help, the formula works fine!!

Much thx!!

Florence

"Max" wrote:

> One way to try ..
>
> Assuming source data is in col A, A1 down,
> in groups of 5 rows each as indicated
> (w/o any intervening blank rows)
>
> Put in say, B1:
>
> =OFFSET($A$1,ROWS($A$1:A1)*5-5+COLUMNS($A$1:A1)-1,)
>
> Copy B1 across 5 cols to F1, fill down until zeros appear signalling
> exhaustion of data from col A
>
> The above will return the desired results in cols B to F
> --
> Rgds
> Max
> xl 97
> ---
> GMT+8, 1° 22' N 103° 45' E
> xdemechanik <at>yahoo<dot>com
> ----
> "Florence" <[email protected]> wrote in message
> news:[email protected]...
> > Hello everybody,
> >
> > I have a single column of texts with the following pattern:
> > Row 1: Company Name
> > Row 2: Address
> > Row 3: Tel
> > Row 4: Fax
> > Row 5: Website
> >
> > For example:
> >
> > ABC Company Ltd
> > 20/F, Abc Building, 1 Abc Street, ABC Country
> > 1111 1111
> > 2222 2222
> > www.Abc.com.
> > XYZ Ptd Ltd
> > Suite 5008 - 5010, 50/F, XYZ Building, 7 XYZ Street, XYZ Country
> > 3333 3333
> > 4444 4444
> > www.xyz.com
> > (and so on .)
> >
> > The length of such column is not fixed.
> >
> > So can I use "Paste Special -> Transpose" or =TRANSPOSE(array) to
> transform
> > the data that column A contains all company names, column B contains all
> > addresses, column C contains all tel num. and so on?
> >
> > (The "Paste Special -> Transpose" transformed all data in a single row.
> But
> > I want to transform the record line - by - line without moving again .)
> >
> > Much appreciate if any tips about this.
> >
> > Thank you!!
> >
>
>
>

[Max]

You're welcome, Florence !
Glad to hear it worked for you
Thanks for the feedback
--
Rgds
Max
xl 97
---
GMT+8, 1� 22' N 103� 45' E
xdemechanik <at>yahoo<dot>com
----
"Florence" <[email protected]> wrote in message
news:[email protected]...
> Dear Max,
>
> Thanks a lot for your help, the formula works fine!!
>
> Much thx!!
>
> Florence