ByTheWestWay
New Member
- Joined
- Aug 1, 2011
- Messages
- 5
Hi all
If anyone could lend me a hand then you might just be able to save my bacon!
I have an excel file which i receive on a weekly basis from an outside office. The file contains the following columns of data:
IDENTIFICATION (NUMERIC) / DATE (DATE) / DATE_OF_BIRTH (DATE) / WORK_COMPLETED (STRING)
Each week i add the new data (say 100 rows) to the existing worksheet (around 20,000 rows of data) so the file is steadily getting towards the Excel 2003 65,000 limit.
In the combined sheet i would like some VBA that first of all sorts on the IDENTIFICATION column (A). Then i need to identifiy where there is a blank DATE_OF_BIRTH and search the previous records (using the IDENTIFICATION) and look up this missing DOB information. If there is no previous record then mark the DOB cell as "Unknown". Then i'd like to sort on the DATE column (ascending). Next i need it to identify where there are more than one of the same DATE for same IDENTIFICATION (eg Customer A has 2+ records on the same purchase date). Once identified i need to look at the WORK_COMPLETED column. Say we have a customer who has 3 purchases on the same date (3 records) - if they have purchased products A, B & C then i need the A and B records to be deleted and the C record to be changed to purchased "ALL". Otherwise any other combination should be left as it is.
I appreciate this is asking quite a lot and may not be that well explained but if anyone can help then it would be really appreciated.
BTW
If anyone could lend me a hand then you might just be able to save my bacon!
I have an excel file which i receive on a weekly basis from an outside office. The file contains the following columns of data:
IDENTIFICATION (NUMERIC) / DATE (DATE) / DATE_OF_BIRTH (DATE) / WORK_COMPLETED (STRING)
Each week i add the new data (say 100 rows) to the existing worksheet (around 20,000 rows of data) so the file is steadily getting towards the Excel 2003 65,000 limit.
In the combined sheet i would like some VBA that first of all sorts on the IDENTIFICATION column (A). Then i need to identifiy where there is a blank DATE_OF_BIRTH and search the previous records (using the IDENTIFICATION) and look up this missing DOB information. If there is no previous record then mark the DOB cell as "Unknown". Then i'd like to sort on the DATE column (ascending). Next i need it to identify where there are more than one of the same DATE for same IDENTIFICATION (eg Customer A has 2+ records on the same purchase date). Once identified i need to look at the WORK_COMPLETED column. Say we have a customer who has 3 purchases on the same date (3 records) - if they have purchased products A, B & C then i need the A and B records to be deleted and the C record to be changed to purchased "ALL". Otherwise any other combination should be left as it is.
I appreciate this is asking quite a lot and may not be that well explained but if anyone can help then it would be really appreciated.
BTW
