Pages - Sorting data type by size in rows

a_zakhary wrote:

how can one create a sorting rule and introduce bytes to data sets in Pages?

One way is to do something like this:

The formula in F2 of the new Bytes column, filled down the column:

=LEFT(E2,FIND(" ",E2,1)−1)*1024^FIND(MID(E2,LEN(E2)−1,1),"kMG",1)

Then sort the table by the Bytes column.

Substitute ; for , in the formula if your region uses , as a decimal separator.

NB. If you don't want to show the Bytes column in your Pages document (unfortunately Pages does not appear to allow a column to be "hidden") then you could set up your table in Numbers, do the sorting there, and copy-paste the relevant columns into your Pages table.

SG

I would normalize the table content into one SI unit, and use that SI unit abbreviation in the column heading as self documentation of the column (numeric-only) content. Then it becomes a simple, Pages supported numeric sort.

Neither Pages itself, nor its AppleScript dictionary support provide sorting finesse that would cope with your current table content.

Hi a_zakhary,

I wrote: "Where I got stuck was reducing the number of digits in the results with the very different magnitudes."

The ROUND function is our friend.

These are actual values in the "kilobytes" table. No more RANDBETWEEN !

Tested in Numbers. Formula in the Display table, cell C2 (and fill down and fill right to column F)

=IF(kilobytes::A2<1024,ROUND(kilobytes::A2,2)&" KB",IF(kilobytes::A2<1048576,ROUND(kilobytes::A2÷1024,2)&" MB",ROUND(kilobytes::A2÷1048576,2)&" GB"))

I threw in a few "standard" kilobyte values (1024 and 1048576) just as a test for the transition from KB to MB to GB.

The formulas will work in Pages, but it will be necessary to hide the "kilobytes" table somehow.

In Numbers, that table can be moved to another sheet (and not printed).

Regards,

Ian.

As a footnote, I wrote a Ruby script that takes your column five data, and sorts it as::

I wrote and tested this against a special installation of Ruby 2.6.2 which works fine. However, Apple's System Ruby, being the ancient, now retired Ruby v2.3.7, failed on one crucial line of code, which at this point, I am not sure I can code around. Apple's System Python is five years out of date, and may/may not be an alternative solution. AppleScript lacks advanced data structure tools and would be a horror story to use as a solution.

 I can't complicate myself with functions.

Well, am sorry to "complicate" your life with functions and formulas. Too bad you haven't found anything helpful in this thread.

Copying and pasting a formula into a cell in a Pages or Numbers table is a tough thing, I know.

Of course, if you don't like functions, you just may find Excel isn't exactly the right place for you either!😀

Good luck with your project.

SG

Late edit,

Sorry, I was using RANDBETWEEN() to create values. They changed with every key stroke. Please disregard my previous screenshots.

Hi a_zakhary

Pages and Numbers sort according to the data type. For example, 12.93 GB Is text, and is sorted as text, not as a number value.

Here is a half-baked idea created in Numbers for Mac. It may start you on your journey, or it may encourage other users to improve on this. Where I got stuck was reducing the number of digits in the results with the very different magnitudes.

Based upon:

1 megabyte = 1,024 kilobytes.

1 gigabyte = 1,024 megabytes (1,024 kilobytes times 1,024 kilobytes = 1,048,576 kilobytes).

The advantage of Numbers is that we can add "helper" tables and then hide them by moving them (Cut and Paste) to another Sheet (which we don't print).

Enter your raw data as kilobytes in the "kilobytes" table.

In the Display table, formula in C2 (and fill down to the bottom of the table, then fill right to column F)

=IF(kilobytes::A2<1024,kilobytes::A2&" KB",IF(kilobytes::A2<1048576,kilobytes::A2÷1024&" MB",kilobytes::A2÷1048576&" GB"))

Now Sort Descending on any column in the "kilobytes" table. Here we go with column A:

In other words, do the sorting in the "kilobytes" table (proper sorting of number values) and the "Display" table (text values) will follow.

Regards,

Ian