Filtering Missing Data

Glasgowsmile

Board Regular
Joined
Apr 14, 2018
Messages
112
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?
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
4,973
Office Version
365
Platform
Windows
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
 

Glasgowsmile

Board Regular
Joined
Apr 14, 2018
Messages
112
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.
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
4,973
Office Version
365
Platform
Windows
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" :confused:

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 :confused:
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
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
4,973
Office Version
365
Platform
Windows
Switch to the 64-bit version
@SpillerBD
I am intrigued :confused:
How does 64bit solve this issue ?
 

Glasgowsmile

Board Regular
Joined
Apr 14, 2018
Messages
112
@SpillerBD
I am intrigued :confused:
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:

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
4,973
Office Version
365
Platform
Windows
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 :confused:) 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
 

Forum statistics

Threads
1,085,882
Messages
5,386,536
Members
402,004
Latest member
moneyman4438

Some videos you may like

This Week's Hot Topics

Top