2019 Grand Tour

[StarWolve]

(02-08-2019 10:38 AM)Babbitt Wrote:  That is fantastically helpful. If you don't mind sharing I would love to see your excel formula.

Thanks

I did a write up on it in this thread - there's a couple posts that show the different formulas: http://www.glmc.org/GLMC_Garage/showthread.php?tid=81

However, I ran into to some trouble and tried a different formula this year - and I think it was less efficient. In the end, it wasn't the formula at all - it was Word messing up the formatting. I cut and paste the location names into Word, and should have used notepad. Word messes with the quotes, and that messed up the excel formula. (left and right quotes instead of a plain double-quote)

The formula I used this year was:
=SUMPRODUCT(--ISNUMBER(SEARCH({"ABALONE","APACHE TEARS","CALCITE","CLINOZOISITE","...ETC...","YTTERBIUM","DUBNIUM"},CELL_ADDRESS_HERE)))>0

In the past, I've used:
=IF(COUNT(SEARCH({"AA","BB","CC","..."},CELL_ADDRESS_HERE)),"TRUE","FALSE")

They both do the same thing - return True or False if the cell contains any of the strings in the formula.

You can also use Shift+F3 to change the case in Word, and =UPPER(cell_address) or =LOWER to convert case in Excel, which is somewhat helpful if your formula is case specific.

Nothing really complicated - no macros or anything, just using the built in formulas to sort the data.