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

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 <atyahoo<dotcom
----
"Florence" wrote in message
...
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!!

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 <atyahoo<dotcom
----
"Florence" wrote in message
...
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!!

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 <atyahoo<dotcom
----
"Florence" wrote in message
...
Dear Max,

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

Much thx!!

Florence