Finding last occurrence of a label and its value in Spreadsheet

	A	B	C	D	E	F
1		A	11		A	19
2		B	12		B	16
3		C	13		C	15
4		D	14		D	18
5		C	15
6		B	16
7		A	17
8		D	18
9		A	19

Finding a value of label that occurs as last in a large list is always complex one. Spreadsheet's lookup functions will helps up to some level but not completely. Using the following workaround, its possible and the desired value can be populated.

Feed the below values in respective cells and press CTRL+SHIFT+ENTER to run the script.

F1 = INDEX($C$1:$C$9,MAX(ROW($A$1:$A$9)*($B$1:$B$9=$E1)))
F2 = INDEX($C$1:$C$9,MAX(ROW($A$1:$A$9)*($B$1:$B$9=$E2)))
F3 = INDEX($C$1:$C$9,MAX(ROW($A$1:$A$9)*($B$1:$B$9=$E3)))
F4 = INDEX($C$1:$C$9,MAX(ROW($A$1:$A$9)*($B$1:$B$9=$E4)))

Here Column "A" was referred just for row number reference nothing else. Column "A" may have any value or empty.

Note: Labels are A-D and values are numeric values.

Find string from list of ODT files in Linux

In Linux, a string pattern can be searched using grep command from one or more files from a directory (including its sub directories). Normally the search will be easy when in the case of text files including program source code files like c, PHP, Java and etc. At the same time a search from list of ODT files from a directory is not that easy.

This can be also possible with the use of utility command such as find, unzip and grep. The below code will done the job perfectly. The parameter can be changed upon your wish. This program will output file name(s) which contains the search string.

#content of searchodt.sh

#!/bin/bash

if [ $# -ne 1 ]; then
    echo "Usage: sh searchodt.sh <searchterm>"
    exit 1
fi

for file in $(find . -name "*.odt" -type f); do
    unzip -ca "$file" content.xml | grep -qil "$1"
    if [ $? -eq 0 ]; then
        echo "$file"
    fi
Done

Selective cell protection in Spreadsheet

Protecting sheet will locks the entire sheet and not allows users to edit any cell value. In some scenarios, some of the limited cells can be locked by using the following workaround.

• Select the cells that not to be locked.
• Right click and select "Format" and then Select "Cell Protection" Tab.
• Uncheck the protected option to skip from lock.
• Select Tools Menu → Protect Document → sheet and give password to lock entire sheet. Uncheck "Select Unprotected cells" option to prohibit user even to select the protected cells if needed. 

Here after user can able to enter or modify the values only on allowed cells.

Regular Expressions on OOo Calc

• Most characters match themselves. There are exceptions - see below.
  COUNTIF(A1:A100; "foo") will return all the instances of the string "foo" in the specified range.
• "." matches any single character.
  COUNTIF(B2:B18; "..") counts all cells with exactly two characters.
• "*" is a special character that matches zero or more occurences of the previous expression.
  COUNTIF(B2:B18; ".*e") counts all cells that end in "e".
• "+" is a special character that matches one or more occurences of the previous single character.
  COUNTIF(B2:B18; ".+m.+") includes "Tmmy" and "name", but not "my".
• A string of characters enclosed in square brackets ([]) matches any one character in that string.
  COUNTIF(B2:B18; "[efg].*") counts all cells starting with e, f, or g.

Removing Duplicate Rows in Open Office Calc

There is no ready made solution to remove duplicate items in spreadsheet. Use the following method to keep the sheet clean from duplicates.

1. Create one new column before or after the column needs to be filtered. (ie) if column "A" need to be filtered then create before to "A" Now "A" become "B" and new one become as "A".
2. Enter '1' in cell "A1" and in "A2", enter the formula =if(B2=B1,0,1). 
3. Then copy the cell "A2" and paste it through out the rows the sheet have. (ie A2 - Axx)
4. Now the Column "A" contains only 0's and 1's. It means that the row contains '1' or unique values and '0' means duplicates.
5. Now, copy the entire "A" column and place the cursor in "A1".
6. Then select 'Special paste' from Edit menu or else click 'Ctrl+Shift+V' to get that.
7. In that, un-check the 'Paste all' option and un-check all the options except 'Text and Numbers'. Then press 'OK'. It will paste only the values not formula's.
8. Now insert one new row at the top of all and enter 1 on cell "A1".
9. Now place the cursor in "A1" and click sort button in tools bar. This will sort the entire sheet with respect to column "A".
10. Now, the duplicate values with '0' are all in top of the sheet and unique rows are resides at bottom.
11. Now, remove all the rows which are all having value '0' in column "A".
12. Now the sheet is cleaned and remove the column "A" to get the exact list without duplicates.

For MS-Office some steps differs but the concept is same.

Use of one cell value as a comment in another

    Sub Add_Comments()
        strSheet1Name = "Sheet1"
        strSheet2Name = "Sheet2"
        strSheet2Col = "F"
        strSheet1Col = "P"
        ActiveWorkbook.Sheets(strSheet2Name).Activate
        For intRow = 2 To ActiveSheet.Range(strSheet2Col & "65536").End(xlUp).Row
        If ActiveSheet.Range(strSheet2Col & intRow).Comment Is Nothing Then
            If ActiveWorkbook.Sheets(strSheet1Name).Range(strSheet1Col & intRow).Value <> "" Then
            ActiveSheet.Range(strSheet2Col & intRow).AddComment ActiveWorkbook.Sheets(strSheet1Name).Range(strSheet1Col & intRow).Value
            End If
        Else
            ActiveSheet.Range(strSheet2Col & intRow).Comment.Delete
            If ActiveWorkbook.Sheets(strSheet1Name).Range(strSheet1Col & intRow).Value <> "" Then
            ActiveSheet.Range(strSheet2Col & intRow).AddComment ActiveWorkbook.Sheets(strSheet1Name).Range(strSheet1Col & intRow).Value
            End If
        End If
        Next
        MsgBox "Done"
    End Sub

Colorize the cell respect to the value

    Using style function with if condition we can do
   

    STYLE(IF(CURRENT()=0;"def";IF(CURRENT()>0;"positive";"negative")))

    Ref : http://wiki.services.openoffice.org/wiki/Documentation/How_Tos/Calc:_STYLE_function

Err: Wrong Datatype (#Value will be displayed)

Error:

    Error: Wrong Datatype (#Value will be displayed)

Solution:
    This is because, by Default Macros are disabled for security reasons. Enabling of this macro in Security settings will solve this.

Extract only numbers and text generated by formulas and paste in new range

1. Press Shift+Ctrl+V to open the Paste Special dialog box.
2. From the option list, enable only the Text, Numbers and Click OK.

        This will gives only values not formula.

          Ref : http://blogs.sun.com/oootnt/entry/removing_duplicate_rows_in_calc