Excel VBA: Consolidating data with existing data each time macro is run

[spencerp237]

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:



Sub AdvisorSort()


Dim i As Long
Dim rowIndex As Long
Dim lastRow As Long
Dim lastRow2 As Long


Sheets("QueryCriteria").Rows("2:" & Rows.Count).ClearContents
Sheets("DataQuery").Select
rowIndex = 2


For i = 1 To Cells(Rows.Count, "A").End(xlUp).Row


If Cells(i, "Q") = "TRUE" And Cells(i, "M") = "Client Contribution" Or Cells(i, "M") = "Merge In from Other Account" Then
Rows(i).Copy Destination:=Sheets("QueryCriteria").Rows(rowIndex)
rowIndex = rowIndex + 1


End If


Next i


Sheets("QueryCriteria").Select


Sheets("QueryCriteria").Range(Sheets("QueryCriteria").Columns(1), Sheets("QueryCriteria").Columns(11)).Clear
Sheets("QueryCriteria").Range(Sheets("QueryCriteria").Columns(13), Sheets("QueryCriteria").Columns(14)).Clear
Sheets("QueryCriteria").Range(Sheets("QueryCriteria").Columns(16), Sheets("QueryCriteria").Columns(46)).Clear


Sheets("QueryCriteria").Columns("O:O").Cut
Sheets("QueryCriteria").Columns("A:A").Insert
Sheets("QueryCriteria").Columns("M:M").Cut
Sheets("QueryCriteria").Columns("B:B").Insert


lastRow = Sheets("QueryCriteria").Cells(Rows.Count, 1).End(xlUp).Row
lastRow2 = Sheets("Advisors").Cells(Rows.Count, 1).End(xlUp).Row
Sheets("Advisors").Range("A3").Consolidate Array("QueryCriteria!R2C1:R" & lastRow & "C2", "Advisors!R3:C1;R" & lastRow2 & "C2"), xlSum, TopRow:=False, LeftColumn:=True

End Sub



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.

[p45cal]

Are you looking to end up with this sort of thing?:
18481
Where advisors come and go, but you want to retain a bit of a history?

[spencerp237]

Yes! Advisors may be parts of some months but not others

[p45cal]

OK, now if I write Pivot Table, does that give cause for concern?

[spencerp237]

The exported data can't be saved each month so I didn't think pivot tables would work

[p45cal]

The exported data can't be saved each month so I didn't think pivot tables would workWell, you're effectively saving it in the Advisors sheet, so why not in the QueryCriteria sheet (or any other sheet for that matter, even one dedicated to that - it can be (xlvery)hidden)? You currently wipe QueryCriteria more or less clean each time but you don't need to…

[spencerp237]

I guess one of the big reasons for not keeping the data is because there is gonna be 50,000+ rows for each month and i want to keep it from getting excessively long

[p45cal]

I guess one of the big reasons for not keeping the data is because there is gonna be 50,000+ rows for each month and i want to keep it from getting excessively longI only mean the 2 columns and reduced-number-of-rows of data that you effectively filter. Otherwise you're saying that your advisor table is some 50k rows long, which I don't believe.

This begs a question:
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.We could write a similar query to get only the information you require for this table. Does that online data retain historical data too? Ideally you could create a pivot table directly from that online database, but that may not be possible.

[spencerp237]

I mean the amount of rows in the "QueryCriteria" worksheet is over 50k for each month. Which then gets summed up by Advisor in the Advisors sheet (which will be around 150ish rows). I didn't think pivot tables would work so I ruled them out in my head, so thank you for suggesting it. I was able to do some experimenting with it and I think I may have gotten what I need except for one part.

In the original data query, there is a date column and I adjusted the sorting part of the macro to include this. When I make a pivot table with that data, I put the Advisors column in Rows, Amount in Values, and Date in columns. The problem is that the dates have a day, so each individual day becomes it's own column. I used the group option, which is almost what I need. It combined all the dates in the month, but it just says "Jan" or "Feb". It is possible that Jan 2017 and Jan 2018 could end up being in the file in the future so is there a way to specify this?

[p45cal]

I strongly suspect you're grouping by months only, and you need to group by months AND years (otherwise if you have more than one year of data, the Januaries of all the years will be lumped together).
The problem then is that you'll have an extra row at the top of the table showing years.

Am I right in guessing that column Q of DataQuery has formulae in? I ask because if you do, it would be easy to have another which brings each date down to the first of the month (this is just the same as what happens when you enter just a year and month into a cell in Excel; it becomes the first of that month automatically, if Excel recognises it as a date), and use that date as source data for the pivot.
That formula could be the likes of:
=EOMONTH(D2,-1)+1
or:
=DATE(YEAR(D2),MONTH(D2),1)

The whole business of using pivots v. using macros to create a table is that the table layout is usually very good and adjusts itself automatically to the updating data, the arithmetic is all done for you - and quickly.
Using a macro, you will, one day as like as not, come across something that you hadn't thought of when you wrote it; no-one writes bugless code (and if he tells you that he does, he's lying - or very misguided). With pivots, 98% of the work is done for you.