Page 1 of 2 12 LastLast
Results 1 to 10 of 13

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

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

    Default Filtering Missing Data

    Hello,

    When I apply a filter to this document I'm using, a lot of the data suddenly goes missing.

    I've got a lot of two digit codes that I'm sorting by and when I sort A to Z it only brings in the codes that are numbers and remains blank for all the other two digit codes that have letters.

    I've tried filtering by selecting the range of the spreadsheet and also by just doing the headers and letting Excel guess the range but neither work. Selecting fields below works fine, it's just the A to Z and Z to A that breaks it. If I do Z to A then the numbered codes are missing entirely so I end up having to reset the formulas entirely to get it working again.

    Any suggestions?

  2. #2
    Board Regular
    Join Date
    Mar 2015
    Posts
    4,056
    Post Thanks / Like
    Mentioned
    73 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Filtering Missing Data

    Try formatting the whole column as TEXT and see if that solves your problem


    Otherwise post a sample of your data like this

    01
    99
    AB
    CX

    etc

    and underneath post what you want to see as filtered results

  3. #3
    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
    Try formatting the whole column as TEXT and see if that solves your problem


    Otherwise post a sample of your data like this

    01
    99
    AB
    CX

    etc

    and underneath post what you want to see as filtered results
    We'll take your example:

    If I sorted A to Z on that filter it would only show 01 and 99 -- AB and CX wouldn't be found.

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

    Default Re: Filtering Missing Data

    Just following up - any idea what may be happening with the Sorting of this?

  5. #5
    Board Regular
    Join Date
    Mar 2015
    Posts
    4,056
    Post Thanks / Like
    Mentioned
    73 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Filtering Missing Data

    1. If everything is formatted as text (as suggested in post#2) I would expect everything to be treated as text - it works for me
    - sorting works, filtering works, nothing "goes missing"

    2. Put this formula in adjacent column in row 2 and copy down (where X is the column that you want to sort by)
    =CELL("type",X2)
    The values returned should all be L - if any of the formulas return V then they are numbers - you do not want numbers


    If you still are unable to achieve what you want ...

    3. Which version of Excel are you using?

    4. Perhaps I have misunderstood what you are trying to do
    so explain ...
    - exactly what you are trying to achieve
    - exactly what you are doing, step by step
    - what is the input
    - what are the results
    - what do you want as the results
    - what are you entering inside the filter boxes etc

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

    Default Re: Filtering Missing Data

    Switch to the 64-bit version.
    Excel 2013, 2016 with PowerBI
    Knowing that it can be done is half the battle!

  7. #7
    Board Regular
    Join Date
    Mar 2015
    Posts
    4,056
    Post Thanks / Like
    Mentioned
    73 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Filtering Missing Data

    Switch to the 64-bit version
    @SpillerBD
    I am intrigued
    How does 64bit solve this issue ?

  8. #8
    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
    @SpillerBD
    I am intrigued
    How does 64bit solve this issue ?
    Didn't work for me. Same issue.

    Back to what you had asked though.

    As I had mentioned before I am trying to sort data, some having numbers and some with letters from A to Z or Z to A.

    When I sort A to Z only the numbers show up, not the two digit letters (but they still show up in the filter!) and when I do Z to A, only the two digit letters and not the numbers.

    I've formatted it all the text but that didn't change the outcome.

    Here is what shows up when I sort:

    https://imgur.com/ozbwqDN

    and here is what it looks like before I sort, so you can see the variety of options.

    https://imgur.com/63VxKj7

    and here is the formula in those cells -- so you can see it's pulling the data from another worksheet. I tried making that worksheet Text format too but it didn't help.

    Code:
    =IFERROR(INDEX('ActvRateCat SumRpt'!C:C,MATCH(C21,'ActvRateCat SumRpt'!B:B,0)),"")



    Last edited by Glasgowsmile; Sep 9th, 2019 at 05:22 PM.

  9. #9
    Board Regular
    Join Date
    Mar 2015
    Posts
    4,056
    Post Thanks / Like
    Mentioned
    73 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Filtering Missing Data

    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
    C
    D
    E
    20
    Value
    Code
    Formula
    21
    1
    05 =IFERROR(INDEX('ActvRateCat SumRpt'!C:C,MATCH(C21,'ActvRateCat SumRpt'!B:B,0)),"")
    22
    4
    AA =IFERROR(INDEX('ActvRateCat SumRpt'!C:C,MATCH(C22,'ActvRateCat SumRpt'!B:B,0)),"")
    23
    5
    DD =IFERROR(INDEX('ActvRateCat SumRpt'!C:C,MATCH(C23,'ActvRateCat SumRpt'!B:B,0)),"")
    24
    2
    26 =IFERROR(INDEX('ActvRateCat SumRpt'!C:C,MATCH(C24,'ActvRateCat SumRpt'!B:B,0)),"")
    25
    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
    C
    D
    E
    20
    Value
    Code
    Formula
    21
    1
    05 =IFERROR(INDEX('ActvRateCat SumRpt'!C:C,MATCH(C21,'ActvRateCat SumRpt'!B:B,0)),"")
    22
    3
    09 =IFERROR(INDEX('ActvRateCat SumRpt'!C:C,MATCH(C22,'ActvRateCat SumRpt'!B:B,0)),"")
    23
    2
    26 =IFERROR(INDEX('ActvRateCat SumRpt'!C:C,MATCH(C23,'ActvRateCat SumRpt'!B:B,0)),"")
    24
    4
    AA =IFERROR(INDEX('ActvRateCat SumRpt'!C:C,MATCH(C24,'ActvRateCat SumRpt'!B:B,0)),"")
    25
    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
    C
    D
    E
    20
    Value
    Code
    Formula
    21
    5
    DD =IFERROR(INDEX('ActvRateCat SumRpt'!C:C,MATCH(C21,'ActvRateCat SumRpt'!B:B,0)),"")
    22
    4
    AA =IFERROR(INDEX('ActvRateCat SumRpt'!C:C,MATCH(C22,'ActvRateCat SumRpt'!B:B,0)),"")
    23
    2
    26 =IFERROR(INDEX('ActvRateCat SumRpt'!C:C,MATCH(C23,'ActvRateCat SumRpt'!B:B,0)),"")
    24
    3
    09 =IFERROR(INDEX('ActvRateCat SumRpt'!C:C,MATCH(C24,'ActvRateCat SumRpt'!B:B,0)),"")
    25
    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
    C
    D
    E
    F
    G
    20
    Value
    Code
    Formula
    Try ??
    Formula
    in F
    21
    5
    DD =IFERROR(INDEX('ActvRateCat SumRpt'!C:C,MATCH(C21,'ActvRateCat SumRpt'!B:B,0)),"") DD =D21
    22
    4
    AA =IFERROR(INDEX('ActvRateCat SumRpt'!C:C,MATCH(C22,'ActvRateCat SumRpt'!B:B,0)),"") AA =D22
    23
    2
    26 =IFERROR(INDEX('ActvRateCat SumRpt'!C:C,MATCH(C23,'ActvRateCat SumRpt'!B:B,0)),"") 26 =D23
    24
    3
    09 =IFERROR(INDEX('ActvRateCat SumRpt'!C:C,MATCH(C24,'ActvRateCat SumRpt'!B:B,0)),"") 09 =D24
    25
    1
    05 =IFERROR(INDEX('ActvRateCat SumRpt'!C:C,MATCH(C25,'ActvRateCat SumRpt'!B:B,0)),"") 05 =D25
    Sheet: Sheet1

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

    Default Re: Filtering Missing Data

    Quote Originally Posted by Yongle View Post
    @SpillerBD
    I am intrigued
    How does 64bit solve this issue ?
    Sorting level limits, which was an assumption since we have no idea of how many combinations are in the original data set.
    Excel 2013, 2016 with PowerBI
    Knowing that it can be done is half the battle!

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
  •