Solved

# Formula needed that indicates when two other cells are identical.

Posted on 2011-05-04
339 Views
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
Question by:Humbl3St3ps

LVL 10

Expert Comment

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

Author Comment

My present formula has already been posted. Please look at end of first paragraph on original question submission.
0

LVL 30

Expert Comment

Use this

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

Sid
0

LVL 30

Expert Comment

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

LVL 33

Expert Comment

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

Author Comment

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

LVL 30

Expert Comment

>>>"or vice versa"

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

Sid
0

LVL 33

Expert Comment

Dusty, did you checked my proposal?
0

Author Comment

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

LVL 43

Expert Comment

Try this formula

0

LVL 33

Expert Comment

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

LVL 30

Expert Comment

ssaqibh Bhai: Awesome :)

Sid
0

LVL 43

Expert Comment

Thanks.....if it is acceptable :)
0

LVL 43

Accepted Solution

small mistake.... the 3 should have been 4
0

LVL 30

Expert Comment

ssaqibh Bhai: I guess you meant 4 instead of 3 in your formula?
0

LVL 30

Expert Comment

Xover

Sid
0

Author Closing Comment

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

LVL 43

Expert Comment

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

Author Comment

ssaqibh,

Advice noted and will be followed in the future. Thanks again.

Dusty
0

## Featured Post

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directiâ€¦
Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Datâ€¦
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a â€¦