Consolidating data in access
You consolidate data by category when the source areas do not share the same cell coordinates in their respective worksheets but their data entries do use common row and/or column labels.
When you consolidate by category, you include these identifying labels as part of the source areas.
After you specify all the source areas (including the cells that contain these column and row labels), click the OK button in the Consolidate dialog box to execute the consolidation in the destination area.
Data consolidation is an important process that is used to summarize large quantities of information.
Once the process will be completed, the computer program is going to create a brand new worksheet that is going to set up a summary of all the data belonging to the respective worksheets.
The process does an excellent job at aiding computer users personally and manually record individual data cells from particular reference points, then entering them into various other places using a brand new spreadsheet.
This way, the formatting, re-organization, and re-arranging of huge amounts of information can be considerably simplified.
To use the top row of column labels, select the Top Row check box in the Use Labels In section of the Consolidate dialog box.
To use the left column of row labels, select the Left Column check box in this area.
The following VBA code also can help you consolidate duplicate rows in the worksheet, but at the same time, the original data will be destroyed, you need to backup a copy of the data. Hold down the ALT F11 keys, then it opens the Microsoft Visual Basic for Applications window. Click Insert Sub Combine Rows() 'Update 20130829 Dim Work Rng As Range Dim Dic As Variant Dim arr As Variant On Error Resume Next x Title Id = "Kutoolsfor Excel" Set Work Rng = Application.