How does the sorting work?

[Lautibah]

I can sort a column using "more options" feature. How does it work? I have found that for me it does nothing in particular.
I thought it would work like: "sort this column take into account this column first but also according to this second column". But in the end it just sorts accordint the first option you choose, so I don't know what I am not doing OK.

Offtopic: I have a bit more than 1 year remaning for defending my thesis, I don't know if after this I will keep working in the same/using magic plot! Meantime I will keep this forum alive with my strugglings.

Cheers.

[MagicPlot]

Hi, great to hear you

Sorting by multiple columns works as follows. The program sorts rows by the first selected column and if the values in the first column are the same, the second column is taken into account. As an example, imagine sorting of a table with family names and given names columns: first sort is by family name column and for the same family name the given names will be sorted.

[Lautibah]

Hello!

Ok, so the "more options" feature actually does something just in the situation two rows matches... that's explains why it does not work to me.

I thought it would work kind of the following example:
two columns
0.12 1
0.21 1
0.13 2
0.23 2
0.11 3
0.22 3

if you sort according to B ascending mode it will be as the shown above. But if you set sort B taking into account A it would be something like:

0.12 1
0.13 2
0.11 3
0.21 1
0.23 2
0.22 3

That is what I am looking for. Not sorting purelly by A, neither by B, just a combination of those which sorts them simultaneously but being B more important (note that the matrix does not match exactly the sort according to A)


Well, I will do it by hand.

Thank you!

[MagicPlot]

If your order data is regular as in the example, you can automate sorting using column formulas:

Code: Select all

col(C) = cell(A, 2*((i-1)%3 + 1) - 1 + floor((i-1)/3))
col(D) = cell(B, 2*((i-1)%3 + 1) - 1 + floor((i-1)/3))

Please check.

[Lautibah]

Well, it was just an example but... yes, now that you say so... I can sort normally the column B and they end up having a logical structure. (this is not all my data, but I do not want to overload the post)

Code: Select all

