1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Cells With Different Information are "Equal"

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

Thread Status:
Not open for further replies.
Advertisement
  1. RHurlburt

    RHurlburt Thread Starter

    Joined:
    Oct 31, 2003
    Messages:
    130
    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

    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

    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. RHurlburt

    RHurlburt Thread Starter

    Joined:
    Oct 31, 2003
    Messages:
    130
    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. (y)

    Dick
     
  5. RHurlburt

    RHurlburt Thread Starter

    Joined:
    Oct 31, 2003
    Messages:
    130
    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

    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. RHurlburt

    RHurlburt Thread Starter

    Joined:
    Oct 31, 2003
    Messages:
    130
    How about: :rolleyes:

    And the event blinking doesn't bother me all that much.

    Thanks again.
     
  8. XL Guru

    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. RHurlburt

    RHurlburt Thread Starter

    Joined:
    Oct 31, 2003
    Messages:
    130
    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. RHurlburt

    RHurlburt Thread Starter

    Joined:
    Oct 31, 2003
    Messages:
    130
    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.
     
  11. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/224722

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice