# Cells With Different Information are "Equal"

Discussion in 'Business Applications' started by RHurlburt, Apr 28, 2004.

Not open for further replies.
1. Trying to determine what I need use in order to create a formula which will allow me to state two cells are equal but they have different information.

Simply:

ColA..........ColB

Kitten........Cat
Puppy........Dog

I would like a formula, or table, that would allow me to state A5=B5 (Kitten = Cat) and A6=B6 (Puppy = Dog).

Sounds simple but I just can't seem to come up with a proper solution. The real situation is that when I download a query from a site, they have two different formats that I am using. One is ..."FY (09/04)" and the other is ..
" FY(9/04)". I need to be able to use a "IF(AND" or a "IF(OR".

By the way, I know they are always "Not Equal" as far as formulae go, however, I need to be able to distinguish a " FY(9/04)" from a " FY(9/05)" before I do some changing to the format to get a formula working. IF I create a table with the "Kitten/Cat" arrangement, I can set up 12 different Kitten/Cat equalities in a cell (F2, perhaps) and use that(those) cells as reference in my formula.

I hope this is clear.

Thanks.

2. ### XL Guru

Joined:
Aug 30, 2003
Messages:
2,702
Hi Dick.

How about just formulas to convert format 1 to format 2 and compare, & vice versa?

With the two "FY" examples in A1 & A2,

=" "&SUBSTITUTE(A1," (0","(")=A2

and

=TRIM(SUBSTITUTE(A2,"("," (0"))=A1

?,
Andy

3. ### XL Guru

Joined:
Aug 30, 2003
Messages:
2,702
I was waiting for you to figure that was wrong so as not to get out of synch, but I have to run. So the correction is ;

a 4-way OR check to cover whichever format comes first, plus a straight 5th for A=B.

=OR(" "&SUBSTITUTE(A1," (0","(")=A2,TRIM(SUBSTITUTE(A2,"("," (0"))=A1," "&SUBSTITUTE(A2," (0","(")=A1,TRIM(SUBSTITUTE(A1,"("," (0"))=A2,A1=A2)

Rgds,
Andy

4. I just checked the original and one gave me "TRUE", the other "FALSE", but it was a start, I thought.

Thanks for the correction.

I believe that will do it. If the values are equal, I get a "TRUE", if not "FALSE", so, in my formula I can tell it that if Cell A6 (whatever) = "TRUE", do .... and if "FALSE", do different.

I had missed SUBSTITUTE, thank you for pointing me the way. Dick

5. Update:
The formula works great with "FY (09/04)" and " FY(9/04)", but it doesn't work if the months are: 10, 11, or 12. ie "FY (12/04)" and " FY(12/04)". or a combination of different formatted FY. ( "09" and "10"). I ALWAYS use the "FY (mm/yy)" to copy to " FY(m/yy)" if result is "TRUE". I then use:

*******
Private Sub Worksheet_Change(ByVal Target As Range)
Set Target = Range("I8")
If Target = "True" Then
FYChange
End If
End Sub
*******
"FYChange" copies the cell with "FY (mm/yy)" to the cell with " FY(m/yy)".
A question along with this. As the event is working, the cells "blink". Is there something I can do to prevent this, or is it just an anomaly to put up with?

Dick

6. ### XL Guru

Joined:
Aug 30, 2003
Messages:
2,702
Hi Dick. I can't look at the event thing now. All I can (quick-fix) say to cover the other format possibilties is throw in a couple more ORs.

At this rate you'll have enough for a whole rowing team. (What? There's no smiley for wince?!?)

Rgds,
Andy

=OR(" "&SUBSTITUTE(A1," (0","(")=A2,TRIM(SUBSTITUTE(A2,"("," (0"))=A1," "&SUBSTITUTE(A2," (0","(")=A1,TRIM(SUBSTITUTE(A1,"("," (0"))=A2," "&SUBSTITUTE(A1," (","(")=A2,TRIM(SUBSTITUTE(A2,"("," ("))=A1,A1=A2)

7. How about: And the event blinking doesn't bother me all that much.

Thanks again.

8. ### XL Guru

Joined:
Aug 30, 2003
Messages:
2,702
Decided it might be more followable (credit to MBN for inspiring this "word") if broken down into evaluating in 2 chunks, defined by the position of "(".

=AND(SUBSTITUTE(RIGHT(A1,LEN(A1)-FIND("(",A1)+1),"(0","(")=SUBSTITUTE(RIGHT(A2,LEN(A2)-FIND("(",A2)+1),"(0","("),TRIM(LEFT(A1,FIND("(",A1)-1))=TRIM(LEFT(A2,FIND("(",A2)-1)))

Gotta run ; can someone help me sort out my printer later please? Rgds,
Andy

9. I believe the blinking is caused by other events (queries) happening. I think I need to put in a delay for this particular event so that it runs after the other events.

10. On the queries, I have unchecked the "Background Refresh" and have attempted a delay in the code, but I still get blinking of the cells affected.
Also get the blinking if any other changes occur on that page and I suspect it is because it is:

Private Sub Worksheet_Change(ByVal Target As Range)

An example of the change is clicking on a button (macro) to go to another sheet.

I can put up with it, but it isn't neat. Will keep probing and when I find an appropriate answer, will post.