Correcting an excel sheet after exporting from crystal report

[excelnewbie34]

see your replies to similar issues and thought of looking for help with you.

I have an excel sheet which is exported from a crystal report. It is product details.
Every product has one row and go up to 36-40 columns. There are around 1000 rows.
After export, the data after the column 30 goes to next row and sometime to a third row as well.

So instead of one row for each product, some product info is on 3 rows, some on 2 rows and some are on 1 row(that is due to lack of data in the last columns).
I am copying and pasting the date from the row below to the last last column of the row above manually to do some analysis.

Is there anything I could do to correct this? I appreciate all the help you could provide.

Thanks

[Admin]

Hi

Welcome to board !!

Care to post a sample workbook with expected results.

[excelnewbie34]

1665
Please see the attached Excel sheet in the post below.

[excelnewbie34]

1665
Please see the attached image.
1666

Thanks for the help.

[Rick Rothstein]

Please see the attached image.
1666

Some questions on the worksheet you posted...

1) You have a section labeled "So it would look like this at the end"... does that mean you do not want the split apart data fixed on the worksheet, but rather, you only want the data you showed in this labeled section to be outputted by itself?

2) What about the data in Row 1 thru 10... is that supposed to be copied, as is, first, and then the output data format you showed in the labeled section would follow it?

3) Does the data you want outputted always start on Row 11?

4) It looks like all the data you want listed in columns is shown in the raw data with the header text in the cell before it (for example, Column E has "Order no:" in it and Column F has the actual order number)... all but the Product Name (Column M)... the cell to the left of it (Column L) does not show the words "Product Name:", rather, it shows the date that goes in the "Expect:Delivery" column... is that correct or did you post a bad sample with the "Product Name:" data column missing?

[excelnewbie34]

Thank you very much for seeing my post.
Please see my comments below.
The uploaded workbook is a representation of my original sheet. When I get the crystal report, it looks like the date from row 1-10.

When I export to excel, I get it as from 11-19.
It should be like row 21-24.
I would be satisfied with this at least as I can delete few columns and add them as column headings as I shown in row 32-33.



Some questions on the worksheet you posted...

1) You have a section labeled "So it would look like this at the end"... does that mean you do not want the split apart data fixed on the worksheet, but rather, you only want the data you showed in this labeled section to be outputted by itself?
Yes, I would like to get the split apart data fixed and the columns with headings such as Expect Delivery etc deleted and appear as headings.

2) What about the data in Row 1 thru 10... is that supposed to be copied, as is, first, and then the output data format you showed in the labeled section would follow it?
No, this was included to show how my original crystal report would look like.
3) Does the data you want outputted always start on Row 11?
No. It actually start on a4. This is a model sheet I prepared to include before and after appearances.
4) It looks like all the data you want listed in columns is shown in the raw data with the header text in the cell before it (for example, Column E has "Order no:" in it and Column F has the actual order number)... all but the Product Name (Column M)... the cell to the left of it (Column L) does not show the words "Product Name:", rather, it shows the date that goes in the "Expect:Delivery" column... is that correct or did you post a bad sample with the "Product Name:" data column missing?

You are correct. It is a bad sample.On the original sheet, the product number column is absent.
So I worked on it a bit more, and changed the data. New file is uploaded now. This is the original file with sensitive information modified.This is the file I receive after I export crystal report to Excel. Similar to data from the previous sheet row 11-19.

On the new sheet(original), you can see all except product 3445656(row 22, 23) has 3 rows. There will be data like this in the sheet. Not all data will have 3 rows. I think this complicates little bit further.

[excelnewbie34]

I added a new workbook with sheets showing end results.

Sheet 3 shows how the result should look like.
If this process is tough and time consuming for a beginner like me, sheet 2 will be what I would look for.

Thanks for your patience with me.