Spot Different Formulas

[jomili]

I have a column that's about 8000 rows deep. 99% of the formulas in the column are Vlookups, but with a few "other" formulas sprinkled in here and there. I know I can look for the green triangle on a cell by cell basis to see that a formula differs from the one bordering it, but how do I scan my column to boil out those different formulas?

What I'd really like is to be able to filter by formula type.

[Glenn Kennedy]

hit CTRL and ¬ (top left of keyboard) - same to reverse - and you will see all the formulae at a glance.

[GeneralDisarray]

If I were you, I would just make a quick function you can call in the worksheet to expose the formulas. You could add a column to the table, making use of the VBA function, then filter on that column.

See the attachment to see what I mean.

VBA part:

Please Login or Register  to view this content.

[jason.b75]

What I'd really like is to be able to filter by formula type.

If you want to hide the vlookup formulas by filtering then try this method. Bear with me, it might appear a bit of a messy process, but once you've done it a couple of times, it should become a natural process that only takes a few seconds.

Select the column with the formulas.

Press Ctrl f

In the 'Find' box, enter VLOOKUP

Change the 'Look In' dropdown to 'Formulas' (if the dropdown is not visible, then you will need to click 'Options')

Make sure that the 'Match entire cell contents' checkbox is unchecked.

Click 'Find All'

Press Ctrl a

Close the box (don't click any cells)

Press Ctrl 9 to hide the vlookup rows. (Shift Ctrl 9 to unhide).

[jomili]

Wow, I'm impressed. I didn't expect to find so many viable alternatives.

Glenn, I've filed yours away for future use. With the right data set it will be a big help. With 8000+ rows, "at a glance" doesn't work very well.
GeneralDisarray, I already had a function to expose my formulas, just didn't think of using it to filter. I'm so happy not everyone thinks the way I do!
Jason, I like your method. I can see a lot of potential in it.

Thanks all 3 of you for helping with this one!

[Glenn Kennedy]

You're welcome and thanks for the Rep. Appreciated!. WRT "at a glance", actually it's not that bad - if you expand the column width to fit the formula, you can scan down 8000 rows (using page down) amazingly quickly.