Sorting exported data from crystal report

excelnewbie34

New Member
Joined
Apr 9, 2014
Messages
7
Hi,

After I export data from crystal report it appears to have lost its original position and some data go under different columns. I have no access to editing the original template.
I have columns starting from A to AK on the sheet. An example is given below. I use Excel 2007. The information statistics is over a month and analysis is difficult due to the export issue.

1600123Name 1BUY DATE01/01/2011
1532345Name 2BUY DATE02/01/2012
Mango 3AppleMango 4
2134546Name 3BUY DATE03/01/2011
Mango 3Apple2Mango 4Mango 3
123487Name4BUY DATE03/02/2011
Mango 3Apple3Mango 4
345612Name5BUY DATE03/03/2011
Mango 3Apple4Mango 4
120978Name6BUY DATE02/03/2011
Mango 3Apple5Mango 4
34258Name 7BUY DATE01/03/2011
Mango 3Apple 6Mango 4
65783Name 8BUY DATE04/01/2011
34298Name 9BUY DATE01/04/2011
Mango 3Apple 7Mango 4
23490name 10BUY DATE02/04/2011
Mango 3Apple 8Mango 4
342Name 11BUY DATE01/06/2011
45623Name 12BUY DATE01/03/2011
45672Name 13BUY DATE06/01/2011
3450978Name 14BUY DATE07/06/2011
3459067Name 15BUY DATE04/02/2011



<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>
Each of these column has other data coming in between.
1st column should only contain numbers.
Second column should only contain Names.
3rd column(Buy date) is a heading of the 4th column. It also has another column data (mango 4) coming in between.
Ideally the data in the 3rd row should come after the 4th column in the second row and likewise.
Some rows do not have that information(mango 3, apple 4 and mango 4) and it is absent as you can see in the last 4 rows which fine.
Is there any way to clean up this and move all the rows coloured in green to come directly right of the data above it?
Like this.

1532345Name 2BUY DATE02/01/2012Mango 3AppleMango 4
2134546Name 3BUY DATE03/01/2011Mango 3Apple2Mango 4Mango 3

<colgroup><col><col><col><col><col span="4"></colgroup><tbody>
</tbody>

Thanks in advance.
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Forum statistics

Threads
1,213,536
Messages
6,114,202
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top