Office


LibreOffice Calc: Finding duplicate / common entries between two columns

This video demonstrates how to find all matching values between two columns in LibreOffice Calc. Precisely, in this video, we fill in columns A and B with random integer values between the numbers 1 and 100 (inclusive). The formula to generate the random values was the following:

=RANDBETWEEN(1;100)

After we filled in columns A and B with random values, we used the following formula in each cell of column C to find all common values between the first two columns:

=IF(ISERROR(MATCH(B1;A:A;0));"";B1)

LibreOffice Cal commands used in this video

  • RANDBETWEEN(Bottom; Top) – Returns an integer random number in a specified range.
  • IF(Test; ThenValue; OtherwiseValue) – Specifies a logical test to be performed.
  • ISERROR(Value) – Tests for error conditions, including the #N/A error value, and returns TRUE or FALSE.
  • MATCH(SearchCriterion; LookupArray; Type) – Returns the relative position of an item in an array that matches a specified value. The function returns the position of the value found in the lookup_array as a number.


Microsoft Outlook: Auto Reply

1) Click the File tab at the top-left corner of the Outlook display.

2) Click the box to Automatic Replies box to the immediate left of the Automatic Replies (Out of Office) text.

3) In the Automatic Replies box that appears, click the circle to the left of the Send automatic replies option.

4) Fill the box with the desire information and click ok
You don’t need to add rules, as you want the auto reply to be sent for every incoming email.


Microsoft Outlook: Auto CC in Emails

1) Click File in your Outlook Inbox.

2) Go to the info category.
3) Make sure the account for which you want to set up automatic Cc copies is selected under Account Information.
4) Click Manage Rules & Alerts.

5) Go to the E-mail Rules tab.
6) Click New Rule….

7) For Step 1: Select a template, make sure Apply rule on messages I send is selected (under Start from a blank rule).

8) Click Next >.
9) Click Next > again.
You can pick criteria for the messages you want to copy via Cc; if you select nothing, however, all emails will be added the CC: recipients.

10) If you are prompted:
Under This rule will be applied to every message you send. Is this correct?, click Yes.

11) Under Step 1: Select action(s), make sure CC the message to people or public group is checked.

12) Under Step 2: Edit the rule description, click people or public group.

13) Double-click any recipients (or lists) from your address book, or enter email addresses directly under To ->; these addresses will receive the CC: copies.
Separate email addresses under To -> with semicolons (;).

14) Click OK.
15) Now click Next >.
16) Optionally, under Are there any exceptions?, specify any exceptions to the Cc: sending rule .
17) Click Next >.
18) Typically, precede the email address or addresses entered under Step 1: Specify a name for this rule with something like “Automatically Cc “.

19) Also typically, make sure Run this rule now on messages already in “Inbox” is not checked.
20) Click Finish.

21) Now click OK.


LibreOffice Calc: Get workbook path only

Get workbook path only

For Linux and Mac

=LEFT(
  CELL("filename"),
  FIND(
    CHAR(1),
    SUBSTITUTE(
      CELL("filename"),
      "/",
      CHAR(1),
      LEN(CELL("filename")) - LEN(
        SUBSTITUTE(
          CELL("filename"),
          "/",
          ""
        )
      )
    )
  ) -1
)&"'"

For Windows

=LEFT(
  CELL("filename"),
  FIND(
    CHAR(1),
    SUBSTITUTE(
      CELL("filename"),
      "\",
      CHAR(1),
      LEN(CELL("filename")) - LEN(
        SUBSTITUTE(
          CELL("filename"),
          "\",
          ""
        )
      )
    )
  ) -1
)&"'"

Bonus

If you want to remove the ' characters as well that are around the filename and path use the following  solution, this will allow you to create hyperlinks for the folders as well

=REPLACE(
  LEFT(
    CELL("filename"),
    FIND(CHAR(1),
      SUBSTITUTE(
        CELL("filename"),
        "/",
        CHAR(1),
        LEN(CELL("filename")) - LEN(
          SUBSTITUTE(
            CELL("filename"),
            "/",
            ""
          )
        )
      )
    ) -1
  )
  ,1,1,""
)

To create hyperlink as well, use the following

=HYPERLINK(
  REPLACE(
    LEFT(
      CELL("filename"),
      FIND(CHAR(1),
        SUBSTITUTE(
          CELL("filename"),
          "/",
          CHAR(1),
          LEN(CELL("filename")) - LEN(
            SUBSTITUTE(
              CELL("filename"),
              "/",
              ""
            )
          )
        )
      ) -1
    )
    ,1,1,""
  )
)