Previous Topic Next Topic

Excel-format exported file to proper col/rows

Closed
Jjudy - Jan 24, 2011 at 06:00 PM
rizvisa1
Posts
4479
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 :(

2 replies

Answer 1 / 2
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
 769
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
0
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.
0
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
 769
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
0