Filter not working (monetary values)

Occasional Contributor

Hi, y'all! Tis weird, but the most basic function of Excel (filtering values) is not working as it should.
I'm building a table with a colum for costs, and the other columns for descriptions, locations, hyperlinks, etc.
I just want to order my table by costs, smallest to biggest, and the filter should do this. However, it isn't working, it leaves everything in roller-coster/zigzag mode, alternating cheap and expensive.
All my costs values are in monetary values. Tries removing that and jsut use the number, same old S.
Wondering what the f I must be doing wrong. Any ideas?
Cheers,
D

8 Replies

@Damian_Dell 

 

Well, one thought for starters: Filtering and Sorting are two different things. So I'm struck by your use of the word "filtering" when what you're describing as desired is clearly sorting. Are you, in other words, inadvertently using the wrong button or menu command?

 

If some of the values are text masquerading as values, that can cause difficulties. You seem to address that, and to know the difference. But depending on how the entries in your Cost column are sourced, is it possible that they're not all consistent?

 

Final question/suggestion: if the workbook in question doesn't contain confidential information, would it be possible for you to post a copy of it on OneDrive or GoogleDrive, and then paste a link here that grants edit access to it. That way we could have a look at the actual workbook.

Hi, Mathetes, and thanks a lot for your interest!
Let's see if my link would work here:

https://greenworking-my.sharepoint.com/:x:/r/personal/damian_dellamico_greenworking_fr/Documents/Doc...

Maybe it won't work. I'm based in France, Europe, and my table is in French (including my costs in Euros).

Yes, I'm aware of the nuance between sorting and filtering. While indeed I just want to sort it through my Costs column only (column A, aka "PRIX TTC", in Euros), my managers may want to sort the data by name of the venues (column C) or their location, and that's why I thought of simply adding a filter and let each one sort it out (or filter it out) as they like best. Perhaps this may result in confusion to you?

I also tried with both ways (only sorting, and only filtering) alternatively. All my values are monetary values, there are no hidden apostrophes, text, or whatever. It must be something very stupid, but so far I'm failing (miserably) to see it. Hence the idea that someone else might be able to see at once what I've been failing to see since a couple of hours ago.

@Damian_Dell 

 

I'm not authorized to use that sharepoint resource. (I suspect it's specific to your company.)

 

Is it possible to put a copy, so long as there's no confidential data in it, in a more "public" space like GoogleDrive or OneDrive?

You also need to grant access. I've sent you a request for that
Many thanks again for your interest, Mathetes!
I've just modified the permission so that anybody with the link can access the copy and play.
Hopefully this time will work?
Now it's 18:03 over here, and it would seem I'm like 9 hours ahead of you. As I'm shutting down to go home, I thank you again and wish you a nice day. I'll see your answer, if any, tomorrow.
Keep well!

@Damian_Dell 

It works if in PRIX TTC column you replace texts like "2 500,00 €" on numbers (2500 in this case) and apply to them currency format.

@Damian_Dell 

 

The "values" in your PRIX TTC column are a mix of numeric and other. Several rows at the bottom of the table are text rather than numbers. That is evident if you try to sort by that column and what appears is this

mathetes_0-1672860308263.png

When the "Order" appears as "A to Z" it's telling you that what's in the column is text rather than numbers.

 

I added a column with the english title "Price" and converted the several bottom rows to numbers, and now the Sort window shows this

mathetes_1-1672860568198.png

And sorts (I believe) as desired.

 

Attached below is the sheet is with that change.