Consolidating Records of a Database


Cant seem to find the appropriate video for this but here is a basic example of what I need to consolidate my database.  Any help appreciated!

Current:                                   Need:

Name     Type                         Name     Type

Joe          A                             Joe          A,C

Joe          C                             Sara        B,C,F

Sara        B                            Bob         B

Sara        C

Sara         F

Bob          B



Selected Answer

I'm sure this can be done without VBA, perhaps using the pivot table in some smart way. The solution offered in the attached workbook does use VBA. The steps involved may appear not less onerous to you but, once they are taken, the result is instantaneous.

  • Copy the code from the 'Consolidate' module to your own workbook's VBA project. You can simply drag it from one project to the other in the Project Explorer window of the VB Editor.
  • Read through the comments in the code and make the required adjustments to the constants at the top.
  • Place the cursor anywhere in the procedure 'Consolidate_Data' and press F5 (or Run -> Run Macro from the toolbar).
  • Done.

In my example the Name and Type columns aren't adjacent. The program copies the contents of the intervening cells from each name's first occurrance. This may or may not be useful to you. If it isn't, make sure that the two significant columsn are adjacent in the source sheet.

Note that the constants NameColumn and TypeColumn must be specified relative to the SourceRange, not the sheet. For example, if the SourceRange is D2:E7 and D is the NameColumn and E the TypeColumn, then NameColumn should be 1 because it is the first column in the specified range, and TypeColumn = 2.


Answer the Question

You must create an account to use the forum. Create an Account or Login