Help required in MS Excel
Solved/Closed
eskagsms
eskagsms
- Posts
- 24
- Registration date
- Wednesday February 9, 2011
- Status
- Member
- Last seen
- May 30, 2012
eskagsms
- Posts
- 24
- Registration date
- Wednesday February 9, 2011
- Status
- Member
- Last seen
- May 30, 2012
Related:
- Help required in MS Excel
- Require help in excel ✓ - Forum - Excel
- Excel Formula Help Required. ✓ - Forum - Excel
- Excell Macro Help Required ✓ - Forum - Excel
- Transfer data from one excel worksheet to another automatically - Guide
- How to change date format in excel - Guide
7 replies
RWomanizer
Apr 11, 2011 at 06:23 AM
- Posts
- 365
- Registration date
- Monday February 7, 2011
- Status
- Contributor
- Last seen
- September 30, 2013
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
Apr 11, 2011 at 06:38 AM
- 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
Regards,
EskagSMS
RWomanizer
Apr 11, 2011 at 07:06 AM
- Posts
- 365
- Registration date
- Monday February 7, 2011
- Status
- Contributor
- Last seen
- September 30, 2013
Apr 11, 2011 at 07:06 AM
Can you share some part of your data, to help you in more efficient way.
eskagsms
Apr 12, 2011 at 02:03 AM
- 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
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
Apr 16, 2011 at 01:53 AM
- 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