Further Question about Transpose

[Guest]

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

 

[Guest]

Dear Max,

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

Much thx!!

Florence

 

[Max]

You're welcome, Florence !
Glad to hear it worked for you
Thanks for the feedback