Page 2 of 2 FirstFirst 12
Results 11 to 13 of 13

Thread: Filtering Missing Data
Thanks Thanks: 0 Likes Likes: 0

  1. #11
    Board Regular
    Join Date
    Jul 2014
    Location
    Memphis, TN
    Posts
    2,674
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Filtering Missing Data

    Quote Originally Posted by Glasgowsmile View Post
    ...I've formatted it all the text but that didn't change the outcome.
    Formatting does not change the values from numbers to text.
    You need to use Data\Text to Columns...
    Excel 2013, 2016 with PowerBI
    Knowing that it can be done is half the battle!

  2. #12
    Board Regular
    Join Date
    Apr 2018
    Posts
    109
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Filtering Missing Data

    Quote Originally Posted by SpillerBD View Post
    Formatting does not change the values from numbers to text.
    You need to use Data\Text to Columns...
    What do you mean by this Data\Text to Columns?

    Since I have formulas would I need to push the data to another column and sort that column instead?

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

    Default Re: Filtering Missing Data

    Quote Originally Posted by Yongle View Post
    Ah - there are formulas creating the values in the column that you are sorting on ..

    I am unable to recreate your issue, as you can see below

    BEFORE
    Excel 2016 (Windows) 32 bit
    [COLOR=#FFFFFF ]C[/COLOR]
    [COLOR=#FFFFFF ]D[/COLOR]
    [COLOR=#FFFFFF ]E[/COLOR]
    [COLOR=#FFFFFF ]20[/COLOR]
    Value
    Code
    Formula
    [COLOR=#FFFFFF ]21[/COLOR]
    1
    05 =IFERROR(INDEX('ActvRateCat SumRpt'!C:C,MATCH(C21,'ActvRateCat SumRpt'!B:B,0)),"")
    [COLOR=#FFFFFF ]22[/COLOR]
    4
    AA =IFERROR(INDEX('ActvRateCat SumRpt'!C:C,MATCH(C22,'ActvRateCat SumRpt'!B:B,0)),"")
    [COLOR=#FFFFFF ]23[/COLOR]
    5
    DD =IFERROR(INDEX('ActvRateCat SumRpt'!C:C,MATCH(C23,'ActvRateCat SumRpt'!B:B,0)),"")
    [COLOR=#FFFFFF ]24[/COLOR]
    2
    26 =IFERROR(INDEX('ActvRateCat SumRpt'!C:C,MATCH(C24,'ActvRateCat SumRpt'!B:B,0)),"")
    [COLOR=#FFFFFF ]25[/COLOR]
    3
    09 =IFERROR(INDEX('ActvRateCat SumRpt'!C:C,MATCH(C25,'ActvRateCat SumRpt'!B:B,0)),"")
    Sheet: Sheet1

    AFTER sort A to Z

    Excel 2016 (Windows) 32 bit
    [COLOR=#FFFFFF ]C[/COLOR]
    [COLOR=#FFFFFF ]D[/COLOR]
    [COLOR=#FFFFFF ]E[/COLOR]
    [COLOR=#FFFFFF ]20[/COLOR]
    Value
    Code
    Formula
    [COLOR=#FFFFFF ]21[/COLOR]
    1
    05 =IFERROR(INDEX('ActvRateCat SumRpt'!C:C,MATCH(C21,'ActvRateCat SumRpt'!B:B,0)),"")
    [COLOR=#FFFFFF ]22[/COLOR]
    3
    09 =IFERROR(INDEX('ActvRateCat SumRpt'!C:C,MATCH(C22,'ActvRateCat SumRpt'!B:B,0)),"")
    [COLOR=#FFFFFF ]23[/COLOR]
    2
    26 =IFERROR(INDEX('ActvRateCat SumRpt'!C:C,MATCH(C23,'ActvRateCat SumRpt'!B:B,0)),"")
    [COLOR=#FFFFFF ]24[/COLOR]
    4
    AA =IFERROR(INDEX('ActvRateCat SumRpt'!C:C,MATCH(C24,'ActvRateCat SumRpt'!B:B,0)),"")
    [COLOR=#FFFFFF ]25[/COLOR]
    5
    DD =IFERROR(INDEX('ActvRateCat SumRpt'!C:C,MATCH(C25,'ActvRateCat SumRpt'!B:B,0)),"")
    Sheet: Sheet1

    AFTER sort Z to A

    Excel 2016 (Windows) 32 bit
    [COLOR=#FFFFFF ]C[/COLOR]
    [COLOR=#FFFFFF ]D[/COLOR]
    [COLOR=#FFFFFF ]E[/COLOR]
    [COLOR=#FFFFFF ]20[/COLOR]
    Value
    Code
    Formula
    [COLOR=#FFFFFF ]21[/COLOR]
    5
    DD =IFERROR(INDEX('ActvRateCat SumRpt'!C:C,MATCH(C21,'ActvRateCat SumRpt'!B:B,0)),"")
    [COLOR=#FFFFFF ]22[/COLOR]
    4
    AA =IFERROR(INDEX('ActvRateCat SumRpt'!C:C,MATCH(C22,'ActvRateCat SumRpt'!B:B,0)),"")
    [COLOR=#FFFFFF ]23[/COLOR]
    2
    26 =IFERROR(INDEX('ActvRateCat SumRpt'!C:C,MATCH(C23,'ActvRateCat SumRpt'!B:B,0)),"")
    [COLOR=#FFFFFF ]24[/COLOR]
    3
    09 =IFERROR(INDEX('ActvRateCat SumRpt'!C:C,MATCH(C24,'ActvRateCat SumRpt'!B:B,0)),"")
    [COLOR=#FFFFFF ]25[/COLOR]
    1
    05 =IFERROR(INDEX('ActvRateCat SumRpt'!C:C,MATCH(C25,'ActvRateCat SumRpt'!B:B,0)),"")
    Sheet: Sheet1


    (a guess ) Would adding another column and sorting on that work (see F below)

    Excel 2016 (Windows) 32 bit
    [COLOR=#FFFFFF ]C[/COLOR]
    [COLOR=#FFFFFF ]D[/COLOR]
    [COLOR=#FFFFFF ]E[/COLOR]
    [COLOR=#FFFFFF ]F[/COLOR]
    [COLOR=#FFFFFF ]G[/COLOR]
    [COLOR=#FFFFFF ]20[/COLOR]
    Value
    Code
    Formula
    Try ??
    Formula
    in F
    [COLOR=#FFFFFF ]21[/COLOR]
    5
    DD =IFERROR(INDEX('ActvRateCat SumRpt'!C:C,MATCH(C21,'ActvRateCat SumRpt'!B:B,0)),"") [COLOR=#FF0000 ]DD[/COLOR] =D21
    [COLOR=#FFFFFF ]22[/COLOR]
    4
    AA =IFERROR(INDEX('ActvRateCat SumRpt'!C:C,MATCH(C22,'ActvRateCat SumRpt'!B:B,0)),"") AA =D22
    [COLOR=#FFFFFF ]23[/COLOR]
    2
    26 =IFERROR(INDEX('ActvRateCat SumRpt'!C:C,MATCH(C23,'ActvRateCat SumRpt'!B:B,0)),"") 26 =D23
    [COLOR=#FFFFFF ]24[/COLOR]
    3
    09 =IFERROR(INDEX('ActvRateCat SumRpt'!C:C,MATCH(C24,'ActvRateCat SumRpt'!B:B,0)),"") 09 =D24
    [COLOR=#FFFFFF ]25[/COLOR]
    1
    05 =IFERROR(INDEX('ActvRateCat SumRpt'!C:C,MATCH(C25,'ActvRateCat SumRpt'!B:B,0)),"") 05 =D25
    Sheet: Sheet1
    I moved the Formulas to another sheet and I now reference that with =A5 like you did above but the sorting still doesn't work.

    When I sort A to Z it sorts all the NUMBERS like 04, 24, 25 but leaves out all the other information like GA, BY, CA, DI

    Same exact type of thing happens in reverse, it leaves out all the NUMBERS but pulls in the rest.

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
  •