Hi everyone. I think this is a bit outside the normal realm of this forum, but I'm hoping you can help. I've got a macro that reads through one sheet, and puts some data in another sheet, based on a few criteria. The macro is working, but it's very slow - it took about 7 minutes to get through 5,000 lines, and my full source data is about 300,000 lines. Ideally, I'd love to make this process much faster.
I've attached a sample file with the macros included. The "Process File" will do the following:
- Clear all data on the "EIB" sheet
- Sort the data on the "ImportedData" sheet
- Remove the HTML tags from Columns B and C of the "ImportedData" sheet
- If there's no data in Column B, use the first 80 characters from Column C
- Reformat the dates from "YYYY-MM-DD HH:MM:SS UTC" to "YYYY-MM-DD" if it's a valid date (there are some data entry errors in the original source, for example a 3 or 5 digit year)
- If the "Status" column is NOT "Inactive", or the status is "Completed" with a date after 10/1/2020, copy some of the columns to the "EIB" sheet
I also manually processed the first 5 rows of the "ImportedData" sheet onto a new sheet called "Manual - 5 rows", so you can see what the output should look like.
Please Login or Register to view this content.