Hey guys, (Barry?)
Just trying to sort without a certain column being affected.
Need the info in the last column to NEVER change it's position, while sorting by another column.
Example: Ranking stays 1-10 in the far right column. In order...ALWAYS. Lol.
Thought there was some simple way to set a formula, but if that was true I forgot.
Please set me straight.
Thanks
B
MacBook Pro 13″, macOS 10.12
Numbers tables behave like a database with regard to sorting.
Each row is a Record. Each column is a data field in that record.
Sorting the table sorts the rows and keeps the records intact.
To keep columnn K in the same order you have (at least) two options:
1: Separate column K from the rest of the table. Sorting the now 10 column table will sort columns A to J, but will have no effect on column K, which will now be Column A of a different table.
OR:
If:
The data in K is exactly what you want to stay in those cells—a sequence of integer values starting with 1 in K3 and ending with 10 in K12
AND
Rows 1 and 2 of the table are Header rows (which are NOT included in sorts and will remain as rows 1 and 2 when the table is sorted).
You can enter this formula in K3 and fill it down to the rest of the cells in column K:
K3: IF(ROW()>12,"",ROW()-2)
My example table shows the initial order of the rows in column A, a set of random numbers on which the table may be sorted, and the results of the formula, entered in all non-header rows of column K, shown below the table.
Sorting trhe table on the contents of column E (or any other column in your case) relocates rows 3 to 15 according to the values in column E. (These may not appear to be in order in the example as the RANDBETWEEN function recalculates after the move.)
If you have a need to return the table to its original order, I would suggest adding a row to the column containing numbers in that original order and placed as fixed values, not ones dependent on calculations after each change.
Regards,
Barry
Back in order after sorting on the fixed values in column A
Numbers tables behave like a database with regard to sorting.
Each row is a Record. Each column is a data field in that record.
Sorting the table sorts the rows and keeps the records intact.
To keep columnn K in the same order you have (at least) two options:
1: Separate column K from the rest of the table. Sorting the now 10 column table will sort columns A to J, but will have no effect on column K, which will now be Column A of a different table.
OR:
If:
The data in K is exactly what you want to stay in those cells—a sequence of integer values starting with 1 in K3 and ending with 10 in K12
AND
Rows 1 and 2 of the table are Header rows (which are NOT included in sorts and will remain as rows 1 and 2 when the table is sorted).
You can enter this formula in K3 and fill it down to the rest of the cells in column K:
K3: IF(ROW()>12,"",ROW()-2)
My example table shows the initial order of the rows in column A, a set of random numbers on which the table may be sorted, and the results of the formula, entered in all non-header rows of column K, shown below the table.
Sorting trhe table on the contents of column E (or any other column in your case) relocates rows 3 to 15 according to the values in column E. (These may not appear to be in order in the example as the RANDBETWEEN function recalculates after the move.)
If you have a need to return the table to its original order, I would suggest adding a row to the column containing numbers in that original order and placed as fixed values, not ones dependent on calculations after each change.
Regards,
Barry
Back in order after sorting on the fixed values in column A
Perfect answer. Yes, I already had another column set with "unmoving" numbers. I didn't want to send the whole thing and complicate anything. Fairly certain I probably asked you about this several years ago. Must have been why I remembered something about a formula that would hold those numbers in place.
Thanks again Barry!
B
You're welcome (again) KingB,
Thanks for the green checkmark!
Regards,
Barry
Sorting only part of a table
