Filtering Missing Data

Glasgowsmile

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

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. 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
152
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
6,977
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
6,977
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
152
@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
6,977
Office Version
  1. 365
Platform
  1. 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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,738
Messages
5,597,829
Members
414,180
Latest member
Sir Khaya

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top