Solved How to sort every nth row?

English
, ,
#1

I cannot do a search, only ask a question.

A column of number values of approximatey inserted into Calc A9:A20
An ascending sort is needed to be repeated every sixth row.
Instead of a click on “Data/Sort” and continuing from there with several given options, could the code from “Data/Sort” be edited to do just one function and be put into a cell something equivalent to =Sort(Ref1:Ref2,Ascending,repeat every 6th row)?
How could one go about accomplishing an “auto sort”?
example.ods
Maybe this will help:
example2.ods
Thanks Check this out Final Example.ods

#2

… and n-1 rows in between n-th rows should be left untouched?

#3

I think he intends to ideally divide the table into blocks of six records each to be sorted separately.

If this is the correct interpretation I think it can only be solved with a macro

#4

I have problems to imagine a use-case, and thats always bad for my motivation.
Not at all I can understand for what reason the OriginalQuestioner changes the order of the columns in his example when sorting.
Next problem with understanding: Why isn’t there a column designating the groups (6 rows each) which obviously must be of high relevance?
It doesn’t make any sense to first define definitely a structure or a view-orianted design, and then to ask others to solve problems probably caused ba inadequate design decisions.
Design along the functionality you need to achieve first. Everything else can wait.
Asking questions it’s a good idea not to ask for a step on an supposed way. The step might address a problem avoidable by a more suitable design from the beginning.

#5

I read your comment by chance only now having not seen it before thinking that there were no other comments after the original request

It’s true. The column for counting blocks of six is fact central for the solution.

In my precedent comment I proposed to ( @Simlpy me ) to insert this column (counter block) by default, as first column, transforming the structure from two to three column

This allows also to obtain different views of the data directly sorting on the place (the macro in this case is very simple) without the need for data duplication

#6

Yes. I read your comment, but I wanted to post my thoughts in order and I stood for a repetition concerning the group column.
I also looked into your solution. In a playful mood I then I tried to get a solution based on standard functions providing immediate (automatic) updates. It wasn’t as inefficient as I had suspected (prepared for 1000 rows with 783 having data). If you want to check:
ask295564sortingInGroupsOf.ods
I wouldn’t suggest to use such overcharged sheets. In addition the solution is only applicable if the one column to sort meets certain needed assurances.

#7

It is only recently that I discovered the existence of these solutions of the type immediate (automatic) updates, while I deepened the macros to sort in the writer-calc area and filter in the scope calc

I study them more to deepen the understanding of certain formulas and techniques (usually particularly long and complex) rather than to use them.

#8

The reasons are:

  1. I have long ago chosen to work on a few very large LO documents (writers and calcs), and therefore all the superfluous must be eliminated

  2. as you pointed out, as records increase, processing times and document weight increase

  3. using macros the traditional sorting and filtering, even on several thousand records is almost instantaneous even on a PC a few years ago, i don’t need immediate update.

  4. after some time getting my hands on those formulas again at my age (> 60 years) becomes really heavy

I have seen your attachment, and I will study it to deepen that kind of really complex but very useful formulas to deepen for the range database applications I am working on.
Processing times of your formulas are excellent

#9

If you can, you can insert before columns A: B a service column with value 1 for the first block of six, 2 for the second block of 6, 3 for the third block of 6, and so on.

Once this is done, copy this new block of three columns A, B, C into columns E, F, G, copying columns A to E, B to G and C to F separately

Finally you order the range E-G with sort keys E (primary) and F (secondary), both numeric and ascending

You should eventually get your sort of block of 6 data at a time while keeping the data-index pair
This solution does not force you to use a macro

ADDENDUM:
To automatically generate the sequence of 6 blocks with six one, six two, six three, etc., you can for example in column L row 1 ( L1 () generate the sequence of integers 1,2,3,4,5,6,7,8,9 (this with calc is super easy)

In M1 enter the formula "= INT (L1 / 6.01) e after drag it for all the cells of the M1 column required

Then copy the obtained sequence where you need it.

ADDENDUM2:
This is my simulation.
In sheet two I reported the data used for the simulation and the formulas used to generate them
Only the repeated blocks 1-6 I edited them with repeated copy and paste

I have also retag your post adding two tag

This solution seems to me to work without the use of macros, but with a simple macro you can automate the whole process, including the generation of the service column and the final exchange of the index-data columns, not performed here

ADDENDUM3: inserted in sheet 2 column C to automatically generate repeating blocks 1-6 to be copied and pasted as needed
sortEveryNth_1614467369499788.ods

ADDENDUM4: Press the “start sort” button to start sorting.

I understand little or nothing about your structure.

I think it would be better to start from a database coding races, dogs, etc. and build the sheet from that
I therefore limited myself to automating blocks of six as required

It is essential that the table to be sorted is the one you provided me, otherwise the column and row references must be replaced.

I think there is a second solution, perhaps faster to try, but this one seems to work.
The calc file must be saved and reopen to work

sortEveryNth_dogs.ods

ADDENDUM5 In the new attachment, I have added in columns X and Y an example of formulas to immediately and automatically sort the first block of eight times

The proposed formulas are quite simple, but they have the limit that if you copy / paste / drag you will lose the connection with the correct subrange of data to be processed.

However, a macro can automatically create the correct formulas for all loaded tabs.

16147023663391483-1copia.ods

#10

Thanks, I am on it right now…

#11

Neither of those seem to do what I’m looking for. edited the origal calc.

#12

Strange, I did a simulation and got the same results as you

The new example2.ods you re-edited confirms the same interpretation I gave for the first one

Here in Italy it is night, tomorrow morning I will attach an ods document with the simulation.

#13

Thank you, that will work, however, my preferred solution would avoid going into ‘Data/Sort’. I am in need of getting more time using this spreadsheet and less time on manual operations. Especially ones involving thousands of rows. You said the dummy service column could be automatically generated but I don’t know how to do that other than “add column before” with not a clue as how to get blocks of 6…000000,111111, etc. 6 AM Sunday morning, thanks again

#14

My entire test plan, 150 records, in the latest version of the attached file was generated automatically.
Find all the formulas in sheet2, including the generation of blocks 000000,111111, etc.

If you insert by default that service column in your data record, passing from two columns (index-data) to three columns (counter block of 6 record-index-data) you have the advantage that if you order by counter-index you get the chronological sorting, if orders by counter-data instead get the data sorted for each block of six

By inserting that column fixed, you can then sort your data on the place by going back (even with an undo) to the previous situation without copy record in other place.

With the sort it is however possible to copy the sorted data in another range leaving the source data unchanged

Consider this possibility. However, I repeat, you can automate everything with a macro.

Once you decide which of the two techniques to use, the macro if you give me a few days I can do it

#15

@simlpyme, if you accept the answer, flag the icon under the counter vote ( color icon, now gray, must become green) and modify the title of your question adding [solved] at the beginning of the title

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.