Sorting rows in sheet based on two values input to cells as string variables (or original arrays)

Question

I think I am asking the impossible but I am a pessimist, so here it goes. I have a sheet with the following data set:

I have built this from two 2D Arrays, one contains the numbers outside the brackets, one contains the numbers inside the brackets. These are calculated in the background ad printed back out here in this format, which is important.

Now I want to sort the WHOLE ROW (there are values either side on this data set), in the following way: Sort 1: coln 1-1 values outside brackets, in descending order THEN coln 1-1 values inside brackets, in ascending order THEN Sort 2: coln 1-2 values outside brackets, in descending order THEN coln 1-2 values inside brackets, in ascending order THEN and so on to coln 1-11

What you can see in the image is the result of a manual custom sort, just based on cell value, which you can see doesn't quite work, and also doesn't have the subtlety of the two numbers.

I thought about sorting the arrays before printout but then the association of the numbers with each other and with the data either side would be lost.

I then considered combining these two 2d arrays, which probably means 3D??? and adding a column to apply the row reference. Then I could, somehow, use the row reference as the sort key??? I'm not really sure that makes sense or how to go about it.

Any bright ideas?

The best way to do it is to create helper columns to hold only the values that you want to sort by. — user6432984
– user6432984, Commented Jul 29, 2017 at 17:10

trincot · Accepted Answer · 2017-07-29 17:18:21Z

Don't store the two values together in one cell, as that makes it a text value. Instead double the number of columns and use each pair of columns to store the two values. Then apply the sorting on that as you would normally do.

If you still need the "9 (9)" format, then create a new sheet where you use a formula to read out the values from the first sheet. Make sure to use a formula that is non-sensitive to the swapping of rows that will occur when you sort the first sheet. You can use a function that will look somewhat like this (details will differ depending on where exactly you have the data in the first and second sheet):

=Sheet1!INDEX($A:$ZZ, ROW(), COLUMN()*2-1) & " (" &
     Sheet1!INDEX($A:$ZZ, ROW(), COLUMN()*2) & ")"

Collectives™ on Stack Overflow

Sorting rows in sheet based on two values input to cells as string variables (or original arrays)

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related