Manage multivalued attributes

Hi there,

I've got a task to design a dimensional data model and group data accordingly in excel. While doing that, I discovered that one of the dimensions consisted of multivalued attributes (i.e. one-to-many relationship), which had to be converted into multiple one-to-one relationships in excel. Attached is the file with sample data, where Sheet 1 is the current situation, and Sheet 2 is what I would like to achieve. Please help me out. Thanks a lot.

(NOTE: The actual workbook consists of almost a million rows, so I would really appreciate it if the codes do not have many repetitive processes that need to be written)

I know you have a solution, but, just for fun, another option:

Please Login or Register  to view this content.

Some differences to note:
1. John's code operates on the Active Sheet and overwrites it
2. My code writes the output to a new sheet which must pre-exist (in this case, Sheet2)
3. I've added a timer routine so you can compare times. Bit of a movable feast depending on the foibles of the machine.
Sometimes one is faster, then the other ... but not a lot in it
4. The timer will not work in 64 bit installations of Excel


Regards, TMS

You're welcome. Thanks for the rep.

This version caters for the scenario where there is a Request ID but no associated Linked Services.

Please Login or Register  to view this content.

Regards, TMS