I'm SURE there's a better way to do this - hoping one of you can help point me to the right function, formula or approach.
I have spreadsheets being loaded with data by automated process on a daily basis. That data can be anywhere from 5 to over 1 million rows. Generally, we expect about 50,000 rows though.
Among other validations being done, I need to be sure that there are no duplicates across 4 specific columns. However, I cannot DELETE them - I have to flag them with an error message.
Currently, I'm doing this by creating a key like FIELD1.FIELD2.FIELD3.FIELD4 for each row.
I then do a COUNTIF through the range of data on that value, and if the result is > 1, I replace with an error message.
The calculation is taking far too long though.
- I have turned off screen updating
- I have modified calculation to be done manually
Is there something I can do instead of using COUNTIF, as that processing seems to be killing the performance? Takes just north of 1 minute for 60,000 rows.
Thanks for any guidance!
Bookmarks