+ Reply to Thread
Results 1 to 5 of 5

Use dictionary to populate worksheets

Hybrid View

Justin H2O Use dictionary to populate... 01-03-2020, 09:00 AM
PCI Re: Use dictionary to... 01-03-2020, 11:16 AM
Justin H2O Re: Use dictionary to... 01-03-2020, 01:23 PM
PCI Re: Use dictionary to... 01-03-2020, 02:05 PM
Justin H2O Re: Use dictionary to... 01-07-2020, 11:46 AM
  1. #1
    Registered User
    Join Date
    12-30-2010
    Location
    New Haven, Ct
    MS-Off Ver
    Excel 2016
    Posts
    41

    Use dictionary to populate worksheets

    Hello All! I was hoping someone might be able to give me a hand.

    I am attempting to automate the population of a plant productivity report for my employer. Every Tuesday I receive two tables that I enter manually into multiple spreadsheets in order to derive the report that my bosses are looking for. One of those tables looks like this:

    Bored | Bored | 1435.48 | 328.07
    Cylinders | Cylinders | 1677.9 | 322.97
    EM | EM | 432.52 | 124.38
    Exits | Exits | 2096.17 | 755.53
    Maintenance | Plating | 338.18 | 87.1
    Mortise | Mortise | 1764.85 | 548.08
    Plating | Plating | 394.4 | 108.68
    Polishing | Plating | 424 | 153.63
    Powder Coat | Plating | 120 | 45.5
    Receiving | Shipping | 111.07 | 21.77
    Shipping | Shipping | 592 | 231.98
    Tool & Die | Cylinders | 104 | 25.93
    Waste Water | Plating | 40 | 19.5


    I am currently taking both tables and loading them into a multi-dimensional dictionary without issue. The problem that I'm running into is that I can't figure out a clean and concise way to get the values out of the dictionaries and into the spreadsheets. In certain cases, such as the table above, I need to sum the value columns (columns 3 & 4) when the profit center group (column 2) is the same, prior to populating the spreadsheet.

    Perhaps I'm going about this the completely wrong way? I'm not sure. Hopefully someone has some ideas. I've uploaded a copy of the workbook I have been working on. The tables that I'm using are on Sheet1 and the sheets I'm trying to populate are tabs 2-5. Thanks in advance for any help- it's truly appreciated!

    Justin
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    5,088

    Re: Use dictionary to populate worksheets

    Can you explain how from the raw data in sheet1 columns B to D you populate columns F to H.
    Then which data from sheet1 is uased to fill others sheets?
    Is there a difference between sheets' values, it seems the same ?
    - Battle without fear gives no glory - Just try

  3. #3
    Registered User
    Join Date
    12-30-2010
    Location
    New Haven, Ct
    MS-Off Ver
    Excel 2016
    Posts
    41

    Re: Use dictionary to populate worksheets

    Thanks for your response, PCI.

    The two separate tables on sheet 1 are emailed to me separately. I just stick them into an empty sheet to work with the data, so neither table is derived from the other. As for the remaining sheets, the first two are very similar and the second two are also very similar. There are slight differences depending upon the audience that is receiving the file. I hope this answers your questions.
    Last edited by Justin H2O; 01-07-2020 at 11:48 AM.

  4. #4
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    5,088

    Re: Use dictionary to populate worksheets

    This a part of the answer, now how to populate are tabs 2-5 from data in Sheet1.
    Please give a detail example.

  5. #5
    Registered User
    Join Date
    12-30-2010
    Location
    New Haven, Ct
    MS-Off Ver
    Excel 2016
    Posts
    41

    Re: Use dictionary to populate worksheets

    There are two distinct scenarios I'm attempting to create using two dictionaries that I create from the data on Sheet1.


    Scenario One: The first dictionary is an hours table (header on first row, 1st column = key):
    HTML Code: 
    I want to take the value from the Regular_Hrs column for the key "Exits" and insert it into the first open column on the row in the Sargent PC Graphs Less OT (2) tab with the Exits profit center regular hours:

    Hours4.jpg

    Scenario Two: The second dictionary is a "rollup" table (header on the first row, 1st column = key):

    HTML Code: 
    I want to sum the frozen rollup value for all of the keys (Profit_Center) that have a group of "Bored" and insert that sum into the first open column on the row in the Sargent PC Graphs Less OT (2) with the Bored profit center frozen rollup:

    Hours2.jpg

    I really hope this all makes sense. I have to create these two scenarios a BUNCH of times for each profit center across multiple tabs, which is why my initial thought was to create the dictionaries rather than stuff variables. Now I'm questioning whether this was the best, most efficient way to go about this. Please let me know if you have any other questions.
    Last edited by Justin H2O; 01-07-2020 at 11:50 AM.

+ 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. Why dictionary very slow ,is dictionary have maksimum data
    By daboho in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 10-30-2018, 03:57 PM
  2. Adding dictionary into dictionary and retrieving data
    By jaryszek in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 10-08-2018, 06:52 AM
  3. [SOLVED] Macro to create new worksheets and auto populate cells in created worksheets
    By cole.young in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 01-05-2018, 02:47 PM
  4. [SOLVED] Dictionary or Scripting.Dictionary. Binding Referencing Dim-ing. Sub routines and Function
    By Doc.AElstein in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 07-12-2016, 08:28 AM
  5. Cant get data to populate in VBA code from array & scripting dictionary
    By leanne2011 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-25-2015, 04:38 AM
  6. Dictionary - Using a dictionary of dictionaries to hold individual orders
    By wazimu13 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-09-2015, 08:32 PM
  7. Replies: 2
    Last Post: 05-06-2013, 08:53 AM

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