Notifications
Q&A

TRUE/FALSE flags for alternating colouring by group

+0
−0

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?

Why should this post be closed?

0 comments

1 answer

+0
−0

Yes. Assuming a header row and in OpenOffice Calc 4.1.4.

In E1 enter 0. In E2 enter:

=IF($A2=$A1;$E1;NOT($E1)) 

and copy down to suit.

Where an 'A' value is the same as its immediate predecessor the formula takes the 'E' value from the row immediately above, otherwise it returns the negated version of the preceding 'E' value.

To reverse the colours, change E1 to 1.

0 comments

Sign up to answer this question »