Duplication of a formula

[tamrob23]

I literally have over 1,500 rows of formulas, is there a formula to check to see if you duplicated a formula? Any help will be greatly appreciated.


Thanks!

Tamara

 

[schielrn]

The only way I can think of doing this is with VBA or a UDF. Is that an option?

 

[tamrob23]

What is VBA or a UDF?

 

[schielrn]

It is code or a user defined function. They are ways of creating your own functions or creating code to do stuff automatically.

Try this. Press Alt+F11. Then press Alt, I and then M. This will insert a module. Then paste this code in the module:

'--------------------------------------------------------------------------------------------------------
'***NEW FUNCTION***
'Description: Returns text equivalent of formula in cell.
Function GetText(cell As Range)
GetText = cell.Formula
End Function

Then go back to your workbook and input this formula:

=SUMPRODUCT(--((RIGHT(gettext($A$1:$A$18),LEN(gettext($A$1:$A$18))-1)=RIGHT(gettext(A1),LEN(gettext(A1))-1))))

Change the ranges to your range for the formula and then you can copy this formula down the column. Anything that is greater than 1 is a duplicate. You can filter on this if you want.

Hope that helps and post back any questions if you are lost.

 

[tamrob23]

I think this will help. Thank you for your time, have a great weekend!

Tamara

 

[Peter_SSs]

I literally have over 1,500 rows of formulas, is there a formula to check to see if you duplicated a formula? Any help will be greatly appreciated.


Thanks!

Tamara

Can you expand a little on the situation? Perhaps some example formulas? Maybe a dummy example with only, say, 8-10 rows to explain what you have and what you are trying to achieve?