Previous Topic Next Topic

Help required in MS Excel

Solved/Closed
eskagsms
Posts
24
Registration date
Wednesday February 9, 2011
Status
Member
Last seen
May 30, 2012
 - Apr 11, 2011 at 06:13 AM
eskagsms
Posts
24
Registration date
Wednesday February 9, 2011
Status
Member
Last seen
May 30, 2012
 - Apr 16, 2011 at 01:53 AM
Hello,

I have a list of text strings in rows separated by blank rows. For example:
Row 1: Name of the publication
Row 2: Authors
Row 3: Organization
Row 4: Text of the publication
Row 5: Blank

This is repeated several times in a sheet.

I want this to appear in columns. For example:

Column 1: Name of the Publication
Column 2: Authors
Column 3: Organization
Column 4: Text of the publication

Please can anyone help?

Regards,

Eskagsms

7 replies

Answer 1 / 7
RWomanizer
Posts
365
Registration date
Monday February 7, 2011
Status
Contributor
Last seen
September 30, 2013
 120
Apr 11, 2011 at 06:23 AM
Copy the Data:

now right click where you want to paste.

select "Paste Special"
Check "Transpose" in bottom right of the pop-up and then click OK.
0
Answer 2 / 7
eskagsms
Posts
24
Registration date
Wednesday February 9, 2011
Status
Member
Last seen
May 30, 2012
Apr 11, 2011 at 06:38 AM
Hello, thanks for your help. However, I have to select each dataset separately and paste special to transpose each time. Is there any way this can be automated?

Regards,
EskagSMS
0
Answer 3 / 7
RWomanizer
Posts
365
Registration date
Monday February 7, 2011
Status
Contributor
Last seen
September 30, 2013
 120
Apr 11, 2011 at 07:06 AM
Can you share some part of your data, to help you in more efficient way.
0
Answer 4 / 7
eskagsms
Posts
24
Registration date
Wednesday February 9, 2011
Status
Member
Last seen
May 30, 2012
Apr 12, 2011 at 02:03 AM
Hi, I have uploaded the file at https://authentification.site/files/27918458/Sample.xls

Thanks,

EskagSMS
0

Didn't find the answer you are looking for?

Ask a question
Answer 5 / 7
Kenf
Apr 15, 2011 at 02:07 PM
You can reference the cells and use an if statement to determine the top of the block, in this case the top of the block is under two blank lines
(note I started in the second segment since your first line didn't start with two blank rows, so you have to do this manually or add a blank row to the top)
So if A6 and A7 are black we pick the top line
In cell B8 enter =IF(AND(A7="",A6=""),A8,"")
In cell C8 enter =IF(AND(A7="",A6=""),A9,"")
In cell D8 enter =IF(AND(A7="",A6=""),A10,"")
for as many cells as you need for each cell we move the right we move down one cell B8 to A8, C8 to A9, D8 to A10
Once your formulas are entered copy them down to the bottom of your data
You will have a whole bunch of blank lines, so copy Columns B to F( or where ever your sheet stops)
Paste special in a new sheet, You can then sort to remove the spaces
If you need to keep them in the same order, after you paste them add a column with numbers and sort out the spaces then sort by number.

If you do this a lot you may want to use the indirect function and maybe macros.
0
Answer 6 / 7
Kenf
Apr 15, 2011 at 02:12 PM
I posted sample
http://www.speedyshare.com/files/27985686/Copy_of_Sample.xls
0
Answer 7 / 7
eskagsms
Posts
24
Registration date
Wednesday February 9, 2011
Status
Member
Last seen
May 30, 2012
Apr 16, 2011 at 01:53 AM
That's working fine. Thanks a lot for your help. Regards. EskagSMS
0