Forum Discussion
How to group rows of data with text
Hi Jason,
Most logical for will be to use PivotTable. Not sure what's wrong with formatting - I played a bit, see in Sheet2. In you sample data i change addresses - they are different for every record, i guess one invoice shall be on one address.
Prime/Sub account - yes, it shall be ignored or repeated in row data for each record. Another point you have mix of numbers and text in same column(s), desirably to have in one format.
Another option is to transform the range with Get&Transform (Excel 2016) or PowerPivot (previous versions). Not sure if you familiar with it, just in case make a sample in next sheet. In that case all transformations could be done using this tool.
Hi Jason,
Most logical for will be to use PivotTable. Not sure what's wrong with formatting - I played a bit, see in Sheet2. In you sample data i change addresses - they are different for every record, i guess one invoice shall be on one address.
Prime/Sub account - yes, it shall be ignored or repeated in row data for each record. Another point you have mix of numbers and text in same column(s), desirably to have in one format.
Another option is to transform the range with Get&Transform (Excel 2016) or PowerPivot (previous versions). Not sure if you familiar with it, just in case make a sample in next sheet. In that case all transformations could be done using this tool.
Hi Sergei,
Thank you for your reply. The pivot table can work, but the issue arrises when I try to creat the pivot table with more than 700,000 lines of data, it comes up with a message saying it ran out of memory etc... so I could break the excel file in half and try the pivot table... I was hoping that there would be a way to use the consolidate feature and have it pull down the text cells... Thanks again for your help.
Jason
Hi Jason,
On which version of Excel are you? If on 2010 or later you may separate your files with source records and with pivot table. With that few millions of row shall not be a big issue. Here, for example, 50 millions of rows are analazing in Pivot Table http://www.masterdataanalysis.com/ms-excel/analyzing-50-million-records-excel/
Idea is to load your source records in first file into the data model of the second. Using Power Query (aka Get & Transform in Excel 2016), even without any transformation. Just two clicks: Get data from file and after that Load to data model.
After that create PivotTable within second file.
If make some note complex transformations within the Power Query (Group by Invoices) you don't need the PivotTable at all, Power Query returns you resulting table. This option is more preferable.
In more details that's for exmple here
https://www.youtube.com/watch?v=KLQqGtYydUc
However, consolidate also could work. Since you consolidate data for same invoices you shall insert column with invoices numbers right before columns with figures to sum (move or copy the column to here).
Next, copy the sheet with you data (select sheet tab and copy). In new sheet select entire range (or even entire sheet), in Ribbon Data->Remove Duplicates. Select column with invoices numbers to remove duplicates.
Next, in second sheet select names of your columns starting with invoices numbers till end of all sums (last 6 columns, in yellow in attached file). Click Consolidate, SUM as aggregation, entire range for same columns in first sheet (green in sample) as reference, and, IMPORTANT, both top row and left column as labels. Okay now.
See attached in Sheet3.
Forgot about Prime and Sub accounts. In previous example they will be ignored. If you need to take them into account consolidate on helper column (instead of invoice numbers) which you shall create before sums, like =Invoice & Prime Acc & Sub Acc.
In general any column could be combined on which you'd like to consolidate. Remove duplicates also is to be done on that column.
And do everything on copy of your file.
