Have you tried using a pivot table? seems a better approach
I have a spreadsheet which has data in column A & B. I trying to do an if statement to state "YES" if data in both A and B has been duplicated. EG 111 in (A) & 222 in (B) are a PAIR, im need an if statemnet in Column (C) to say if this PAIR are duplicated in these two columns return "YES". I have tried THREE different methods to do acheive this which are in the attached document. The main issue I am having is the iff statement is returning yes for some of the number when thye are completely different. I have used concatenate, & and tried to multiply the two values to try to make it unique. I have an example of each in the separate tabs.
Your formulas are correct. The reason why you are getting the wrong result is that you are comparing numbers with more digits than Excel can hold, so they look the same.
EDIT: Although these are text strings, because there are no alphabetic characters, for the comparison, Excel is taking their value. If you add an alpha character, they will not then compare equal.
Last edited by ByTheCringe2; March 27th, 2007 at 21:01.
I think you may be right ByTheCringe2...he did have >1 in the formula. Now I'm kicking myself...I must be tired.
EDIT: If you wish, you can delete my post above...it is incorrect.
Last edited by bradles; March 27th, 2007 at 21:39.
There are currently 1 users browsing this thread. (0 members and 1 guests)