Hello everyone,
I have an Excel problem, I am supposed to:
- import a text which lacks certain rows from.txt file
- clean it - change some text/letter combinations to letters etc, vlookup some other values
- delete empty rows using formulas
Everything should be done automatically, so when a new text file is imported I can get my new, neat database without having to repeat all those steps again. This sounds easy to do using VBA, but... here is the catch - I can't use VBA for that.
I have managed to create an array formula which does that, It's been done in 2 different approaches:
- delete a row if at least one cell in that row is empty
- delete a row if every cell in that row is empty
Formula works fine, but it's very CPU consuming - my original database has 10 columns and around 25000 rows so imagine using that formula 250 000 times.
My question is - how can I simplify the formula so it would delete blank rows of a certain cell range without consuming 100% of a CPU for 20 minutes and almost crashing excel.
I am attaching an excel file with my approach.
I hope we can figure something out together!
Regards,
lukevba
I have an Excel problem, I am supposed to:
- import a text which lacks certain rows from.txt file
- clean it - change some text/letter combinations to letters etc, vlookup some other values
- delete empty rows using formulas
Everything should be done automatically, so when a new text file is imported I can get my new, neat database without having to repeat all those steps again. This sounds easy to do using VBA, but... here is the catch - I can't use VBA for that.
I have managed to create an array formula which does that, It's been done in 2 different approaches:
- delete a row if at least one cell in that row is empty
- delete a row if every cell in that row is empty
Formula works fine, but it's very CPU consuming - my original database has 10 columns and around 25000 rows so imagine using that formula 250 000 times.
My question is - how can I simplify the formula so it would delete blank rows of a certain cell range without consuming 100% of a CPU for 20 minutes and almost crashing excel.
I am attaching an excel file with my approach.
I hope we can figure something out together!
Regards,
lukevba
Attachments
Last edited: