Formula needed that indicates when two other cells are identical.
I have a spreadsheet I use to match up lists of names buildings or long lists of data that can be thousands of lines long. I need a cell to indicate when two other cells are identical. I am currently using the following formula: =IF(A3=D3, "Match", "No Match")
Unfortunately if I need to add or delete cells from column D (or vice versa) then the formulas from that row and below no longer match up, leaving me to drag the correct formula above through to the end in order to rectify the error.
What formula can I use that will not need this constant adjustment and allow me to delete cells or add cells as I need? Please no visual basic or pivot tables if possible; hopefully this can be done with just a formula.
Your asking the formula to be dynamic in such a way where you are hitting the boundary between formulas and vba...if that formula exists I would like to see it..it would be very lenghty indeed...can you post your present formula now?
0
Humbl3St3psAuthor Commented:
My present formula has already been posted. Please look at end of first paragraph on original question submission.
As suggested in the original post by writing "or vice versa" in parenthesis, I need to be able to manipulate both Columns. The formula you suggested works perfectly for adding and deleting cells in the D Column. Unfortunately it does not allow me to delete or add cells in the A Column without producing "#REF!" in the cell containing the formula. I've not used the OFFSET formula before and so after a bit of internet study I can see where I can use it in other places. Thank you for the suggestion.
I cut and paste your formula, but get an error when I hit enter. I've changed some of the numbers around, but I'm not sure what will correct the error.
I've given your formula a thorough beating after the quick correction to "4" and it has passed every test. I'm actually comparing multiple columns with each other, and I was able to adapt your formula to account for the comparisons with the other columns and everything is working perfectly. Thank you for your time, effort and brilliance. They are well appreciated, thank you.
Just a quick advice for the future. You should have chosen my first comment as an answer because that is the one which contains a solution. The other comment was merely an adjustment.
Saqib
0
Humbl3St3psAuthor Commented:
ssaqibh,
Advice noted and will be followed in the future. Thanks again.
Dusty
0
Featured Post
Solve your biggest tech problems alongside global tech experts with 1:1 help.