0

Very often tables are presented in Excel files in a non-normalized form. I have one such table which I wish to take into (somewhat) First Normal Form using either SQL commands in Access or, preferrably, some feature of Excel or Access. This would make the data more tractable in pivot tables and just about any real application.

Here's an example of the type of conversion that I want to produce, the first table being the one to be converted, and the second being the expected result: Normalizing in Excel or Access to 1NF

edit: Let's also say there are enough dateX columns so that doing manual Union queries isn't practical.

I know I could easily do it with VBA or manually appending the columns, if the table weren't big, but I'm interested about the two possible solutions that I mentioned above.

Thanks a lot!

Improve this question

1 Answer 1

Reset to default
2

If you are open to an add-in and you are using Excel 2013 (maybe also 2010), I'd recommend having a look at Microsoft's PowerQuery addin.

This allows you to do very powerful requerying and reshaping of data from many sources including tables in the source workbook.

Improve this answer
4
  • Thanks, I wasn't aware of the software. I'll try it out. Your suggestion was pretty much appropriate. However, I'd like to stick to Office's default features. As I said, this could also be solved with VBA, but I'd rather not go that way since this is a common scenario that I have encountered while not using a computer that I control (and thus being unable to install other software or access my macros).
    – s_a
    Commented Mar 11, 2014 at 23:39
  • 1
    I understand and sympathise with the desire to use native features only. It seems though, that Microsoft are making significant changes to Excel and removing features from Access & adding them to Excel. Quite possibly, you need the "Data Model" feature in Excel and I think that this may now be native though very poorly documented.
    – Julian Knight
    Commented Mar 12, 2014 at 8:23
  • 1
    Here is an article about using the Excel 2013 built-in data model for doing relationships (joins), it may point you in the right direction.
    – Julian Knight
    Commented Mar 12, 2014 at 8:50
  • I wasn't aware of this features either, everyone seems to have focused on the little things when Excel 2013 came out. I'll think about upgrading.
    – s_a
    Commented Mar 12, 2014 at 13:41

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .