tljenkin
Board Regular
- Joined
- Jun 14, 2007
- Messages
- 147
Hello All,
I have a massive csv file thats linked to an excel file as "data model" & connection only, lets call it Master.
Now this csv file arrives weekly with a different name, so week 1, its called Week 1 and that rolls each week.
In the Master, you have the following tabs: Data and Reference tabs.
Data has the dump from the csv, about 100,000 lines. These lines are contained in a Table created automatically by Excel once the data query selection is retrieved. Query already preset in Master.
Reference has 5 columns each populated with items that must be deleted from Data
For three of those columns in Reference, the items contained will look for exact matches in a column in Data. Eg In cell A2 of Reference, you have the word "ball" and this has to be searched for in column A in Data. There are exact matches there. For every instance discovered, the VBA routine should delete the entire row.
For the remaining two columns, this is not an exact match. Using same example, ball would be searched for in cells than contain "soft ball" etc. VBA should search for an instance of ball in the text string in that column and delete entire row as well.
The Ask:
Thanking you in advance Great VBA Gurus.
I have a massive csv file thats linked to an excel file as "data model" & connection only, lets call it Master.
Now this csv file arrives weekly with a different name, so week 1, its called Week 1 and that rolls each week.
In the Master, you have the following tabs: Data and Reference tabs.
Data has the dump from the csv, about 100,000 lines. These lines are contained in a Table created automatically by Excel once the data query selection is retrieved. Query already preset in Master.
Reference has 5 columns each populated with items that must be deleted from Data
For three of those columns in Reference, the items contained will look for exact matches in a column in Data. Eg In cell A2 of Reference, you have the word "ball" and this has to be searched for in column A in Data. There are exact matches there. For every instance discovered, the VBA routine should delete the entire row.
For the remaining two columns, this is not an exact match. Using same example, ball would be searched for in cells than contain "soft ball" etc. VBA should search for an instance of ball in the text string in that column and delete entire row as well.
The Ask:
- When the Master is opened, an input box should pop up asking to select path and name of csv file. This should update the VBA code and the source in query settings.
- VBA that can Loop through all items in column A of Reference against column A of Data and delete rows of all exact matches
- VBA that can Loop through all items in column B of Reference against column B of Data and delete rows of all matches found within a text string
Thanking you in advance Great VBA Gurus.