[Solved] Transposing data in a column to multiple rows

[Prasanjith]

Hi team,[*]

I searched the forum but did not find answer to my problem. forgive me if i have repeated a thread.
I am using libreoffice 6.2.2.2 on Ubuntu 19.04

I want to know how i can transpose data in a single column (as shown below)

(Column A)
Name1
Address 1
Telephone 1

(empty cell)

Name2
Address 2
Telephone 2

(empty cell)

Name3
Address 3
Telephone 3

(empty cell)

Name4
Address 4
Telephone 4

to multiple rows accordingly as shown below.(A new row after every empty cell or cells )

(Column B) | (Column C) | (Column D)
Name1 | Address 1 | Telephone 1
Name2 | Address 2 | Telephone 2
Name3 | Address 3 | Telephone 3
Name4 | Address 4 | Telephone 4

Thank you
prasanjith

urls are for your kind perusal

https://drive.google.com/open?id=1mxjqz ... aLwgnkECG1

https://drive.google.com/open?id=1oglPM ... eJEPdLYy3p

[RusselB]

I'm thinking use the TRANSPOSE function in the Name row of each of your entries.
After you have the data transposed, delete the data from column A, then sort the data. Sorting the data will force the (now) blank lines to the bottom of the sorted data.

[FJCC]

I used the INDEX() and ROW() functions in the attached file.

[Prasanjith]

I'm thinking use the TRANSPOSE function in the Name row of each of your entries.
After you have the data transposed, delete the data from column A, then sort the data. Sorting the data will force the (now) blank lines to the bottom of the sorted data.

I am very new to libre office. can you please share the steps on how to do this.

Thank you
Prasanjith

[Prasanjith]

I used the INDEX() and ROW() functions in the attached file.

thank you for the fast reply. this method works only when there is 4 entries only. when there is more or less it fails.
I have attached a sample data for your kind perusal. sorry I didn't know that i could attach documents.

Thank you
Prasanjith

[Prasanjith]

i think the answer is TRANSPOSE FUNCTION.....but how do i say go to next row after black cell or cells ?

[RusselB]

I'll do up a sample spreadsheet tomorrow.
Bed time for me.

[Prasanjith]

Thanks bro.....sweet dreams

[Prasanjith]

any upadates???

[Prasanjith]

Hi All,

I was able to get this resolved in another thread via Macro.

you can check that out from the following link

https://forum.openoffice.org/en/forum/viewtopic.php?f=20&t=97842&p=469382#p469382


RusselB and FJCC thank you very much for effort and support.

Prasanjith
Kudos...