Consulting

Page 1 of 3 1 2 3 LastLast
Results 1 to 20 of 43

Thread: How to analyze big data with Excel

  1. #1
    VBAX Contributor
    Joined
    Jul 2018
    Posts
    127
    Location

    How to analyze big data with Excel

    I have a lot of data but I am not sure how to analyze it with Excel.

    Should I use regressions? Pivot tables? Something else?

    What is the first step in analyzing big data??

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    25,080
    Location
    Power Query and Power Pivot and pivot tables.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3
    VBAX Contributor
    Joined
    Jul 2018
    Posts
    127
    Location
    Hi xld,

    thank you for your answer!

    I have tabular data and my first data table has 1600 rows and 100+ columns.

    In what way should I try to transform my data before analysis? Should it be tabular or non-tabular?

    I am trying to compare cities against each other and find out what city-city combinations is the best.

  4. #4
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    25,080
    Location
    That amount of data is trivial for these tools.

    You should look to denormalise it so that you can pivot the data and get the sort of analysis you require. Power Query can import that data as it is and unpivot it so that it can then be pivoted.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  5. #5
    VBAX Contributor
    Joined
    Jul 2018
    Posts
    127
    Location
    Hi again xld,

    thank you for your reply!

    I have unpivoted my columns using PowerQuery and I now have 3 columns, city name, metric and value.

    How should I proceed?

  6. #6
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    25,080
    Location
    On the Home tab, click the arrow on the Close & Load button (Furthest left) and select Close & Load To. In the popup dialog, select connection only and Add to Datamodel. Exit back to Excel.

    Now in Excel, go to Insert>Pivot Tables, select 'Use this workbook's Data Model' from the popup dialog, you should see your query there which you can now use in a pivot table.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  7. #7
    VBAX Contributor
    Joined
    Jul 2018
    Posts
    127
    Location
    Hi xld,

    thank you for your instructions, I now have a data model with my data in it but when I click on metric in my field list I get a list of all metrics.

    Is it possible to single out them one by one in the way my data started out like?

  8. #8
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    25,080
    Location
    Put the metric in the rows pane not the value pane.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  9. #9
    VBAX Contributor
    Joined
    Jul 2018
    Posts
    127
    Location
    Hi xld,

    I have followed your instructions but I am not sure on how the unpivoted data when pivoted in a pivottable is better?

    After I have my datamodel should I create measures to analyze the data in PowerPivot? Or try to use regression on the data?

  10. #10
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    25,080
    Location
    What you have is a set of raw data that you can now analyse. How you analyse it I cannot say, I do not know the data or what you are trying to achieve.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  11. #11
    VBAX Contributor
    Joined
    Jul 2018
    Posts
    127
    Location
    Hi xld,

    thank you for your reply. I don't know how I should analyze my data.

    I am comparing cities against each other on a lot of different metrics, more is better and there is a min and a max score.

    Say I have cities A, B and C.

    Then I am comparing City A vs City A. City A vs City B. City A vs City C.

    City B vs City A, City B vs City B. City B vs City C.

    City C vs City A. City C vs City B. City C vs City C.

    I want to find the combination of highest score between all combinations of the different cities.

  12. #12
    VBAX Contributor
    Joined
    Jul 2018
    Posts
    127
    Location
    Any suggestions on how I can analyze data that is as in the above post??

  13. #13
    Knowledge Base Approver VBAX Guru
    Joined
    Apr 2012
    Posts
    4,500
    Quote Originally Posted by waimea View Post
    Any suggestions on how I can analyze data that is as in the above post??
    You can't since these are only strings.
    Please post a sample Excel workbook.

  14. #14
    VBAX Wizard
    Joined
    Apr 2007
    Posts
    6,602
    Location
    Quote Originally Posted by waimea View Post
    Any suggestions on how I can analyze data that is as in the above post??


    Why compare a city against itself?

    If you have 10 cities (C01 - C10) and 15 metrics (M01 - M15) to compare, then I'd figure you'll have 15 x (10 x 9) / 2 = 675 'compares' comparing each metric for each city pair

    e.g.

    C01 - C02 - M01, C01 - C02 - M02, … C01 - C02 - M15
    C01 - C03 - M01, C01 - C03 - M02, … C01 - C03 - M15

    ….




    C09 - C10 - M01, C09 - C10 - M02, … C09 - C10 - M15


    Sounds like you started with a 10 row, 16 column array of data using my made up numbers above ??
    Last edited by Paul_Hossler; 04-13-2019 at 06:40 PM.
    Paul

    ------------------------------------------------------------------------------------------------------------------------
    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s)
    (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  15. #15
    VBAX Contributor
    Joined
    Jul 2018
    Posts
    127
    Location
    Hi Paul,

    thank you for your reply! I am comparing a city against itself only because I loop trough "all items" in two comboboxes.

    There is no value in comparing city A with city A.

    I am not sure what I am starting with at the moment. I will have to look at my file again.

    I will post a sample workbook. Do you have any suggestions on how I should analyze the "compares"?

  16. #16
    VBAX Wizard
    Joined
    Apr 2007
    Posts
    6,602
    Location
    As an CONCEPTUAL EXAMPLE .... … you could look at something like this

    10 cities (GREEN), 3 metrics (YELLOW)with each metric having a weighting factor (ORANGE)

    The BLUE are just random numbers 0-100 but could be your raw scoring of City-X against City-Y for Metric-M (Col A - Col K)

    Each raw score is weighted (Col M - Col N) in Col P - Col W

    Weighted scores are summed Col Y - Col Z

    The weighted summary is sorted High to Low in Col AB - Col AC

    I used 2 UDFs and array functions

    F9 to recalc and see what happens



    Capture.jpg
    Attached Files Attached Files
    Paul

    ------------------------------------------------------------------------------------------------------------------------
    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s)
    (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  17. #17
    VBAX Contributor
    Joined
    Jul 2018
    Posts
    127
    Location
    Hi Paul,

    thank you for your reply! You are a conceptual example genius!

    I am looking at your workbook right now and I am trying to understand it!

    How did you determine the weights?

  18. #18
    VBAX Wizard
    Joined
    Apr 2007
    Posts
    6,602
    Location
    Quote Originally Posted by waimea View Post
    Hi Paul,

    thank you for your reply! You are a conceptual example genius!

    I am looking at your workbook right now and I am trying to understand it!

    How did you determine the weights?
    I just put in anything for the weights - in real life, you'd have to figure out what each metric was 'worth' in your analysis

    This example determined which City Pair was 'best', but it's probably possible to figure out which City was highest scored
    Paul

    ------------------------------------------------------------------------------------------------------------------------
    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s)
    (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  19. #19
    VBAX Contributor
    Joined
    Jul 2018
    Posts
    127
    Location
    Hi Paul,

    thank you for your reply.

    How would you go about finding the city/cities with the highest score?

  20. #20
    VBAX Wizard
    Joined
    Apr 2007
    Posts
    6,602
    Location
    The 'best' City Pair is in AB2 in my example above. The City01-City02 has a score of 70 for Metric-01

    You could give City01 a 70 and City02 a 70 for Metric-01, and then weight Metrics, and, etc.

    Capture.JPG


    This makes City-04 the 'best' with a composite score of 530.95

    It really all depends on how you weight the metrics and how you choose to determine the 'best'
    Attached Files Attached Files
    Paul

    ------------------------------------------------------------------------------------------------------------------------
    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s)
    (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

Posting Permissions

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