Hello forum,
I've been playing with a problem but need some help for those that are interested in a teaser. I've recorded macros and attempted writing very basic code but I'm sure someone out there will have a compact solution with smooth code. My goal is to achieve a macro that will perform a laborious routine of copying, pasting and formatting, etc...
In a nut shell I have 2 worksheets, worksheet A consists of a list of company names ranging from anything from 5 to 30 that have attributes attached to each name (consisting of 3 columns). This list is generated and compiled by a daily report and run externally. The idea is to build a 5 day history trend overview of this data that is collected each day into worksheet B, which represents the entity and its associated value in column 3 (X).
This means that on each day I need to rebuild the list (worksheet B) consisting of the relevant entities and its associated X value from column 3 based on the daily data output from worksheet A (which also needs to be suitably formatted). Therefore, I'm asking for help with the best approach in using the cleanest logic without getting bogged down. The main things I need to bear in mind are:
- Entities in the 5 day trend (worksheet B) that have data more than 5 days (without any new additions from the daily generation of Worksheet B) are subsequently removed from the list;
- New entities that have either not been on the daily generation (worksheet A) for more than 5 days or are completely unique to be subsequently added to the list (worksheet B);
- Obviously entities that are already on the worksheet B that are less than 5 days to be updated with daily values of X;
- The list to be suitably formatted by listing the trend overview eventually in alphabetical order.
Worksheet B on 23/06
| Worksheet B | ||||||
| Entity | Comments | |||||
| Abba | ||||||
| AD | ||||||
| Danish | ||||||
| EFG | ||||||
| Future | ||||||
| Danish | ||||||
| General | ||||||
| Global | ||||||
| Jazz | ||||||
| Scotland | ||||||
| Screen | ||||||
| Terra | ||||||
| UV |
Genaration of Worksheet A on 24/06
| Worksheet A daily generation | |
| Entity | X |
| Multi | |
| Banca | |
| Danish | |
| USA | |
| Belgium | |
| Future | |
| Scotland | |
| General |
Subsequent Worksheet B on 24/06
| Worksheet B | ||||||
| Entity | Comments | |||||
| AD | ||||||
| Banca | ||||||
| Belgium | ||||||
| Danish | ||||||
| EFG | ||||||
| Future | ||||||
| GG | ||||||
| General | ||||||
| Global | ||||||
| Jazz | ||||||
| Multi | ||||||
| Scotland | ||||||
| Screen | ||||||
| UV | ||||||
| USA |
The macro recorder and tweaking can do the insertion and deletion of columns along with the use of pasting VLOOKUP formulas, but deleting and inserting based on dynamic rows with the above criteria I'm short of ideas and have run into trouble. I have no knowledge of Loops, Until, Do, etc... and was hoping for someone to put me out of my mysery and give me some good sound code to solve this issue.
Many thanks in advance for anyone to provide a solution or at least push me in the right direction.
[ATTACH=CONFIG]40204[/ATTACH][ATTACH=CONFIG]40206[/ATTACH]