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
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 :(
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:
- Excel-format exported file to proper col/rows
- Windows 10 iso file download 64-bit - Download - Windows
- How to change date format in excel - Guide
- Marksheet format in excel - Guide
- Format factory - Download - Other
- How to open an excel file in notepad - Guide
2 responses
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Jan 27, 2011 at 08:55 AM
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
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
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.
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
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
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