Dec 23

I recently came across a puzzeling scenario which required a flavor of denormalization of data in an Excel sheet, and while it doesn't fit the traditional definition of normalization/denormalization, it's still not a bad way in which to view the problem.

In essence, the dataset that I was dealing with contained a unique value, or "key", in the first column, and a series of associated related data in subsequent columns, on a row by row basis.  The dataset represented a one-to-many relationship between records in two disparate systems where the first column represented a defect ID, and all subsequent columns contained incident IDs.

The goal, in this exercise, was simply to produce a denormalize representation of the same data listed the entire dataset in only two columns - providing a distinct, or one-to-one relationship between each defect ID, and an incident ID, where the defect ID was repeated for each related incident ID

A fair amount of exploration into Excel's pivots, transpose, and standard set of functions left me empty-handed in solving the problem.  In the end, leveraging a VBA script as the basis for I've provided here did solve the problem.  This script will iterate through each cell, starting with the second column, on a row by row basis.  The first column, or key, is preserved while the data in each cell is pasted into the second column, row by row.  The procedure then moves to the second row of source data, preserves the key, and so on.  Once each row of source data is denormalized, the procedure will fill-down the key data in order to create the one-to-one relationship.

So, a table that looks like this:

K1
D11
D12
D13
K2
D21
D22
D23
K3
D31
D32
D33

Is translated thusly:

K1
D11
K1
D12
K1
D13
K2
D21
K2
D22
K2
D23
K3
D31
K3
D32
K3
D33

The procedure works equally well with variable row length data.  Note in line 7, the procedure will only handle data up to column "dk".  This can be expanded, or reduced as needed.

And following below, is the macro which produces the results described above:

Sub DenormalizeKeysToData()
Dim i As Long
LastRow = Range("a65536").End(xlUp).Row
For i = 2 To LastRow
Range("a" & i & ":a" & i).Copy
Range("a" & Range("b65536").End(xlUp).Row + 1).PasteSpecial xlValues
Range("b" & i & ":dk" & i).Copy
Range("b" & Range("b65536").End(xlUp).Row + 1).PasteSpecial xlValues, Transpose:=True
Next i
Rows("2:" & LastRow).Delete
Application.CutCopyMode = False
For Each Cell In Range("a2:a" & ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row)
If Cell.Value = "" Then Cell.Value = Cell.Offset(-1, 0).Value
Next Cell
Range("a1").Activate
MsgBox ("Finished")
End Sub

Hope this saves someone a lot of time and energy...

Posted by Adam KrauseGo slimTweet MeShort URL


Strict Standards: Declaration of serendipity_event_s9ymarkup::event_hook() should be compatible with serendipity_event::event_hook($event, &$bag, &$eventData, $addData = NULL) in /home1/pigslips/public_html/s9y/plugins/serendipity_event_s9ymarkup/serendipity_event_s9ymarkup.php on line 146

Strict Standards: Declaration of serendipity_event_s9ymarkup::uninstall() should be compatible with serendipity_plugin::uninstall(&$propbag) in /home1/pigslips/public_html/s9y/plugins/serendipity_event_s9ymarkup/serendipity_event_s9ymarkup.php on line 146

Strict Standards: Declaration of serendipity_event_emoticate::event_hook() should be compatible with serendipity_event::event_hook($event, &$bag, &$eventData, $addData = NULL) in /home1/pigslips/public_html/s9y/plugins/serendipity_event_emoticate/serendipity_event_emoticate.php on line 204

Strict Standards: Declaration of serendipity_event_emoticate::uninstall() should be compatible with serendipity_plugin::uninstall(&$propbag) in /home1/pigslips/public_html/s9y/plugins/serendipity_event_emoticate/serendipity_event_emoticate.php on line 204

Strict Standards: Declaration of serendipity_event_nl2br::event_hook() should be compatible with serendipity_event::event_hook($event, &$bag, &$eventData, $addData = NULL) in /home1/pigslips/public_html/s9y/plugins/serendipity_event_nl2br/serendipity_event_nl2br.php on line 395

Strict Standards: Declaration of serendipity_event_nl2br::uninstall() should be compatible with serendipity_plugin::uninstall(&$propbag) in /home1/pigslips/public_html/s9y/plugins/serendipity_event_nl2br/serendipity_event_nl2br.php on line 395

