For use in connection with conditional formatting with alternating coloured fills in OpenOffice Calc, several years ago a user of Super User was applying this Ruby script:

 f = File.readlines("shading.txt") #<-- I just copy and paste a column from spreadsheet to here

$i = 0
$switch = 0

open('shading_out.txt','a'){|g|
while $i < f.size do
    if f[$i] == f[$i+1]
        g.puts ($switch).to_s + "    " + ($switch).even?.to_s
    else
        if $i == (f.size-1)
        $switch-=1
        g.puts ($switch).to_s + "    " + ($switch).even?.to_s
        else
            g.puts ($switch).to_s + "    " + ($switch).even?.to_s
            $switch+=1
        end
   end
    $i += 1 
end
}

to derive TRUE/FALSE flags by group from a copy of a column of grouped IDs. The sample provided was provided twice, once blue/yellow/blue:

02902982085   4   a   ?  TRUE  <-- true's would be blue
02902982085   #   1   r  TRUE
02902982085   b   $   0  TRUE
01395235224   w   z   [  FALSE <-- false's would be yellow, i.e., =NOT($E1) would be true for second conditional (see image below)
01395235224   a   -   5  FALSE
10352351342   r   .   r  TRUE
10352351342   z   2   -  TRUE
10352351342   2   x   0  TRUE
10352351342   q   ]   /  TRUE

and once yellow/blue/yellow. The TRUE/FALSE indicator was required in Column E.

The Q was essentially whether it was possible to avoid reliance on Ruby and the work that entailed.

Is there a way within OpenOffice Calc to generate a column of suitable TRUE/FALSE values from such data?

posted about 4 years ago

CC BY-SA 4.0

4y ago by Alexei‭

pnuts‭

407 reputation 21 14 8 46