+ Reply to Thread
Results 1 to 14 of 14

Notify already insert

  1. #1
    Registered User
    Join Date
    02-04-2020
    Location
    Italy
    MS-Off Ver
    Libre Office
    Posts
    6

    Notify already insert

    A B C D
    1 ID Italian Source To Translate ..
    2 1 Ciao foo foo
    3 2 Buon giorno foo1 ..
    4 3 Buona sera foo2 ..
    5 4 Ciao foo


    Hi, I have to report in column D if the terms have already been translated. So the translator will not have to translate the duplicate items. He will notes so that the terms are already traslated.
    So he will avoid translating published the term contained in B5 and he will avoid compiling cell C5. Is there a function to insert in D cells
    Last edited by Robyf1; 02-04-2020 at 06:01 AM.

  2. #2
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    365 and rarely 2016
    Posts
    3,198

    Re: Notify already insert

    =IFERROR(LOOKUP(1,1/((b$2:b$5=b2)*(c$2:c$5<>"")),c$2:c$5),"") in cell d2

    copy down, it will also complete d3 and d4 as the value in c3 and 4 and not blank, but I would want that, as it is only the missing values you complete

    you may need to change , for ; depending on you locale
    Last edited by davsth; 02-04-2020 at 06:17 AM.

  3. #3
    Registered User
    Join Date
    02-04-2020
    Location
    Italy
    MS-Off Ver
    Libre Office
    Posts
    6

    Re: Notify already insert

    this is a static example, the real case will include many lines with different duplicates

  4. #4
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    365 and rarely 2016
    Posts
    3,198

    Re: Notify already insert

    what is your point?

    You would extend the ranges to the appropriate size for your data. What does the formula not do? If you want a solution you have to be a bit more specific where it is not meeting your needs?

    How does it error or not work?

  5. #5
    Registered User
    Join Date
    02-04-2020
    Location
    Italy
    MS-Off Ver
    Libre Office
    Posts
    6

    Re: Notify already insert

    err:501 in D1
    err:508 in D2, D3, D4 and D5

  6. #6
    Registered User
    Join Date
    02-04-2020
    Location
    Italy
    MS-Off Ver
    Libre Office
    Posts
    6

    Re: Notify already insert

    could you turn the document over, can't I attach?

  7. #7
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    365 and rarely 2016
    Posts
    3,198

    Re: Notify already insert

    firstly I believe iferror works in libre office? can you confirm this is true?

    Secondly do you need to change the , to ; in the formula for the region you are in? as I mentioned in the first post?

  8. #8
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    365 and rarely 2016
    Posts
    3,198

    Re: Notify already insert

    you can attach as per the instructions in the yellow bar

  9. #9
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    365 and rarely 2016
    Posts
    3,198

    Re: Notify already insert

    As everyone on here is a volunteer you should be making their lives easier and attaching your file. However as it is your first time I have done it for you
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    02-04-2020
    Location
    Italy
    MS-Off Ver
    Libre Office
    Posts
    6

    Re: Notify already insert

    200204_00756.png

    see image please

  11. #11
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    365 and rarely 2016
    Posts
    3,198

    Re: Notify already insert

    Yes see the yellow bar at the top of the screen
    HOW TO ATTACH YOUR SAMPLE WORKBOOK:
    davsth Fast answers need clear examples. Post a small Excel sheet (not a picture) showing realistic & representative sample data WITHOUT confidential information (10-20 rows, not thousands...) and some manually calculated results. Just before posting, scroll down to GO ADVANCED, click, and then scroll down to MANAGE ATTACHMENTS and click again. Now follow the instructions at the top of that screen.


    Go advanced is just below this box when you type a reply.
    then there is a manage attachments link that you can click on and then attach the file

    but i sent you the solution anyway (hopefully)

  12. #12
    Registered User
    Join Date
    02-04-2020
    Location
    Italy
    MS-Off Ver
    Libre Office
    Posts
    6

    Re: Notify already insert

    Quote Originally Posted by davsth View Post
    =IFERROR(LOOKUP(1,1/((b$2:b$5=b2)*(c$2:c$5<>"")),c$2:c$5),"") in cell d2

    copy down, it will also complete d3 and d4 as the value in c3 and 4 and not blank, but I would want that, as it is only the missing values you complete

    you may need to change , for ; depending on you locale
    Thank you so much. Could you, please explain the formula to me?

  13. #13
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: Notify already insert

    Moved to correct forum

  14. #14
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    365 and rarely 2016
    Posts
    3,198

    Re: Notify already insert

    iferror if the lookup doesn't find a match, it returns #n/a the iferror just turns this to a blank
    iferror(formula,what to do if formula is an error)

    you are looking up a 1 b2:b5=b2 returns a list of true and falses, as does c2:c5 multipled together they becomes 1s and 0s

    true* true=1
    true*false=0
    false*false=0
    false*true=0

    A 1 only occurs when the value form colum b matches and the value from column c is not blank)


    1/this list produces a line of 1s and div /0 the first match is the match you want and return the value from column C

    LOOKUP(1,1/((B$2:B$5=B2)*(C$2:C$5<>"")),C$2:C$5)

    is
    LOOKUP(1,1/(({TRUE;FALSE;FALSE;TRUE})*({TRUE;TRUE;TRUE;FALSE})),C$2:C$5)
    then
    LOOKUP(1,1/{1;0;0;0},C$2:C$5)
    then
    LOOKUP(1,{1;#DIV/0!;#DIV/0!;#DIV/0!},C$2:C$5)

    in the above the first match is in the first row, so it returns the value in C2

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Read-Only or Notify?
    By ell_ in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-23-2018, 04:05 AM
  2. Notify user that a file is locked upon running insert/update query
    By Snoopy2003 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-21-2012, 04:59 PM
  3. Notify me of any changes in workbook
    By jsmity in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 01-16-2008, 11:35 AM
  4. [SOLVED] Notify Message?
    By MAB in forum Excel General
    Replies: 0
    Last Post: 08-03-2006, 11:50 AM
  5. [SOLVED] Notify
    By Greg in forum Excel General
    Replies: 3
    Last Post: 04-05-2006, 11:40 PM
  6. Can Excel notify me when a date is due?
    By Claire Chandler in forum Excel General
    Replies: 3
    Last Post: 02-08-2005, 12:06 PM
  7. Help finding value and notify
    By funkymonkUK in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-04-2005, 06:27 AM

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