Posts by Jaffey

  • Combine two macros and collate changes between new data and master list

    Re: Combine two macros and collate changes between new data and master list


    Deleting the rows didn't work for me but I did try deleting the whole ("to") sheet and then recreating it and that seemed to fix it. I supposed I could build something into the script to start with a fresh 'to' sheet everytime. Would you suggest that as well or is there a better way to reset the page/ranges without having to recreate it?

  • Combine two macros and collate changes between new data and master list

    Re: Combine two macros and collate changes between new data and master list


    Hi Justin, I'm using the same method to define MyRnge and MyNewRnge you gave me in http://www.ozgrid.com/forum/sh…p?t=149625&goto=newpost** I'm not sufficiently skilled to go changing it. I expect MyNewRnge is larger because it has more rows than MyRnge to start with. This will always be the case as MyNewRnge is the master list and will always be larger than MyRnge (except when running tests of the code etc)

  • Combine two macros and collate changes between new data and master list

    Re: Combine two macros and collate changes between new data and master list


    Thank's Justin. It's working except for the following; the first part of the macro correctly identifies and appends the records that don't exist in MyNewRnge from MyRnge and flags them "new to list", however, later on when MyRnge is being checked for records that have dropped from MyNewRnge, the new records that were previously appended/flagged 'new to list' have their flag overwritten as "dropped", I presume because they didn't exist in MyNewRnge to begin with(?). The macro then proceeds to add the flag 'dropped' to another 1000 or so empty records.


    I have attached a file and if you press 'reset data' followed by "CompareNew", you will see exactly what I mean as I have left ScreenUpdating turned on.forum.ozgrid.com/index.php?attachment/35780/

  • Combine two macros and collate changes between new data and master list

    I created a macro that compares two ranges and updates the 'master' range with new records from a data refresh thanks to some great help from Justin Doward. I then took his code and 'reversed it' so I could identify records in the master list that no longer appear in the the new data range and flag them as 'dropped' on the master list. (I don't want to delete them from the master list)


    If possible, I would like to combine these two macros. I would appreciate any help as well as comments on how I can clean up the code that I've no doubt sullied after Mr Doward provided it to me. There is also a bug in the Compare2 macro insofar as it copies the comment 'dropped' beside empty rows when the master list is much longer than the new list. I've attached a workbook with the macros as well as copied them below. forum.ozgrid.com/index.php?attachment/35764/



  • Update Records on Master List from New List and Archive changes

    Re: Update Records on Master List from New List and Archive changes


    Thanks Justin, I tried "reversing" your code as you suggested and I was fairly successful in that I was able to get the macro to identify licencees who dropped from the new list and then add them to the bottom of the master list with a comment "dropped" but what I would really like to do is append the comment beside the existing record above it instead of duplicating it at the bottom. I tried a bunch of different ways but so far no luck. I feel like I'm close though. Do you see any obvious errors in the attached or should I repost it as a new question? I held off on posting it right away becuase what I really want to do is combine this with your original solution so i don't have to run two separate macros if possible.


  • Update Records on Master List from New List and Archive changes

    Re: Update Records on Master List from New List and Archive changes


    Thanks Justin! That helps a lot!


    I am still testing it but so far it is identifying new agents and the agents that changed employers very well! One thing it doesn't do, however, is detect the agents who drop off the list. Do you think that can be added or would it be better done seperately?

  • Update Records on Master List from New List and Archive changes

    Good day Oz!

    I have a master list of approx. 5,000 license numbers (column A “Lic#”) and the employee names to go with them (column B “ Agent”) as well as the name of the agency they work for (Column C “Agency”). The license numbers are government issued and are never (supposedly) used by more than one individual. Each month I compile an updated list of the names from the government website and update the master list with the changes. The types of changes that can occur are;
    - Agents moving to a different agency
    - Agents moving to a new, previously non-existing agency
    - Agents changing the spelling of their name
    - Agents no longer actively licensed at any agency (i.e. dropping off the list)
    - Agents ‘resurfacing’. i.e. agents who dropped off the list reappearing at their old (or new) employer with their old licence#

    For vetting purposes we are required to show the last 3 agencies an agent was registered with so I would like to track up to 3 agency changes per licence#/agent. (name of the agency and the date they changed) I have attached a workbook with tab ‘newdata’ showing what the new data looks like coming in, a ‘master’ tab showing how the new data compares to the master tab, and a goal tab, which is my proposed solution for how the data should be tracked. Note - I am open to other suggestions!

    So far I have modified acw’s code slightly and for the most part it successfully goes through the new data on sheet1 and appends the new licence # records to the bottom of the master list (sheet2) (never quite gets to the end though). If someone could help me fix that and then show me how to identify the agents who switched agencies I will be well on my way!



    Links I tried to adapt
    ACW’s:Compare 2 Worksheets To Update Values http://www.ozgrid.com/forum/showthread.php?t=33808
    and RIKM553’s http://www.ozgrid.com/forum/sh…pdate+changed+records+add

  • Combine two macros to eliminate creation of 5,000+ tabs

    Happy New Year Wizards of Oz! Thank you for all your help over the past year.

    I apologize in advance if my thread title is not sufficiently descriptive but I couldn’t think of a better one. My goal is to combine two macros to eliminate 1000’s of tabs created as I process a series of .htm files stored in a folder.

    My first macro performs a data scrape against a web site and creates a single .htm file after each search term is submitted and the results returned.
    The second macro imports each .htm file into its own tab.
    The third macro copies the data from the tabs created onto one summary tab.

    The number of .htm files now exceeds 5,000 so you can imagine how much Excel likes 5,000 tabs and only 1 gig of ram. So my objective is to eliminate the need for all those tabs by combining the 2nd and 3rd macros so that the data from the htm files is copied to the summary tab as the files are being processed. The ideal solution of course would combine all 3 macros and copy the data directly from the html tables as they are displayed on the web page however that is a much bigger project and all my attempts at importing the tables via VBA have been unsuccessful thus far. For that reason I resorted to calling a 3rd party program in the first macro which runs an iOpus “imacro” script to perform the data scrape and saving of the .htm files so I am content to leave that alone for now (question for another day)

    Below is the code for the 2 macros I would like to combine.



    Macro2 – IMPORT MACRO - Pulls in the .htm files and creates a new tab for each one using the name of the imported htm file as the tab name



    Macro3 – COLLATE MACRO - copy all tabs to the summary tab



    I tried to get the import macro to handle the collating by inserting the copy statements at the end of the import/create tab loop and then adding a command to delete the new tabs after the data has been copied from them but I can't get it to work...just mentioning this as a possible approach to use.

  • Change cell color depending on change in hour

    Re: Change cell color depending on change in hour


    That works perfectly! I recorded a macro while applying your formula and now it formats the new pages created when drilling down in a pivot table very nicely. Thank you so much!

    Also, I am trying to understand what you're formula is doing. I see you are using HOUR to get the hour as a whole number and then using MOD to divide it by 2 and then depending if the remainder equals 1 or zero if gives true/false. is that correct?

    Here is my code for anyone interested;

  • Change cell color depending on change in hour

    I have reviewed many posts dealing with changing cell formatting based on different variables but nothing quite like what i require (a post from earlier today was the closest "Change cell color depending on change in dates" http://www.ozgrid.com/forum/showthread.php?t=147419)

    I would like the color of a row to change each time the hour of the recorded time in a cell changes. For example, calls betwen 7-8 would be light tan, calls from 8-9 would be a slightly darker tan, calls form 9-10 would be light tan again, then back to tan for 10-11. I hope I'm describing this adequately but in case not I have atatched a simple file to illustrate. Based on previous posts it would seem conditional formatting is the answer but I can't figure out a formula that detects the change in hour as opposed to writing separate formulas for each hour. As always, many thanks!

  • Find Values In Column, Move To Another Column

    Re: Find Values In Column, Move To Another Column & Delete Row Values


    Thank you snb! I expected a loop would be required but your method is so much simpler/elegant...I just wish I knew understood what it was doing 8)


    I understand the offset references but not the rest of it. If you could provide a brief comment how cl.Row>3 is targetting the phone numbers I would be very grateful. thanks again

  • Find Values In Column, Move To Another Column

    I have attached a sample of some raw call data call from a phone switch. I need to clean it up so that a pivot table can interpret it correctly.

    The majority of the rows are fine with the appropriate columns filled in with the call details. I.e.. Date, Time, Digits Dialled etc.
    But every so often you will see a record with just one variable in columns E and F. This happens when an inbound call is recorded. The value in column E is actually the caller ID (phone#) of the incoming call and it belongs to the record in the preceding row. The value in column F is a series of x's which I do not need and can be discarded.

    What I would like is some VBA code that will search through column E for these stray incoming phone numbers and then move/copy them to column I on the previous row and then delete the row the value was on after it has been moved. If anyone has another suggestion I am open to ideas. Thanks!

  • Display Last X Most Recent Days In Pivot Table

    Re: Display Last X Most Recent Days In Pivot Table


    Dave - The report is used by many users. Supervisors tend to focus on the most recent week's worth of data, managers like to see a 2 week snapshot and the directors usually want to look at a whole month or even 2 month's of data and virtually everybody starts by looking at yesterday's results first. I'm trying to make it so they don't have to spend so much time checking and unchecking boxes in the date filter. I have created many versions in the past where each sheet had it's own pivot table with a preset 'view' (using grouping) and then link them together (thanks to other code I found here) but I am trying to create what I think/hope is a simpler solution. My workbook contains no data upon opening, a macro execute the opening and importing of the required data file and then others create the pivot tables. I will upload what I have but there is very little saved on this part as I have been having very little success.

  • Display Last X Most Recent Days In Pivot Table

    I would like to adapt Andy Pope's code to display the last X days of call data in a pivot table - starting from the most recent date - instead of just today's data which this code displays. Ideally, I would like the user to be able to enter the number of days of data they wish to see by entering a number in a cell and have the code pick it up from there. Ex. enter 14 and have the pivot table display the last 14 day's worth of calls. If the code could also skip calls made on Sat or Sun it would be even better



    The code is from http://www.ozgrid.com/forum/showthread.php?t=59793


    I have seen many posts on displaying pivot items as well as read all of;
    Hide/Show Pivot Table Field Items
    Hide Pivot Table Fields Pivot Items by Criteria
    but I cannot get them to work the way I need them to.
    Your help, as always, is greatly appreciated.


    Please note there are usually about 50,000 rows of call data in the DATA sheet the pivot table reads from at roughly 1,000 calls per day so when a user selects the most recent 7 days to display, the pt is selecting some 7000 records to work with. The call dates are stored in a column called DATE on the DATA sheet. The pivot table is called ptAGENT on a sheet named AgentMaster

  • VBA Code To Convert Dates

    Re: VBA Code To Change Dates Format


    The dates are in column 3 ("C:C") of my worksheet. I ran text to columns on that column while recording the macro and pasted the results you see above. When you run the macro with that bit of code it does not change the date format.


    I modified the code from Dave's reply above to use column 3 and it worked just fine.