+ Reply to Thread
Results 1 to 11 of 11

Consolidating data with existing data each time macro is run

  1. #1
    Registered User
    Join Date
    02-24-2017
    Location
    Minnesota
    MS-Off Ver
    2016
    Posts
    17

    Consolidating data with existing data each time macro is run

    I have a workbook with the sheets "Advisors", "DataQuery", and "QueryCriteria". I am running a data query and then exporting the information into an excel worksheet from an online database, and then I copy/paste it into the "DataQuery" worksheet. Right now my macro:
    1. Reads through all of the rows of data for certain criteria, if met, the rows are copied to the "Query Criteria" worksheet
    2. Goes through the "QueryCriteria" worksheet and deletes all of the columns I do not need, and then moves the remaining columns to the left
    i. I need 2 columns from the exported data. One is "Name" and one is "Amount".
    3. I then use .Consolidate to read through the remaining data in the "QueryCriteria" worksheet and consolidate based on the "Name" and then sum the "Amount". This data is placed in the "Advisors" worksheet starting in cell A3. In A2, I have a title for the column. In column B starting at B3, the Amount is summed based on the name and placed here.

    I can get this part to run fine. Here is my code so far:
    Please Login or Register  to view this content.
    I need help with the following:
    1. This macro will be run monthly. New names could come up in the "Names" column. Each time it is run, it needs to read the Names already added to the "Advisor" worksheet (which will be in column A starting at A3). If the name is not already listed in that column, the name needs to be added to the the list.
    a. I have tried adjusting the .Consoliate Array (...) to include both the data from "Query Criteria" and "Advisors", but whenever I tried to include the "Advisors" worksheet in the array and consolidate the data on top of it, I get an error.

    2. The data query that is run is set to a date range (by month). I need to a way to title the the B column in the "Advisors" worksheet by month (ex: 12/16, 1/17, 2/17, etc.) in row 2. If the macro has already been run for that month, column B with the sum should be replaced with the new data. If the macro has not been run for that month, I need the sum placed in a new column. For example, if I have the data already in for the months 12/16 and 1/17 (2 separate columns, B & C) but am running the macro for 2/17 for the first time, the sum would be placed in column D, and the heading 2/17 created in cell D2. Also, I need to Total column always on the far right. Before adding 2/17, it would be in column D. After running the macro for 2/17, it would be moved to column E and include the new 2/17 data in column D in its total sum.

    I apologize if this is confusing. I have been stuck on this for quite awhile and tried multiple different methods but can't get it to work. Feel free to ask any questions. Anything you can help with would be much appreciated.

  2. #2
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: Consolidating data with existing data each time macro is run

    Can you attach an example workbook with the three data sheets?
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

  3. #3
    Registered User
    Join Date
    02-24-2017
    Location
    Minnesota
    MS-Off Ver
    2016
    Posts
    17

    Re: Consolidating data with existing data each time macro is run

    I hope this is good enough. If you need nay more info let me know. I made an extremely simplified version since the actual data is sensitive. The actual "Data Query" worksheet will have over 100k+ rows each time it needs to be run.
    Attached Files Attached Files

  4. #4
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: Consolidating data with existing data each time macro is run

    This doesn't use the QueryCriteria worksheet. It consolidates the Data Query in the code.

    Please Login or Register  to view this content.
    Last edited by AlphaFrog; 02-26-2017 at 05:50 AM.

  5. #5
    Registered User
    Join Date
    02-24-2017
    Location
    Minnesota
    MS-Off Ver
    2016
    Posts
    17

    Re: Consolidating data with existing data each time macro is run

    Wow thank you I really appreciate it!

    One thing that isn't necessary, but would just be nice to have. How would you have it check if the date is already in the "Advisors" sheet, and then if so, not do anything? (Ex: If the macro has already been run with column "K" having a February date, don't run it again).

    Thanks
    Last edited by spencerp237; 02-26-2017 at 12:59 PM.

  6. #6
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: Consolidating data with existing data each time macro is run

    That's not hard. The code currently overwrites a month if it already is listed. That's what you originally requested.

    Quote Originally Posted by spencerp237 View Post
    If the macro has already been run for that month, column B with the sum should be replaced with the new data. If the macro has not been run for that month, I need the sum placed in a new column.

    So confirm what you want; overwrite or do nothing if the month is already listed.

  7. #7
    Registered User
    Join Date
    02-24-2017
    Location
    Minnesota
    MS-Off Ver
    2016
    Posts
    17

    Re: Consolidating data with existing data each time macro is run

    Right now it makes a new column each time it's run, even if that month has already been done.

    Would you please show me how to do both just for future reference? What I want is overwrite but I can think of future uses for both.

  8. #8
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: Consolidating data with existing data each time macro is run

    The code gets the m\yy for the DataQuery from cell K2.

    Using your example workbook, I can run the macro once and it adds a column 1\17 on the Advisors sheet. I then change values on the DataQuery and run the macro again. It overwrites the 1\17 column on the advisors sheet. So I can't duplicate having it make another 1\17 column.


    Adding the red line to code will have it do nothing if the month is already listed.

    Please Login or Register  to view this content.

  9. #9
    Registered User
    Join Date
    02-24-2017
    Location
    Minnesota
    MS-Off Ver
    2016
    Posts
    17

    Re: Consolidating data with existing data each time macro is run

    Ah I figured out why it wasn't working for me. I had the format in row 2 of the Advisers worksheet set to Accounting on accident. Changed it to Date and it works great. Thank you so much!

  10. #10
    Registered User
    Join Date
    02-24-2017
    Location
    Minnesota
    MS-Off Ver
    2016
    Posts
    17

    Re: Consolidating data with existing data each time macro is run

    Sorry one more question. I'm trying to follow what you did to sum each row and add a "Totals" row to the bottom that sums each column as well, but kept coming up with errors. I've tried adding in:

    Please Login or Register  to view this content.
    And then below each of the Set TotCol using
    Please Login or Register  to view this content.
    At the bottom I did

    Please Login or Register  to view this content.
    But it's not working how I'd like it to.
    Last edited by spencerp237; 03-03-2017 at 12:37 PM.

  11. #11
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: Consolidating data with existing data each time macro is run

    Please Login or Register  to view this content.

+ 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. MACRO to split new data into multiple tabs but not overwrite existing data
    By amo899115 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 01-11-2016, 01:42 PM
  2. Need a macro which allows me to edit existing data to replace old data in same cells
    By Pimp_mentality in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-22-2015, 11:06 PM
  3. Deleted
    By MaxStrong in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-11-2015, 01:50 AM
  4. Replies: 11
    Last Post: 11-04-2013, 04:32 PM
  5. Replies: 3
    Last Post: 10-14-2013, 03:06 PM
  6. Macro help for consolidating data that changes per file basis.
    By texasryder in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-10-2013, 10:20 AM
  7. Need help grouping/consolidating data by time interval
    By kushki in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-28-2012, 04:21 PM

Tags for this Thread

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