1

I have several fairly good-sized Excel worksheets (indexed by WorksheetID) that essentially amount to flat database tables: rows are the records (indexed by DataRecordID within one worksheet), columns are the fields (indexed by DataFieldID within one worksheet), and the cells contain the field values (Value) for the appropriate records (or are blank).

I'm interested in breaking them apart to (many) records like this so that I can import them into MS Access:

ID | WorksheetID | DataRecordID | DataFieldID | Value

I've already done this manually. I see that it's probably a straightforward matter to write a VBA script to chug through the sheets and (optionally) delete the records in the final table.

My question is: Before I roll up my sleeves and begin coding, is there already a built-in capability in Excel 2010 that I don't even need to write a script to do this?

Community
  • 1
  • 1
John
  • 15,990
  • 10
  • 70
  • 110
  • 1
    Doesn't Access have some kind of normalizer built into it? I've got vague memories of having used one in the '97 version. – Marc B Aug 14 '12 at 17:07
  • @Marc B You're right. Let me check into that. – John Aug 14 '12 at 17:09
  • @Marc B I don't think the normalization feature I saw when I imported the tables is enough. It would let me create a separate table for each field if I wanted, but that's not quite what I need. Good thought, though! – John Aug 14 '12 at 17:23
  • 1
    I can't tell for sure, but think this might help: http://stackoverflow.com/questions/10921791/melt-reshape-in-excel-using-vba – Doug Glancy Aug 14 '12 at 17:40

1 Answers1

0

You don't need a script for this provided the data sources are of consistent layout. The basic process is described (with pictures!) here. However since that was considering a single matrix you will need a few extra steps:

Build in to the DataRecordIDs indication of their worksheet origins. Say with a formula such as =A2&"|WorksheetID" copied down to suit in a column inserted as a (new) ColumnB, for all sheets, assuming your first DataFieldID is in A2.

At Step #4 of the process (Step 2b of 3 in the Wizard) you would need to add the relevant ranges from each sheet individually.

Once you have the results Table you would then need to insert a new ColumnB and apply Text to Columns, Delimited with Other | to ColumnA.

Community
  • 1
  • 1
pnuts
  • 58,317
  • 11
  • 87
  • 139