• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 387
  • Last Modified:

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.
0
Humbl3St3ps
Asked:
Humbl3St3ps
  • 6
  • 5
  • 4
  • +2
1 Solution
 
GlobaLevelCommented:
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.
0
 
SiddharthRoutCommented:
Use this

=IF(A3=OFFSET(A3,0,3,1,1), "Match", "No Match")

Sid
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
SiddharthRoutCommented:
Please ensure that the formula is not in E3 else when you delete Col D, Col E will move into Col D and you will get a Circular Reference Error.

Sid
0
 
jppintoCommented:
Use a formula with COUNTIF() instead, like this:

=IF(COUNTIF($A$2:$A$44;D2)=1,"Match","No Match")

Please take a look at the attached file.

jppinto
Countif.xlsx
0
 
Humbl3St3psAuthor Commented:
SiddharthRout,

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.

Dusty
0
 
SiddharthRoutCommented:
>>>"or vice versa"

Not sure if you can do that without VBA but I could be wrong :)

Sid
0
 
jppintoCommented:
Dusty, did you checked my proposal?
0
 
Humbl3St3psAuthor Commented:
Jppinto,

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.

Dusty
0
 
Saqib Husain, SyedEngineerCommented:
Try this formula

=IF(INDIRECT(ADDRESS(ROW(),1))=INDIRECT(ADDRESS(ROW(),3)), "Match", "No Match")
0
 
jppintoCommented:
If you want it "vice-versa", then you can change the formula to this:

=IF(AND(COUNTIF($A$2:$A$44,D2)=1,COUNTIF($D$2:$D$44,A2)=1),"Match","No Match")

Deppending on your Regional Settings, you should change the "," by ";" making my original formula look like this:

=IF(COUNTIF($A$2:$A$44;D2)=1;"Match";"No Match")

jppinto
0
 
SiddharthRoutCommented:
ssaqibh Bhai: Awesome :)

Sid
0
 
Saqib Husain, SyedEngineerCommented:
Thanks.....if it is acceptable :)
0
 
Saqib Husain, SyedEngineerCommented:
small mistake.... the 3 should have been 4
0
 
SiddharthRoutCommented:
ssaqibh Bhai: I guess you meant 4 instead of 3 in your formula?
0
 
SiddharthRoutCommented:
Xover

Sid
0
 
Humbl3St3psAuthor Commented:
ssaqibh,

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.

Dusty
0
 
Saqib Husain, SyedEngineerCommented:
Thanks for the points and the grade.

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

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 6
  • 5
  • 4
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now