Filtering Missing Data

G

Glasgowsmile

Active Member
Joined
Apr 14, 2018
Messages
280
Office Version
  1. 365
Platform
  1. Windows
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?
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
Sort by date Sort by votes
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
 
Upvote 0
Yongle said:
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
Click to expand...

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.
 
Upvote 0
Just following up - any idea what may be happening with the Sorting of this?
 
Upvote 0
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
 
Upvote 0
Yongle said:
@SpillerBD
I am intrigued :confused:
How does 64bit solve this issue ?
Click to expand...

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:
Upvote 0
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
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]
C​
[/th][th]
D​
[/th][th]
E​
[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
20​
[/td][td]Value
[/td][td]Code
[/td][td] Formula
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
21​
[/td][td]
1​
[/td][td]05[/td][td] =IFERROR(INDEX('ActvRateCat SumRpt'!C:C,MATCH(C21,'ActvRateCat SumRpt'!B:B,0)),"")[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
22​
[/td][td]
4​
[/td][td]AA[/td][td] =IFERROR(INDEX('ActvRateCat SumRpt'!C:C,MATCH(C22,'ActvRateCat SumRpt'!B:B,0)),"")[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
23​
[/td][td]
5​
[/td][td]DD[/td][td] =IFERROR(INDEX('ActvRateCat SumRpt'!C:C,MATCH(C23,'ActvRateCat SumRpt'!B:B,0)),"")[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
24​
[/td][td]
2​
[/td][td]26[/td][td] =IFERROR(INDEX('ActvRateCat SumRpt'!C:C,MATCH(C24,'ActvRateCat SumRpt'!B:B,0)),"")[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
25​
[/td][td]
3​
[/td][td]09[/td][td] =IFERROR(INDEX('ActvRateCat SumRpt'!C:C,MATCH(C25,'ActvRateCat SumRpt'!B:B,0)),"")[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]

AFTER sort A to Z

Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]
C​
[/th][th]
D​
[/th][th]
E​
[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
20​
[/td][td]Value
[/td][td]Code
[/td][td] Formula
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
21​
[/td][td]
1​
[/td][td]05[/td][td] =IFERROR(INDEX('ActvRateCat SumRpt'!C:C,MATCH(C21,'ActvRateCat SumRpt'!B:B,0)),"")[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
22​
[/td][td]
3​
[/td][td]09[/td][td] =IFERROR(INDEX('ActvRateCat SumRpt'!C:C,MATCH(C22,'ActvRateCat SumRpt'!B:B,0)),"")[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
23​
[/td][td]
2​
[/td][td]26[/td][td] =IFERROR(INDEX('ActvRateCat SumRpt'!C:C,MATCH(C23,'ActvRateCat SumRpt'!B:B,0)),"")[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
24​
[/td][td]
4​
[/td][td]AA[/td][td] =IFERROR(INDEX('ActvRateCat SumRpt'!C:C,MATCH(C24,'ActvRateCat SumRpt'!B:B,0)),"")[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
25​
[/td][td]
5​
[/td][td]DD[/td][td] =IFERROR(INDEX('ActvRateCat SumRpt'!C:C,MATCH(C25,'ActvRateCat SumRpt'!B:B,0)),"")[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]

AFTER sort Z to A

Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]
C​
[/th][th]
D​
[/th][th]
E​
[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
20​
[/td][td]Value
[/td][td]Code
[/td][td] Formula
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
21​
[/td][td]
5​
[/td][td]DD[/td][td] =IFERROR(INDEX('ActvRateCat SumRpt'!C:C,MATCH(C21,'ActvRateCat SumRpt'!B:B,0)),"")[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
22​
[/td][td]
4​
[/td][td]AA[/td][td] =IFERROR(INDEX('ActvRateCat SumRpt'!C:C,MATCH(C22,'ActvRateCat SumRpt'!B:B,0)),"")[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
23​
[/td][td]
2​
[/td][td]26[/td][td] =IFERROR(INDEX('ActvRateCat SumRpt'!C:C,MATCH(C23,'ActvRateCat SumRpt'!B:B,0)),"")[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
24​
[/td][td]
3​
[/td][td]09[/td][td] =IFERROR(INDEX('ActvRateCat SumRpt'!C:C,MATCH(C24,'ActvRateCat SumRpt'!B:B,0)),"")[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
25​
[/td][td]
1​
[/td][td]05[/td][td] =IFERROR(INDEX('ActvRateCat SumRpt'!C:C,MATCH(C25,'ActvRateCat SumRpt'!B:B,0)),"")[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]


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

Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]
C​
[/th][th]
D​
[/th][th]
E​
[/th][th]
F​
[/th][th]
G​
[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
20​
[/td][td]Value
[/td][td]Code
[/td][td] Formula
[/td][td]Try ??
[/td][td] Formula
in F
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
21​
[/td][td]
5​
[/td][td]DD[/td][td] =IFERROR(INDEX('ActvRateCat SumRpt'!C:C,MATCH(C21,'ActvRateCat SumRpt'!B:B,0)),"")[/td][td]DD[/td][td=bgcolor:#FF0000] =D21[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
22​
[/td][td]
4​
[/td][td]AA[/td][td] =IFERROR(INDEX('ActvRateCat SumRpt'!C:C,MATCH(C22,'ActvRateCat SumRpt'!B:B,0)),"")[/td][td]AA[/td][td] =D22[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
23​
[/td][td]
2​
[/td][td]26[/td][td] =IFERROR(INDEX('ActvRateCat SumRpt'!C:C,MATCH(C23,'ActvRateCat SumRpt'!B:B,0)),"")[/td][td]26[/td][td] =D23[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
24​
[/td][td]
3​
[/td][td]09[/td][td] =IFERROR(INDEX('ActvRateCat SumRpt'!C:C,MATCH(C24,'ActvRateCat SumRpt'!B:B,0)),"")[/td][td]09[/td][td] =D24[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
25​
[/td][td]
1​
[/td][td]05[/td][td] =IFERROR(INDEX('ActvRateCat SumRpt'!C:C,MATCH(C25,'ActvRateCat SumRpt'!B:B,0)),"")[/td][td]05[/td][td] =D25[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question Question
Filter and Unique missing data
Replies
5
Views
98
etaf
etaf
D
  • Solved
Trying to copy data from a specific sheet in one workbook to another with VBA
Replies
2
Views
80
druck21
D
S
  • Question Question
VBA code to determine InDesign Layout
Replies
6
Views
442
Akuini
Akuini
J
  • Question Question
Group last name based on range in alphabet
Replies
2
Views
158
Peter_SSs
Peter_SSs
J
  • Solved
Unique, Filter and Sort Using a Range defined by two cells
Replies
8
Views
213
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,223,888
Messages
6,175,208
Members
452,618
Latest member
Tam84

Share this page

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
Back
Top