Beaker192
Level 1 (5 points)
iPhone

Q: Sort Column Automatically

Hi

 

I have a column with a number of figures against names that changes depending on the input on a separate table. Is there any way to have this column sort descending automatically without me having to click it each time the figures change?

 

Thanks

 

Rob

iPad Pro, null

Posted on Sep 5, 2016 4:17 PM

by t quinn,Solvedanswer
t quinn t quinn
Level 5 (4,930 points)
Mac OS X
A:

Hi Rob,

 

The way I would approach this is to create an index column in your original table that would rank the values you want to sort.

Screen Shot 2016-09-04 at 8.28.46 AM.png

C2= RANK(B2,B,0)

this is filled down.

 

This index is used to pull the values into a report table that is already sorted.

Screen Shot 2016-09-04 at 8.30.57 AM.png

A2= INDEX(data entry::A,MATCH(ROW(cell)−1,data entry::$C,0),column-index,area-index)

This is filled down and across.

 

quinn

Posted on Sep 4, 2016 7:33 AM

Close
Beaker192

Q: Sort Column Automatically

  • All replies
  • Helpful answers

  • by t quinn,Solvedanswer

    t quinn t quinn Sep 4, 2016 7:33 AM in response to Beaker192
    Level 5 (4,930 points)
    Mac OS X
    Sep 4, 2016 7:33 AM in response to Beaker192

    Hi Rob,

     

    The way I would approach this is to create an index column in your original table that would rank the values you want to sort.

    Screen Shot 2016-09-04 at 8.28.46 AM.png

    C2= RANK(B2,B,0)

    this is filled down.

     

    This index is used to pull the values into a report table that is already sorted.

    Screen Shot 2016-09-04 at 8.30.57 AM.png

    A2= INDEX(data entry::A,MATCH(ROW(cell)−1,data entry::$C,0),column-index,area-index)

    This is filled down and across.

     

    quinn

  • by Beaker192,

    Beaker192 Beaker192 Sep 5, 2016 2:44 PM in response to t quinn
    Level 1 (5 points)
    iPhone
    Sep 5, 2016 2:44 PM in response to t quinn

    Cheers pal

  • by t quinn,

    t quinn t quinn Sep 6, 2016 7:37 AM in response to Beaker192
    Level 5 (4,930 points)
    Mac OS X
    Sep 6, 2016 7:37 AM in response to Beaker192

    Hi Rob,

     

    Thanks for the check! and did I mention that the index column can be hidden?

     

    quinn

  • by Beaker192,

    Beaker192 Beaker192 Sep 6, 2016 2:50 PM in response to t quinn
    Level 1 (5 points)
    iPhone
    Sep 6, 2016 2:50 PM in response to t quinn

    You didn't bud but I worked that out. I've been struggling with it a bit, because even though it'll rank one of the columns, it doesn't sort all of the column as though i'd clicked sort descended. I'm not sure if I essentially need to make a whole new table so it'll sort the whole thing.

  • by t quinn,

    t quinn t quinn Sep 6, 2016 3:04 PM in response to Beaker192
    Level 5 (4,930 points)
    Mac OS X
    Sep 6, 2016 3:04 PM in response to Beaker192

    Hi Rob,

     

    I am trying to understand what is not working with your table. Is the index function filled all the way down the table? Can you post a screenshot with the function and the problomatic cells visible?

     

    quinn

  • by Beaker192,

    Beaker192 Beaker192 Sep 6, 2016 3:45 PM in response to t quinn
    Level 1 (5 points)
    iPhone
    Sep 6, 2016 3:45 PM in response to t quinn

    Screen Shot 2016-09-06 at 23.43.49.png

    Basically i'm trying to have the whole table sort as though I had clicked "sort descending". But I could only get the column to list either names, or values. I'm unsure how to then add the corresponding values.

  • by t quinn,

    t quinn t quinn Sep 6, 2016 4:13 PM in response to Beaker192
    Level 5 (4,930 points)
    Mac OS X
    Sep 6, 2016 4:13 PM in response to Beaker192

    Hi Rob,

     

    I just expanded out my solution above. index in data entry works as before.

    Screen Shot 2016-09-06 at 5.03.41 PM.png

    I copy/pasted the formula from A2 into B2 (Numbers won't let me fill from a header column) then I dragged it down and across.

    When you exaimine the formula you will notice that the reference to the index column is fixed ($F) while the reference to column be is not. As I filled across this reference changed to reference the other columns.

    I think it would be good practice to have the formula in the header row also. This way if you add or move columns the headers will agree with the data.

     

    quinn