1.9999039875	1520.0
2.249798275	1520.0
2.49959955	1520.0
2.7487386125	1520.0
2.998695925	1520.0
3.2452073625	1520.0
3.4923461875	1520.0
3.7404124125	1520.0
3.993652675	1520.0
4.2414028625	1520.0
4.4889019	1520.0
4.7402402875	1520.0
4.9885895375	1520.0
5.2355169625	1520.0
5.48557775	1520.0
5.7336463375	1520.0
5.9827214375	1520.0
6.229739725	1520.0
6.4866197875	1520.0
6.727707975	1520.0
6.9809214625	1520.0
7.2297463	1520.0
7.4775027625	1520.0
7.7266490875	1520.0
7.9750064375	1520.0
8.223740575	1520.0
8.4723432125	1520.0
8.7207779875	1520.0
8.97076095	1520.0
9.2182338375	1520.0
9.468098275	1520.0
9.7161010375	1520.0
9.965190875	1520.0
10.213976625	1520.0
10.4631507375	1520.0
10.7158914875	1520.0
10.96147025	1520.0
2.0181054125	1230.0
2.2636254375	1230.0
2.4999211625	1230.0
2.746596125	1230.0
2.9952361625	1230.0
3.2441272625	1230.0
3.492939525	1230.0
3.7409092	1230.0
3.9907117625	1230.0
4.2399452	1230.0
4.4893428625	1230.0
4.739021	1230.0
4.987546075	1230.0
5.23341925	1230.0
5.4824998875	1230.0
5.7324516	1230.0
5.979584225	1230.0
6.2272562875	1230.0
6.4864577125	1230.0
6.7247040125	1230.0
6.9791418375	1230.0
7.22762185	1230.0
7.4763866	1230.0
7.723737825	1230.0
7.9725286375	1230.0
8.2219579125	1230.0
8.4698716625	1230.0
8.7190154625	1230.0
8.9685862125	1230.0
9.2154527875	1230.0
9.4651402375	1230.0
9.7140369375	1230.0
9.9634635375	1230.0
10.2109833	1230.0
10.460543625	1230.0
10.7129702625	1230.0
10.95897315	1230.0
2.00483235	1000.0
2.255921525	1000.0
2.49849005	1000.0
2.7472214375	1000.0
2.9957221	1000.0
3.2412446625	1000.0
3.489550875	1000.0
3.739908125	1000.0
3.990648375	1000.0
4.234449075	1000.0
4.48872825	1000.0
4.738550925	1000.0
4.983862225	1000.0
5.231144	1000.0
5.4806609125	1000.0
5.7306467375	1000.0
5.978887425	1000.0
6.2262231625	1000.0
6.4825285	1000.0
6.7251319125	1000.0
6.977442875	1000.0
7.22541155	1000.0
7.473664725	1000.0
7.7219040625	1000.0
7.9706480625	1000.0
8.2196188	1000.0
8.46754445	1000.0
8.717067725	1000.0
8.9663373125	1000.0
9.2125884125	1000.0
9.463503125	1000.0
9.71140945	1000.0
9.9609671625	1000.0
10.2080316625	1000.0
10.458187825	1000.0
10.71017705	1000.0
10.956201425	1000.0
2.005487725	800.0
2.2479658125	800.0
2.4981080125	800.0
2.7468856125	800.0
2.9926966	800.0
3.2427350375	800.0
3.4900070625	800.0
3.7381591375	800.0
3.9878311	800.0
4.2338450125	800.0
4.48736975	800.0
4.7348803875	800.0
4.982510075	800.0
5.2316602	800.0
5.4790265125	800.0
5.7267727125	800.0
5.977712625	800.0
6.223872725	800.0
6.48452055	800.0
6.72141605	800.0
6.9752290875	800.0
7.2233098375	800.0
7.4717137625	800.0
7.7198367625	800.0
7.9683928875	800.0
8.2169066375	800.0
8.4652101875	800.0
8.7142777375	800.0
8.9637447625	800.0
9.209826225	800.0
9.460314025	800.0
9.7088575125	800.0
9.95843945	800.0
10.20562875	800.0
10.45513095	800.0
10.7075472875	800.0
10.953199425	800.0
2.0137500125	500.0
2.24616845	500.0
2.4963102375	500.0
2.7419336	500.0
2.990849975	500.0
3.2413318625	500.0
3.4906813125	500.0
3.7380123625	500.0
3.9862452625	500.0
4.2353172125	500.0
4.4843306875	500.0
4.7326598125	500.0
4.9811285	500.0
5.22878855	500.0
5.4781502875	500.0
5.723986925	500.0
5.9746759125	500.0
6.22094955	500.0
6.48112185	500.0
6.720388575	500.0
6.973745825	500.0
7.2215110125	500.0
7.46948575	500.0
7.7177876875	500.0
7.9654950875	500.0
8.2147774375	500.0
8.4626547	500.0
8.7112202625	500.0
8.9606778625	500.0
9.207604525	500.0
9.457576875	500.0
9.70512315	500.0
9.954262975	500.0
10.202600725	500.0
10.4515206625	500.0
10.705358475	500.0
10.95007705	500.0
2.0027664125	300.0
2.2451857375	300.0
2.4962372125	300.0
2.74437065	300.0
2.99059595	300.0
3.238586375	300.0
3.488166475	300.0
3.73424435	300.0
3.98426335	300.0
4.2329864625	300.0
4.4843436375	300.0
4.7309939375	300.0
4.979997475	300.0
5.2264629625	300.0
5.475833975	300.0
5.7241262625	300.0
5.971580875	300.0
6.22371275	300.0
6.4808926625	300.0
6.7152094125	300.0
6.9716933375	300.0
7.2199109125	300.0
7.466876425	300.0
7.7157575375	300.0
7.963047925	300.0
8.2121762	300.0
8.4602526125	300.0
8.7082577875	300.0
8.958006975	300.0
9.2047651875	300.0
9.4545288125	300.0
9.7020319625	300.0
9.951762475	300.0
10.1995198875	300.0
10.4484150125	300.0
10.7020626125	300.0
10.9465378375	300.0
2.000499175	100.0
2.2422946	100.0
2.49634885	100.0
2.74093095	100.0
2.990096425	100.0
3.2397149625	100.0
3.4851076125	100.0
3.733795725	100.0
3.9847520125	100.0
4.2319577875	100.0
4.481433875	100.0
4.73140155	100.0
4.978300875	100.0
5.2254254875	100.0
5.474998125	100.0
5.7208187375	100.0
5.970780975	100.0
6.218879275	100.0
6.4772856875	100.0
6.7101487	100.0
6.9689156375	100.0
7.215745575	100.0
7.4643269125	100.0
7.71246095	100.0
7.9601709875	100.0
8.21008255	100.0
8.4561731875	100.0
8.7043382	100.0
8.955314775	100.0
9.2007954	100.0
9.4505521	100.0
9.6996354875	100.0
9.9490312375	100.0
10.1964166	100.0
10.4442669125	100.0
10.6994749375	100.0
10.9433533	100.0

