Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: Sorting data retrieved by formulas?

  1. #1
    Board Regular
    Join Date
    Jun 2018
    Posts
    111
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Sorting data retrieved by formulas?

    Hi,

    I have a table with data generated by formulas. When using the sort function in the filter I get lots of blanks between all values. I can not uncheck blanks in the filter since it will remove other information on the same row that is not related to the table data.

    Any tips?
    Last edited by Cameltoe; Apr 15th, 2019 at 05:38 AM.

  2. #2
    Board Regular Scott T's Avatar
    Join Date
    Dec 2016
    Posts
    2,450
    Post Thanks / Like
    Mentioned
    15 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Sorting data retrieved by formulas?

    What is your formula? You could change it to return 0 or not found.
    Colonel Sandurz: Prepare ship for light speed.
    Dark Helmet: No, no, no, light speed is too slow.
    Colonel Sandurz: Light speed, too slow?
    Dark Helmet: Yes, we're gonna have to go right to ludicrous speed.

  3. #3
    Board Regular
    Join Date
    Jun 2018
    Posts
    111
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sorting data retrieved by formulas?

    Quote Originally Posted by Scott T View Post
    What is your formula? You could change it to return 0 or not found.
    =IF(INDEX($C$3:$C$300;MATCH(D3;$B$3:$B$300;0))="NO";D3;"") this formula retrieves the values in column E. Those values, I want to sort in column F. Please see example below. I can do that using the filter but the issue is there is information on the same row as the blanks moving around then.

    Column E
    (blank)
    (blank)
    89043
    (blank)
    75678
    (blank)

    Column F
    89043
    75678
    Last edited by Cameltoe; Apr 18th, 2019 at 03:47 AM.

  4. #4
    Board Regular James006's Avatar
    Join Date
    Apr 2009
    Posts
    3,210
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Sorting data retrieved by formulas?

    Hello,

    Not sure to understand your example ..

    Are you looking for a formula to sort Column E ..

    or

    Are you looking for a formula to Delete all the Blanks ...???

  5. #5
    Board Regular
    Join Date
    Jun 2018
    Posts
    111
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sorting data retrieved by formulas?

    Quote Originally Posted by James006 View Post
    Hello,

    Not sure to understand your example ..

    Are you looking for a formula to sort Column E ..

    or

    Are you looking for a formula to Delete all the Blanks ...???
    Hi James,

    A formula to sort the cells containing values and putting the blanks in the bottom. Without messing around information that might appear on the same row as the blank row.

  6. #6
    Board Regular James006's Avatar
    Join Date
    Apr 2009
    Posts
    3,210
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Sorting data retrieved by formulas?

    well sorting data ...

    will not produce :

    Column F
    89043
    75678

    unless you need to produce a descending order...

  7. #7
    Board Regular
    Join Date
    Jun 2018
    Posts
    111
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sorting data retrieved by formulas?

    Order does not matter. So does it work?

  8. #8
    Board Regular James006's Avatar
    Join Date
    Apr 2009
    Posts
    3,210
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Sorting data retrieved by formulas?

    Say you create a Named Range for the data your Column E ( e.g. rng )

    in cell F2, you can have the following Array formula:

    Code:
    =IF(ROWS($2:2)<=COUNTA(rng),INDEX(rng,SMALL(IF(rng<>"",ROW(rng)-MIN(ROW(rng))+1),ROWS($2:2))),"")
    and copy down ...

    Hope this will help

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •