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
eskagsms Posts 24 Registration date Wednesday February 9, 2011 Status Member Last seen May 30, 2012 - Apr 16, 2011 at 01:53 AM
Related:
- Help required in MS Excel
- Ms excel free download - Download - Spreadsheets
- Number to words in excel - Guide
- How to take screenshot in excel - Guide
- Dd-mm-yyyy format in excel - Guide
- Excel marksheet - Guide
7 responses
RWomanizer
Posts
365
Registration date
Monday February 7, 2011
Status
Contributor
Last seen
September 30, 2013
120
Apr 11, 2011 at 06:23 AM
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.
now right click where you want to paste.
select "Paste Special"
Check "Transpose" in bottom right of the pop-up and then click OK.
eskagsms
Posts
24
Registration date
Wednesday February 9, 2011
Status
Member
Last seen
May 30, 2012
Apr 11, 2011 at 06:38 AM
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
Regards,
EskagSMS
RWomanizer
Posts
365
Registration date
Monday February 7, 2011
Status
Contributor
Last seen
September 30, 2013
120
Apr 11, 2011 at 07:06 AM
Apr 11, 2011 at 07:06 AM
Can you share some part of your data, to help you in more efficient way.
eskagsms
Posts
24
Registration date
Wednesday February 9, 2011
Status
Member
Last seen
May 30, 2012
Apr 12, 2011 at 02:03 AM
Apr 12, 2011 at 02:03 AM
Hi, I have uploaded the file at https://authentification.site/files/27918458/Sample.xls
Thanks,
EskagSMS
Thanks,
EskagSMS
Didn't find the answer you are looking for?
Ask a question
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.
(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.
eskagsms
Posts
24
Registration date
Wednesday February 9, 2011
Status
Member
Last seen
May 30, 2012
Apr 16, 2011 at 01:53 AM
Apr 16, 2011 at 01:53 AM
That's working fine. Thanks a lot for your help. Regards. EskagSMS
