none
VBA code to create table instead of power query/pivot RRS feed

  • Question

  • I have an excel sheet that I need to open in Excel for macs, so I cannot use power query or power pivot to create an transposed table of values. Could I easily use VBA in a for loop to create the following result set?

    Source table (snippet)

    Week 1 0500-0700 Breakfast Soyrizo
    Week 1 0500-0700 Breakfast Brown Rice
    Week 1 0500-0700 Breakfast Egg
    Week 1 0500-0700 Breakfast Oats
    Week 1 0500-0700 Breakfast Blueberries
    Week 1 0900-1000 Morning Meal Almond Oil
    Week 1 0900-1000 Morning Meal Apple
    Week 1 0900-1000 Morning Meal Lemon
    Week 1 0900-1000 Morning Meal Kiwi
    Week 1 0900-1000 Morning Meal
    Week 1 0900-1000 Morning Meal
    Week 1 0900-1000 Morning Meal
    Week 1 1200-1300 Lunch Beef-Less Ground Beef
    Week 1 1200-1300 Lunch Whole Wheat Wrap
    Week 1 1200-1300 Lunch Mozzerella 
    Week 1 1200-1300 Lunch Arugala
    Week 1 1200-1300 Lunch Peppers
    Week 1 1200-1300 Lunch Onions
    Week 1 1500-1600 Afternoon Meal Wild Salmon
    Week 1 1500-1600 Afternoon Meal Asparagus
    Week 1 1500-1600 Afternoon Meal Sweet Potatoes
    Week 1 1500-1600 Afternoon Meal Parmesan Cheese (Light)
    Week 1 1700-1800 Dinner Cucumber
    Week 1 1700-1800 Dinner Arugala
    Week 1 1700-1800 Dinner Kale
    Week 1 1700-1800 Dinner Onions
    Week 1 1700-1800 Dinner Peppers
    Week 1 1700-1800 Dinner Corn
    Week 1 1700-1800 Dinner Quinoa
    Week 1 1700-1800 Dinner Tofu
    Week 1 1900 PM Snack Mango

    There will be multiple weeks, with the same meals, where there can be different foods each week for each meal

    The result set should be Weeks as columns (as many weeks as shown in source), with meals listed as row group and foods grouped in each week column for each meal

    I had this done using power query, but it wouldnt work on a mac, so trying another route

    Tuesday, May 29, 2018 10:29 PM

Answers

All replies

  • Hello JHarding08,

    I don't have a Mac for testing, please check if below document could work for mac.

    https://1drv.ms/x/s!ArC0gnwxLv5qhxqYENfKrMzZP8on

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Wednesday, May 30, 2018 10:52 AM
  • Hello JHarding08,

    What's the state of the current thread? Could it run in Mac? Please feel free to let us know if the issue has any further progress.

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, June 1, 2018 7:37 AM
  • Used VBA for the solution. Thank you
    • Marked as answer by JHarding08 Tuesday, June 26, 2018 6:35 PM
    Tuesday, June 26, 2018 6:35 PM