You see that for each different column B value, the corresponding column A value is almost equal and that's what I want
1.9999039875 1520.0
2.0181054125 1230.0
2.00483235 1000.0

I have tried to develop my own function after checking yours but I ended up understanding yours and what I should change.
cell(B, 37*((i-1)%17 + 1) - 1 + floor((i-1)/17))
this works well because I have 37 different values in column A for each B different value, and I have 17 different B values. Cool, thanks

[MagicPlot]

I have tried to substitute the 3 on your formula for 37 (number of values of A for each different B values)

It's 2, not 3, that need to be changed. 3 is the number of different values in B column. It's hard to operate with such formulas with Indexes because we address here the 'source' index knowing the 'target'. Looks like a 'reversed problem'.

[MagicPlot]

That's good that it works!

[Lautibah]

Wow, that fast reply... I have just edited my post
thank you!

I have seen now your edit: I agree with you, it is a patch that only works because we know the distribution. It will fail if the system is not 100% predictable (exact distribution for each different B values)
I could develop a better system, that is based on if. It could work, search for the minimum of B, and for each index substract the corresponding B value, if it is negative we are still working with the same B value, otherwise jump to next.

I will give it a try
Edit: oh, I cannot do any for. I will keep working on this, thanks and sorry for the inconveniences!

[MagicPlot]

We are editing simultaneously

There is another idea how to deal with it. You can add a column C with temporary index:

Code: Select all

A B C
0.12 1 1
0.21 1 2
0.13 2 1
0.23 2 2
0.11 3 1
0.22 3 2

and then sort the entire table by 'C, then B'. It can be faster to implement, maybe.

[Lautibah]

- Bad news for the formula. I got excited (as always) because I saw that for column B it work and then I posted it... it works but for half of my system. Lately I checked that for column A it is not that good.
- But also, good news:
I have understood the last part. You propose to use the "more option" feature to first order by "temporary index" and second by column B, that way for duplicated temporary index they will be sorted.
Just doing simple maths this second option is the one that actually WORKED AS A CHARM

Find attached an example of one of my data tables (I have pleeeeeeenty, for one table it tooks me from 10-15 min to do it by hand). I just spend the whole day for some tables and still have many to do. With this method it will be easy job


Edit: good night, Saint petersburg is almost 4 am, rest!

[Lautibah]

Hello again.

Is there any way to print from an "if" a text into a column? I have tried to set up a column in text format but I am not able to print a string.

I have realized that the last method you spoke me about (temporary index) works nice, but not aaaalways. Because my data is experimental, sometimes there is values that are exchanged. I want to write a fourth column in which I do a fast check, so I would like to use the following formula
if(cell(B,i)-cell(B,i-1)<0, if(cmax(B)-cmin(B)-abs(cell(B,i)-cell(B,i-1)) == 0,"","Weird value"),"")

cell(B,i)-cell(B,i-1)<0 is only going to be true when we change between temperatures in my examples (find attached a document in which the formula is applied to check the weird values after sorting with the temporary index.

This way, I can perform a second check to be certain that there are no Weird values, orthewise I will get a message displayed on that column. The problem is that it only can print integers, so I must do something like 1 for true and 0 for false. Or just 11111111111 for true and 0 for false, that way I can idenfity faster that there is a weird value.

[MagicPlot]

Yes, text operations are not supported. Moreover, the column can be either numeric or textual as a whole (in this case the background is changed).

The idea with '1111111' seems OK. I can only add that you can return NaN (it's a constant) in formulas to set an empty cell.