Help required in MS Excel
Solved/Closed
eskagsms
Posts
24
Registration date
Wednesday 9 February 2011
Status
Member
Last seen
30 May 2012
-
11 Apr 2011 à 06:13
eskagsms Posts 24 Registration date Wednesday 9 February 2011 Status Member Last seen 30 May 2012 - 16 Apr 2011 à 01:53
eskagsms Posts 24 Registration date Wednesday 9 February 2011 Status Member Last seen 30 May 2012 - 16 Apr 2011 à 01:53
Related:
- Help required in MS Excel
- Ms excel mod apk - Download - Spreadsheets
- Arrow keys not working in excel - Guide
- How to change date format in excel - Guide
- Kernel for excel repair - Download - Backup and recovery
- Delete duplicates in excel - Guide
7 responses
RWomanizer
Posts
365
Registration date
Monday 7 February 2011
Status
Contributor
Last seen
30 September 2013
120
11 Apr 2011 à 06:23
11 Apr 2011 à 06:23
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 9 February 2011
Status
Member
Last seen
30 May 2012
11 Apr 2011 à 06:38
11 Apr 2011 à 06:38
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 7 February 2011
Status
Contributor
Last seen
30 September 2013
120
11 Apr 2011 à 07:06
11 Apr 2011 à 07:06
Can you share some part of your data, to help you in more efficient way.
eskagsms
Posts
24
Registration date
Wednesday 9 February 2011
Status
Member
Last seen
30 May 2012
12 Apr 2011 à 02:03
12 Apr 2011 à 02:03
Hi, I have uploaded the file at https://authentification.site/files/27918458/Sample.xls
Thanks,
EskagSMS
Thanks,
EskagSMS
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 9 February 2011
Status
Member
Last seen
30 May 2012
16 Apr 2011 à 01:53
16 Apr 2011 à 01:53
That's working fine. Thanks a lot for your help. Regards. EskagSMS
