[Solved] Transposing data in a column to multiple rows
-
- Posts: 11
- Joined: Thu Apr 25, 2019 8:33 pm
[Solved] Transposing data in a column to multiple rows
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
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
Last edited by Prasanjith on Sat Apr 27, 2019 6:53 pm, edited 1 time in total.
libreoffice 6.2.2.2 on Unbuntu 19.04
Re: Transposing data in a column to multiple rows
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.
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.
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
Re: Transposing data in a column to multiple rows
I used the INDEX() and ROW() functions in the attached file.
- Attachments
-
- Rearrange.ods
- (9.09 KiB) Downloaded 127 times
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
-
- Posts: 11
- Joined: Thu Apr 25, 2019 8:33 pm
Re: Transposing data in a column to multiple rows
I am very new to libre office. can you please share the steps on how to do this.RusselB wrote: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.
Thank you
Prasanjith
libreoffice 6.2.2.2 on Unbuntu 19.04
-
- Posts: 11
- Joined: Thu Apr 25, 2019 8:33 pm
Re: Transposing data in a column to multiple rows
thank you for the fast reply. this method works only when there is 4 entries only. when there is more or less it fails.FJCC wrote:I used the INDEX() and ROW() functions in the attached file.
I have attached a sample data for your kind perusal. sorry I didn't know that i could attach documents.
Thank you
Prasanjith
- Attachments
-
- sample data.ods
- (13.36 KiB) Downloaded 128 times
libreoffice 6.2.2.2 on Unbuntu 19.04
-
- Posts: 11
- Joined: Thu Apr 25, 2019 8:33 pm
Re: Transposing data in a column to multiple rows
i think the answer is TRANSPOSE FUNCTION.....but how do i say go to next row after black cell or cells ?
libreoffice 6.2.2.2 on Unbuntu 19.04
Re: Transposing data in a column to multiple rows
I'll do up a sample spreadsheet tomorrow.
Bed time for me.
Bed time for me.
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
-
- Posts: 11
- Joined: Thu Apr 25, 2019 8:33 pm
Re: Transposing data in a column to multiple rows
Thanks bro.....sweet dreams
libreoffice 6.2.2.2 on Unbuntu 19.04
-
- Posts: 11
- Joined: Thu Apr 25, 2019 8:33 pm
Re: Transposing data in a column to multiple rows
any upadates???
libreoffice 6.2.2.2 on Unbuntu 19.04
-
- Posts: 11
- Joined: Thu Apr 25, 2019 8:33 pm
Re: Transposing data in a column to multiple rows
Hi All,
I was able to get this resolved in another thread via Macro.
you can check that out from the following link
viewtopic.php?f=20&t=97842&p=469382#p469382
RusselB and FJCC thank you very much for effort and support.
Prasanjith
Kudos...
I was able to get this resolved in another thread via Macro.
you can check that out from the following link
viewtopic.php?f=20&t=97842&p=469382#p469382
RusselB and FJCC thank you very much for effort and support.
Prasanjith
Kudos...
libreoffice 6.2.2.2 on Unbuntu 19.04