1
02902982085   4   a   ?  <-- yellow
02902982085   #   1   r  <-- yellow
02902982085   b   $   0  <-- yellow
01395235224   w   z   [  <-- blue
01395235224   a   -   5  <-- blue
10352351342   r   .   r  <-- yellow
10352351342   z   2   -  <-- yellow
10352351342   2   x   0  <-- yellow
10352351342   q   ]   /  <-- yellow

I want to alternate light yellow and light blue based on first column. The data is grouped by id, namely the first column. Could have a group of 10+ or just 1. How can this be achieved?


(*) The way I am currently doing it is using this ruby script to produce numbers that I can feed into the column and compute =ISEVEN() on them to then do conditional formatting (see image below) on in OpenOffice:

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
}

Which is just ridiculous... Here is the input (which in reality is me just copying and pasting a column from a spreadsheet into a text file) for above program:

02902982085
02902982085
02902982085
01395235224
01395235224
10352351342
10352351342
10352351342
10352351342

Which would give this output:

0    true
0    true
0    true
1    false
1    false
2    true
2    true
2    true
2    true

But I would only produce the numbers and copy the column to the spreadsheet I'm working on, I included the boolean values to help explain my method in (*).

I essentially need a way to get a TRUE FALSE column like this (without using another program and only using OpenOffice formulas/techniques):

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

So that I can perform conditional formatting based on the "forumla is" option, in this case column E. This way all TRUE entries would be blue, and a second condition would be =NOT($E1) which would make all FALSE entries yellow. OpenOffice calc has no way, as far as I know, to be able to switch the value of a dummy variable upon a switch in ID, namely the two FALSE instances, which is why I'm using ruby...


enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.