+ Reply to Thread
Results 1 to 5 of 5

Normalizing data

  1. #1
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,908

    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
    206

    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
    2016
    Posts
    21

    Re: Normalizing data

    Extremely helpful!!! Thank you!!!

  4. #4
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,908

    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

  5. #5
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    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)

Similar Threads

  1. [SOLVED] Help with normalizing data
    By Spyderwoman in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 01-22-2016, 11:29 AM
  2. [SOLVED] Normalizing The Data
    By zanshin777 in forum Excel Charting & Pivots
    Replies: 7
    Last Post: 09-15-2015, 07:47 AM
  3. Normalizing data
    By jviola in forum Excel General
    Replies: 5
    Last Post: 06-07-2013, 10:05 AM
  4. Normalizing Data
    By folkda in forum Hello..Introduce yourself
    Replies: 1
    Last Post: 03-11-2013, 03:53 PM
  5. normalizing data
    By 4gokycats in forum Excel General
    Replies: 2
    Last Post: 03-26-2005, 11:06 PM

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