response following Mark's -----Original Message----- From: [email protected] [mailto:[email protected]] Sent: Monday, October 03, 2011 10:44 To: [email protected] Subject: [users] Re: Calc: Sort double columns
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. We all know about efforts to teach an old dog new tricks, so > let's just be glad that he is interested in this new trick called Open > Office. He will _not_ be moving to Base or anything else, it will be > either in Excel or another spreadsheet. > > 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. > > Example document here: > http://dotancohen.com/images/examples/sort-example.ods > > Thanks. How was this done in Excel? I tried creating a similar sheet in Excel 2003 (at work) and that wouldn't sort with the merged cells either. I think the easiest solution would be move the data from the second line of each record into extra columns on the first line, so each record is only a single line of the table. Then you'd be able to sort or filter on any other column (e.g. age or position) if needed. Depending on the number of records, it may be easy enough to do manually, or you prefer to write a macro, or export to CSV and process through some other software. <orcmid> I think it can all be handled. First, eliminate the merged cell that has the number in it. Just have the top of the two have the number and make the border between the empty cell underneath invisible. Secondly, make a column that has the actual sorting sequence in it. To match the intended number, use something like 1.0, 1.1, etc. (It should be possible to use some formula magic to populate this row-key column with values derived from the cells that actually have the visible numbers. Use the row-key column to sort the entries into the proper sequence. The column can be made invisible and it should still be usable in sorting. Note: If there are other keys that are to be sorted by, and they only appear in one row or another, these need to be expanded into (hidden) keys on each row so they work too. There are ways to do this so sorting by different keys that still keeps the grouping of the paired rows together. </orcmid> Hope that helps. Mark. -- ----------------------------------------------------------------- To unsubscribe send email to [email protected] For additional commands send email to [email protected] with Subject: help -- ----------------------------------------------------------------- To unsubscribe send email to [email protected] For additional commands send email to [email protected] with Subject: help
