[Solved] Transposing data in a column to multiple rows

Discuss the spreadsheet application

[Solved] Transposing data in a column to multiple rows

Postby Prasanjith » Thu Apr 25, 2019 9:15 pm

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
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
Prasanjith
 
Posts: 11
Joined: Thu Apr 25, 2019 8:33 pm

Re: Transposing data in a column to multiple rows

Postby RusselB » Fri Apr 26, 2019 4:21 am

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.
OpenOffice 4.1.7 and LibreOffice 6.3.3.2 on Windows 7 Pro & Ultimate
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.
User avatar
RusselB
Moderator
 
Posts: 5677
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Transposing data in a column to multiple rows

Postby FJCC » Fri Apr 26, 2019 4:24 am

I used the INDEX() and ROW() functions in the attached file.
Attachments
Rearrange.ods
(9.09 KiB) Downloaded 11 times
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7385
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Transposing data in a column to multiple rows

Postby Prasanjith » Fri Apr 26, 2019 5:13 am

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.


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

Thank you
Prasanjith
libreoffice 6.2.2.2 on Unbuntu 19.04
Prasanjith
 
Posts: 11
Joined: Thu Apr 25, 2019 8:33 pm

Re: Transposing data in a column to multiple rows

Postby Prasanjith » Fri Apr 26, 2019 5:43 am

FJCC wrote: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
Attachments
sample data.ods
(13.36 KiB) Downloaded 17 times
libreoffice 6.2.2.2 on Unbuntu 19.04
Prasanjith
 
Posts: 11
Joined: Thu Apr 25, 2019 8:33 pm

Re: Transposing data in a column to multiple rows

Postby Prasanjith » Fri Apr 26, 2019 6:16 am

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
Prasanjith
 
Posts: 11
Joined: Thu Apr 25, 2019 8:33 pm

Re: Transposing data in a column to multiple rows

Postby RusselB » Fri Apr 26, 2019 6:21 am

I'll do up a sample spreadsheet tomorrow.
Bed time for me.
OpenOffice 4.1.7 and LibreOffice 6.3.3.2 on Windows 7 Pro & Ultimate
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.
User avatar
RusselB
Moderator
 
Posts: 5677
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Transposing data in a column to multiple rows

Postby Prasanjith » Fri Apr 26, 2019 6:36 am

Thanks bro.....sweet dreams
libreoffice 6.2.2.2 on Unbuntu 19.04
Prasanjith
 
Posts: 11
Joined: Thu Apr 25, 2019 8:33 pm

Re: Transposing data in a column to multiple rows

Postby Prasanjith » Sat Apr 27, 2019 9:47 am

any upadates???
libreoffice 6.2.2.2 on Unbuntu 19.04
Prasanjith
 
Posts: 11
Joined: Thu Apr 25, 2019 8:33 pm

Re: Transposing data in a column to multiple rows

Postby Prasanjith » Sat Apr 27, 2019 6:51 pm

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...
libreoffice 6.2.2.2 on Unbuntu 19.04
Prasanjith
 
Posts: 11
Joined: Thu Apr 25, 2019 8:33 pm


Return to Calc

Who is online

Users browsing this forum: Google [Bot], Majestic-12 [Bot] and 30 guests