1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Data Structure Best Practice

Discussion in 'Ask an Excel Question' started by ellistyle, Jul 16, 2017.

  1. ellistyle

    ellistyle New Member

    Messages:
    16
    Interested in your thoughts on this.
    On a monthly basis I aggregate forecast data for a rolling 12 month period. I am trying to arrive at a data format suits this activity. For simplicity's sake, lets say there is static data (e.g. ustomer name, market, product) and dynamic data (e.g. monthly quantity and price)

    Which strategy would your recommend (or perhaps recommend an alternative):

    1. Add a new set of columns for each period. Month and year.
      • The sheet becomes very wide, but none of the static data is duplicated.
      • The column names will always have to be added to capture the dynamic data
    2. Keep a consistent column structure, and add a year column.
      • The sheets gets very long
      • Static data is duplicated

    I am inclined towards #2 so as to keep a consistent column set forever. But the duplicated static data will create a very large file very quickly.

    What do you think?
  2. vletm

    vletm Well-Known Member

    Messages:
    2,763
    ellistyle
    For me, easier to use if
    ... add new set of row for each period ...
    but ...
    all depends
    what do You really have there ?
    ... how many items of data per period?
    and so on ...
  3. ellistyle

    ellistyle New Member

    Messages:
    16
    In each month, I would likely have 5k rows of data. There are about 15 columns of static data. The remaining columns contain the new monthly input. I didn't mention before that I am analyzing variation between forecast periods (or between the current forecast period and a budget). I would like to append each month's new input into one file. For that reason, I'd like to keep columns consistent (instead of adding a new column each rolling period).
  4. vletm

    vletm Well-Known Member

    Messages:
    2,763
    Please, sample file ... no need to have 5k rows ... 500 is enough!
    with information,
    what You really would like to get / to do...
    sample results are bonus ...
  5. ellistyle

    ellistyle New Member

    Messages:
    16
    I didn't give you 500 lines (It would take awhile to anonymize/cleanse my current data for public consumption).

    The 2 tabs show the 2 strategies--both with the same data input across 2 forecast periods.

    In the add columns method, as the forecast period rolls, I would need to add columns to capture that month's new data.

    In the add rows method, there would be 2x as many rows, but always the same column set.

    • I need a means to compare variations between forecast periods.
    • I would like to capture all of the data in one sheet and pivot from there.
    • All previous input is kept as a reference
    Thanks for your help. I appreciate hearing how others have likely handled a similar situation across periods of reporting.

    Attached Files:

  6. Hui

    Hui Excel Ninja Staff Member

    Messages:
    10,606
    2
    But I would also question why you need 15 columns of static data
    You should only need 4
    Date
    Cost code
    Description
    Actual/Budget

    Yes sometimes you can have sub-categories of the cost code
  7. ellistyle

    ellistyle New Member

    Messages:
    16
    Do you mean let the static data drive from a single Customer code? You are right--the static data can be reduced. I am not really concerned about the structure of that.

    The advice I am seeking is really about the most efficient way to capture the data across years.

    I think option number 2 it is.

    Thanks!
  8. Hui

    Hui Excel Ninja Staff Member

    Messages:
    10,606
    2 gives you so much more flexibility
  9. vletm

    vletm Well-Known Member

    Messages:
    2,763
    ellistyle - as I tried to write:
    with information,
    what You really would like to get / to do...
    sample results are bonus ...
    > Two part numbers ..
    > If You would like to use Pivot, then 'data' should be as it needs - with dates.
    > I didn't get idea with those samples, what do You would like to get, sorry.

Share This Page