Menu
  1. Accueil
  2. Tech Forum
  3. Office Software
  4. Excel

Help required in MS Excel [Solved/Closed]

Report
Posts
24
Registration date
Wednesday February 9, 2011
Status
Member
Last seen
May 30, 2012
 -
Posts
24
Registration date
Wednesday February 9, 2011
Status
Member
Last seen
May 30, 2012
 -
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

See more

7 replies

Answer 1 / 7
Posts
368
Registration date
Monday February 7, 2011
Status
Contributor
Last seen
September 30, 2013
 120
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.
Answer 2 / 7
Posts
24
Registration date
Wednesday February 9, 2011
Status
Member
Last seen
May 30, 2012
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
Answer 3 / 7
Posts
368
Registration date
Monday February 7, 2011
Status
Contributor
Last seen
September 30, 2013
 120
Can you share some part of your data, to help you in more efficient way.
Answer 4 / 7
Posts
24
Registration date
Wednesday February 9, 2011
Status
Member
Last seen
May 30, 2012
Hi, I have uploaded the file at https://authentification.site/files/27918458/Sample.xls

Thanks,

EskagSMS
Answer 5 / 7

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.
Answer 6 / 7

I posted sample
http://www.speedyshare.com/files/27985686/Copy_of_Sample.xls
Answer 7 / 7
Posts
24
Registration date
Wednesday February 9, 2011
Status
Member
Last seen
May 30, 2012
That's working fine. Thanks a lot for your help. Regards. EskagSMS
Ask a question