Strict Standards: Declaration of serendipity_event_browsercompatibility::event_hook() should be compatible with serendipity_event::event_hook($event, &$bag, &$eventData, $addData = NULL) in /home1/pigslips/public_html/s9y/plugins/serendipity_event_browsercompatibility/serendipity_event_browsercompatibility.php on line 80

Strict Standards: Declaration of serendipity_event_spartacus::event_hook() should be compatible with serendipity_event::event_hook($event, &$bag, &$eventData, $addData = NULL) in /home1/pigslips/public_html/s9y/plugins/serendipity_event_spartacus/serendipity_event_spartacus.php on line 1183

Strict Standards: Declaration of serendipity_event_imageselectorplus::event_hook() should be compatible with serendipity_event::event_hook($event, &$bag, &$eventData, $addData = NULL) in /home1/pigslips/public_html/s9y/plugins/serendipity_event_imageselectorplus/serendipity_event_imageselectorplus.php on line 1105

Strict Standards: Declaration of serendipity_event_imageselectorplus::uninstall() should be compatible with serendipity_plugin::uninstall(&$propbag) in /home1/pigslips/public_html/s9y/plugins/serendipity_event_imageselectorplus/serendipity_event_imageselectorplus.php on line 1105

Strict Standards: Declaration of serendipity_event_sidebarlogin::event_hook() should be compatible with serendipity_event::event_hook($event, &$bag, &$eventData, $addData = NULL) in /home1/pigslips/public_html/s9y/plugins/serendipity_plugin_sidebarlogin/serendipity_event_sidebarlogin.php on line 148

Strict Standards: Declaration of serendipity_event_popfetcher::event_hook() should be compatible with serendipity_event::event_hook($event, &$bag, &$eventData, $addData = NULL) in /home1/pigslips/public_html/s9y/plugins/serendipity_event_popfetcher/serendipity_event_popfetcher.php on line 1426

Strict Standards: Declaration of serendipity_event_lightbox::event_hook() should be compatible with serendipity_event::event_hook($event, &$bag, &$eventData, $addData = NULL) in /home1/pigslips/public_html/s9y/plugins/serendipity_event_lightbox/serendipity_event_lightbox.php on line 281

Strict Standards: Declaration of serendipity_event_lightbox::uninstall() should be compatible with serendipity_plugin::uninstall(&$propbag) in /home1/pigslips/public_html/s9y/plugins/serendipity_event_lightbox/serendipity_event_lightbox.php on line 281

Strict Standards: Declaration of serendipity_event_tinymce::event_hook() should be compatible with serendipity_event::event_hook($event, &$bag, &$eventData, $addData = NULL) in /home1/pigslips/public_html/s9y/plugins/serendipity_event_tinymce/serendipity_event_tinymce.php on line 291

Strict Standards: Declaration of serendipity_event_tinybrowser::event_hook() should be compatible with serendipity_event::event_hook($event, &$bag, &$eventData, $addData = NULL) in /home1/pigslips/public_html/s9y/plugins/serendipity_event_tinybrowser/serendipity_event_tinybrowser.php on line 150

Strict Standards: Declaration of serendipity_event_prettify::event_hook() should be compatible with serendipity_event::event_hook($event, &$bag, &$eventData, $addData = NULL) in /home1/pigslips/public_html/s9y/plugins/serendipity_event_prettify/serendipity_event_prettify.php on line 245

Strict Standards: Declaration of serendipity_event_xmlrpc::event_hook() should be compatible with serendipity_event::event_hook($event, &$bag, &$eventData, $addData = NULL) in /home1/pigslips/public_html/s9y/plugins/serendipity_event_xmlrpc/serendipity_event_xmlrpc.php on line 160

Strict Standards: Declaration of serendipity_event_podcast::event_hook() should be compatible with serendipity_event::event_hook($event, &$bag, &$eventData, $addData = NULL) in /home1/pigslips/public_html/s9y/plugins/serendipity_event_podcast/serendipity_event_podcast.php on line 939

Strict Standards: Non-static method TwitterPluginFileAccess::get_permaplugin_path() should not be called statically, assuming $this from incompatible context in /home1/pigslips/public_html/s9y/plugins/serendipity_plugin_twitter/serendipity_event_twitter.php on line 1554

Strict Standards: Non-static method TwitterPluginDbAccess::load_short_urls() should not be called statically, assuming $this from incompatible context in /home1/pigslips/public_html/s9y/plugins/serendipity_plugin_twitter/serendipity_event_twitter.php on line 1518