+ Reply to Thread
Results 1 to 10 of 10

How to automatically check for early identical entries?

  1. #1
    Registered User
    Join Date
    02-15-2011
    Location
    Albania
    MS-Off Ver
    Excel 2003
    Posts
    5

    Lightbulb How to automatically check for early identical entries?

    Cheers everyone,

    I am doing a translation and I am using Excel. There are two columns in my worksheet. Column A with the original text and column B with the translation. Every once in a while I run into a word I have translated before.

    Is there anyway I could have excel checking if the value of cell A (x) is equal to a value A (1-x) (eg. A8) if so then fill cell B (x) with the corresponding value (eg. of the cell B8).

    Perhaps it is a simple conditional formating but I can't figure out the formula.

    Thank you.

    D
    Last edited by dori1; 02-15-2011 at 11:12 AM.

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: How to automatically check for early identical entries?

    If you are making hard coded inputs in column B, then if you have a formula in B, the hard-coding will wipe out the formula.... if it is one-time deal, then it shouldn't matter.



    You can start column B, cell B2, with formula like:

    =IF(A2="","",IF(ISNUMBER(MATCH(A2,A$1:A1,0)),VLOOKUP(A2,A$1:B1,2,0),""))

    assuming you are starting in Row 2.

    Then copy down as far as you want.

    Now as you enter items in column A, if a translation already appeared above, then it should show up... if it doesn't show, then overwrite the formula.

    When it is all done, you can copy column B and paste special over itself, selecting Values option so that all formulas are gone. (optional)

    Would that work?
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Valued Forum Contributor Blake 7's Avatar
    Join Date
    10-01-2010
    Location
    Bogota, Colombia
    MS-Off Ver
    Excel 2010 64 bit and Excel 2007,
    Posts
    1,377

    Re: How to automatically check for early identical entries?

    See N's post
    Blake 7

    If your question has been answered, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

    If any member's response has helped to solve your problem, please consider using the scales icon on the top right of their post to show your appreciation.

    http://failblog.org/

  4. #4
    Registered User
    Join Date
    02-15-2011
    Location
    Albania
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: How to automatically check for early identical entries?

    Hi NBVC

    Apparently there is an error in the formula, I can not test it. Would it be possible for you to send me the formula in an excel sheet/cell?

    Thanks a lot.

    D
    Last edited by NBVC; 02-15-2011 at 10:50 AM. Reason: removed email address

  5. #5
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: How to automatically check for early identical entries?

    Try this version:

    =IF(A2="";"";IF(ISNUMBER(MATCH(A2;A$1:A1;0));VLOOKUP(A2;A$1:B1;2;0);""))

    your regional settings probably uses semi-colons instead of commas as argument separators

  6. #6
    Registered User
    Join Date
    02-15-2011
    Location
    Albania
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: How to automatically check for early identical entries?

    Yep it is working.

    Thanks a lot.

    Cheers

    D

  7. #7
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: How to automatically check for early identical entries?

    Great...

    I will delete your email address to avoid spam in your inbox

  8. #8
    Registered User
    Join Date
    02-15-2011
    Location
    Albania
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: How to automatically check for early identical entries?

    Quote Originally Posted by NBVC View Post
    Great...

    I will delete your email address to avoid spam in your inbox
    Super. Thank you again.

    Bests

    Dorian

  9. #9
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: How to automatically check for early identical entries?

    If you are satisfied with the solution(s) provided, please mark your thread as Solved.

    How to mark a thread Solved
    Go to the first post
    Click edit
    Click Go Advanced
    Just below the word Title you will see a dropdown with the word No prefix.
    Change to Solved
    Click Save

  10. #10
    Registered User
    Join Date
    02-15-2011
    Location
    Albania
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: How to automatically check for early identical entries?

    Done.

    Takes a little time to learn 'where is what'.

    thanks

    D

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1