Convert text reports into Excel worksheet databases

Report To Data Assistant in detail

The Report To Data Assistant is a Microsoft Excel add-in by Macro Systems. It is designed to help you convert text reports into Microsoft Excel worksheet databases. For example, if your report looks like the following:

You would be faced with deleting the title rows that repeat every 60-80 lines and all the blank lines. With the Report to Data Assistant, you can easily select the title rows and blank rows and delete them. Then, you can use the data filter features of Excel or the pivot table features to analyze the data.

The Report to Data Assistant can also help you extract rows from a file. When the above report is parsed into Excel, the sales data ends up in column C. With the Report to Data Assistant, you can select all rows with numeric entries in column C. Then you would copy these rows and paste on a new worksheet.

The main menu of the Report to Data Assistant looks like this:

A selection criteria worksheet allows you to store the row selection criteria. The following illustrates such a worksheet:

Row 3 illustrates three conditions that must be met for the row to be included in the selection list per that test. Rows 4 and 5 illustrate simpler test. The following are the many tests that you can use:

• equal to
• not equal to
• contains
• does not contain
• empty
• not empty
• greater than
• greater than or equal to
• less than
• less than or equal to
• begins with
• does not begin with
• ends with
• does not end with
• numeric
• Text
• X or more cell entries
• X or less cell entries
• X cell entries

When the selection process is done, the matching rows are selected. You can copy them to another worksheet via Edit Copy. You can delete them via Edit Delete. If you want to examine them, you can color code them for ease of inspection. Then run the selection again to re-select the rows.

The Report To Data Assistant also has options that allow you to combine multiple rows onto one row and to concatenate multiple cell entries.

See also in "Excel - Data management - merge, convert, delete"

• Address Assistant - Split addresses from one cell into multiple cells.
• Consolidation Assistant - Consolidate data from Excel workbooks or worksheets.
• CSV File Creator - Create a CSV file from Excel workbooks / worksheets.
• Data Assistant for Excel - Make table form data applicable for Excel pivot tables.
• Data Cleaner for Excel - Remove spaces, Change text case, Replace / modify text, etc.
• Data Extraction Assistant for Excel - Extract data from one worksheet to many spreadsheets.
• Data Loader for Excel - Easy loading, processing, and transferring data .
• Filter Assistant - Filter multiple Excel spreadsheet columns at a time.
• Formula Manager - Manage all formulas in Excel worksheets easily.
• Formula Ref Changer and Sheet Index Creator - Change Excel formula reference types.
• List Assistant - Easy way to re-arrange rows in your Excel worksheet.
• Name Splitter - Split names into several Excel cells.
• Number Manager - Fill in the gaps in Excel workbooks.
• Spreadsheet Assistant - Over 200 useful features for MS Excel.
• Text File Importer - Import text files with over a million lines into Excel.
• Text Manager - Change case in Excel, Join text to one cell, Remove spaces, etc..