Options in filter aren't alphabetical

Hello everyone,

I can't figure out why my filter isn't alphabetical, see image for reference.

Normaly via Google I can find my awnsers, but it's realy hard to search on 'filter alphabetical' and such.

This filter is part of a Pivot table. Please let me know if any more information is needed.

2024-02-05 08_49_26-.png

Welcome to the forum.

There are instructions at the top of the page explaining how to attach your sample workbook (yellow banner: HOW TO ATTACH YOUR SAMPLE WORKBOOK). Screenshots are of little practical use as we cannot manipulate them.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.

Without a sample file to look at (instead of a picture), it's hard to say. I've tried including leading spaces and non-printing characters, but Excel still sorts them numerically then alphabetically...

Some of your "numeric" values are left aligned which indicates they are text values that look like numbers rather than true numeric values.

Try using Text to Columns to convert the values.

I see it. But... so far I cannot find the problem. Yes, the column A values are in a special format... but even if I convert them to ordinary numbers, the same happens. When I add my own data (Sheet 1) it's fine.

TMS, that doesn't work. It's very odd...

It's not fine with your data when I look at it, Glenn - it's still not in ascending order.

In Sheet 3, I copy/pasted the VALUES from the original data and formatted column A as 0000. I then set uo the Pivot table, formattting IT as 0000. It's fine now.

No idea what was going on in your original data, though.

I attached the wrong file at Post 7. Refer to Post 11

Typo. uo = up

Thanks for all the help so far Glenn, I see; I've recreated your steps and I also could make it work for the example file.

However, my real datasheet contains Colum A to NQ with around 8000 rows, the Pivot table is gigantic and realy specific. To remake this Pivot table would cost me half a day. So I actually need a different fix then remaking the pivot table.

?? What's taking the time?

The datasheet is in Dutch, and the pivottable needs to be in English. So every colum is translated via the Pivottable. Since the size of the sheet it takes a lot of time to find the correct colum for the Pivot table with the correct translation. Then after that, every hit in the Pivot table need specific settings on the values tab.

Why don't you switch your locale to an English one?

We have a tool running which creates a weekly .csv file on the Sharepoint, this is a export of the logistic data. My datasheet has a query reading this file. Unfortunally the export file has Dutch headers, even if I translate them, after the weekly update it's back in the original language.

This sounds like a bizarre set-up to me.

What is bizar about the setup? The logistic software doesn't provide the analitic tools with the needed data, so there is a export with the data to work with it in Excel. I think it's the most normal thing to do for a bussiness.

Furthermore: bizar or not, I still have the problem with the filter. I can't be the first person on the world with this issue, the sample sheet I've made in like 2 minutes and the problem already shows; there must be a logical explanation.

I meant the different language settings.

I am sure you are not the first in the world to have had this issue, but it would appear to be related to those different language settings. I don't think many other people here, if any, will have a dual-lingual set-up like that, but let's wait and see.

What none of us is going to be able to determine for you is WHY exactly this is happening. I don't think there'll be any automatic solution, but there are some clever people here, so again, let's wait and see. It might require VBA.

Administrative Note re. Forum Guideline #2:

Please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter and rarely necessary.

If you are responding out of sequence, it is usually enough just to mention the helper's user name (e.g @AliGW).

If you do need to quote, limit the quoted section just to the section to which you wish to draw your helper's attention or a direct question to which you wish to respond.

For normal conversational replies, try using the QUICK REPLY box below or the REPLY button instead of REPLY WITH QUOTE.

My misunderstanding of this, then:

The datasheet is in Dutch, and the pivottable needs to be in English. So every colum is translated via the Pivottable.

Sorry.

Great news! Thanks for letting us know.

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

You're welcome. Thanks for letting us know that you got an answer.

---

Please take a moment and consider clicking the "Add Reputation" button at the foot of any of the posts of anyone who helped you reach a solution here today.

Finally, if that takes care of your original question, please click on "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.