At 13:40 03/10/2011 +0200, Dotan Cohen wrote:
I am reviewing the records of a local shop to help him convert to
Open Office. I have found no problems with the simple Word documents
that he uses, but he is using MS Excel where he should have a real
database. [...] In this particular case, he has information that
should be in a single row (record) divided into to rows, with the A
column's cells merged between the two and the "sorting key" stored
in that cell. He now wants to sort based on the merged cells,
keeping the dual-row records intact.
Example:
---------------------------
1 | Some info
| More info
---------------------------
3 | Yet some more info
| And more info
---------------------------
2 | Some important info
| More important info
---------------------------
He now needs to sort that! Does anybody know how this can be
done? Trying the regular sort function returns an error that range
containing merged cells can only be sorted without formats.
That error is a trivial problem: when you sort, just go to the
Options tab in the Sort dialogue and remove the tick from "Include formats".
Example document here:
http://dotancohen.com/images/examples/sort-example.ods
At 12:15 07/10/2011 +0200, Dotan Cohen wrote:
The issue is more of a mess than I thought! Not only does the user
need to sort on double rows, but the number that he is sorting on is
not unique! Therefore, simply creating a second cell with a derived
sort number will not work.
That's not true. Try this:
o Choose a column for your sort key; I used column G.
o In G2, enter: =IF(A2=0;G1;A2+ROW(A2)/1000)
o Fill that formula up to G1 and down the rest of the relevant part
of column G - or just copy it, select the relevant part of column G, and paste.
o Select all the material to be sorted, and sort on column G (with
"Include formats" unticked).
Your double rows with identical keys in column A will be sorted
properly and will stay - as between themselves - in the same order as
they were before the sort. I'm assuming that you don't have more
then 1000 rows of this data; otherwise you'd have to increase the
divisor in the formula. You can either delete or hide column G
afterwards if you prefer. You'll want to keep it if you'll need to
sort additional data later.
Why did I start with G2 instead of G1? Well, the formula in G1 makes
reference to G0, but that is no problem if you start with G2. If
your table started anywhere other than in row 1, you could compose
the formula in the first row of the table without problem.
It looks like the real choice here is to either stay with his
current solution, or to move to a real database (as it was in the
very beginning).
Or use Calc ...
I trust this helps.
Brian Barker
--
-----------------------------------------------------------------
To unsubscribe send email to [email protected]
For additional commands send email to [email protected]
with Subject: help
