+ Reply to Thread
Results 1 to 6 of 6

Normalizing data

  1. #1
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    2013 / 2016 / 365
    Posts
    6,436

    Normalizing data

    People frequently enter data into excel in the format they expect to see a report in. In other words, they do things like put dates in columns and add a new column for each day. While this makes data entry easy, it pretty much limits the report ot one view of the data. Getting things like model sales by month or weeks becomes an exercise in untangling the data.

    The attached is a brief tutorial on what normalized data is, and has a utility that should work in most cases to normalize data that is entered in tabular format.
    Attached Files Attached Files
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

  2. #2
    Forum Contributor
    Join Date
    01-05-2017
    Location
    New York
    MS-Off Ver
    Office 2016
    Posts
    204

    Re: Normalizing data

    This is great! Really helpful! Thanks for sharing!

  3. #3
    Registered User
    Join Date
    06-06-2018
    Location
    London, ON
    MS-Off Ver
    2013
    Posts
    12

    Re: Normalizing data

    Extremely helpful!!! Thank you!!!

  4. #4
    Registered User
    Join Date
    10-29-2017
    Location
    Delhi
    MS-Off Ver
    10
    Posts
    27

    Re: Normalizing data

    Thanks for sharing the very informative tips.

  5. #5
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    2013 / 2016 / 365
    Posts
    6,436

    Re: Normalizing data

    Here is the same routine except there is an update to the process. It is easier to use this spreadsheet as a stand-alone utility rather than attempt to integrate it into your spreadsheet. Chances are you are using it as a one-time conversion anyway. I also added a button to run the macro.
    Attached Files Attached Files

  6. #6
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2019
    Posts
    4,995

    Re: Normalizing data

    It's so simple to do this using Power Query (Get & Transform Data):

    Please Login or Register  to view this content.
    Worked example:
    Please Login or Register  to view this content.
    let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source

    If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE

    Walking the tightrope between genius and eejit...

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1