Previous Topic Next Topic

Excel-format exported file to proper col/rows

Closed
Jjudy - Jan 24, 2011 at 06:00 PM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Feb 8, 2011 at 02:35 PM
Hello,
This is my problem:
I generate a report from my accounting system and export the data to Excel. As an example, if i generate a sales order report the resulting spreadsheet is very difficult to read because the information is jumbled over an array of cells: 12 columns long, 10 rows down (for one product; or 20 rows for 5 products). There are also no headings. I want to create another worksheet whereby the data can be arrange properly in named columns and each order is listed on 1 row. It is impossible to do a copy/paste because there are over 900 entries (20,000 lines) on each report requiring re-formatting.

Please help :(
Related:

2 responses

Answer 1 / 2
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Jan 27, 2011 at 08:55 AM
Could you please upload a sample EXCEL file WITH sample data, macro, formula , conditional formatting etc on some shared site like https://authentification.site , http://docs.google.com, http://wikisend.com/ , http://www.editgrid.com etc
AND post back here the link to allow better understanding of how it is now and how you foresee. Based on the sample book, could you re-explain your problem too.


Note: your data need not be to be real data but a good representative of how data looks like
Answer 2 / 2
Ashap
Jan 30, 2011 at 07:52 PM
Hi & thank you for your response. I have uploaded a "sample" of the file:

https://authentification.site/files/26593789/help_to_format.xlsx

The first sheet entitled "data" has the dumped info from the accounting system. I have written a comment for each cell as to its description. Also, as you will notice the info is not dumped consistently for each record. For example Order # moves between 3 different columns.
The second sheet is how the end product should look like.

Normally this file contains approximately 1000 records which require formatting.

Thanks again for all your help.
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Feb 8, 2011 at 02:35 PM
It is bit complicated.
From what I see
** Net Order Total ** marks the end of an order

So basically you have to go thru each row and read various values

So I would start reading from line 1 and first look in column A for "Order No."
that is followed by "Customer No. "
Once passed that, I know now i have actual data to work with

Again I have to read line by line and in this case cell by cell to get various values, I would keep on reading for each record till i find a line that has "** Net Order Total **. When I see this line, i know that my order is complete and I have to process next record

Hope this help