What I mean by this is maybe explained easiest with an example:

I habe a list of items and every item has a name and an ID. I want to sort that list by ID, with the exception to have item "XYZ" with ID 24 alwas on first place. So I would like to give it's ID-cell the value 0 (for sorting) but it should still show the value 24.

Sounds weird and is probably not possible?

Edit: I want it to look like this:

If the user is sorting (using the filter drop down menu) by ID column, it should be sorted as it's in this picture. I found a solution for the first ID (Hans): Custom number format for that cell:"0;0;"24". It will show the value 0 as '24'. That doesn't help for Franz tho. Here I want a value between 5 and 7 showed as '??'.

CodePudding user response：

A possible solution would be creating a second ID and sorting the values based on this new ID. Something like this:

CodePudding user response：

If you are sorting through formula in other columns you can use:

=SORT(A2:B,MAP(B2:B,LAMBDA(id,IF(id=24,0,id))),1)

CodePudding user response：

Exclude that first item from sorting then merge again. Try the following formula.

={A1:B2;SORT(A3:B,2,1)}

CodePudding user response：

1. Built an array youself for sorting with IF(A1:A7=24,0,A1:A7), so that XYZ will have its weight as 0,
2. place it next to your data as the 3rd column with {},
3. use QUERY() to sort the data and get rid of the 3rd column.

Formula:

=ArrayFormula(
 QUERY({A1:B7,IF(A1:A7=24,0,A1:A7)},
  "Select Col1,Col2 Order By Col3 ASC",
 1)
)

CodePudding user response：

I found the solution (with a little help): For only the cells you want to show "Something else" but be handled as the values, edit the Custom number format to:

By this, it will always show what you put in there, no matter what value the cell has.