Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 01-26-2022, 04:42 PM
PomDave PomDave is offline Sort Filter Problem Windows 10 Sort Filter Problem Office 2010
Advanced Beginner
Sort Filter Problem
 
Join Date: Sep 2011
Location: Australia
Posts: 43
PomDave is on a distinguished road
Default Sort Filter Problem

Hi All,

I'm trying to get a sheet to sort by country where there are more than one row for each country.



Now that parts not this issue, was is the problem is that when I go to sort the original, a to z Tanzania always starts the filtered list. If I then sort the list from z to a Spain now heads the list.

Anyone got any ideas as to what's going on.

Best Rgds,
Dave R.
Attached Files
File Type: xlsx Excel_Problem_1.xlsx (35.8 KB, 7 views)
Reply With Quote
  #2  
Old 01-26-2022, 06:34 PM
NoSparks NoSparks is offline Sort Filter Problem Windows 10 Sort Filter Problem Office 2010
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 831
NoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really nice
Default

In Tanzania the T is the 3rd character in, the first 2 are unprintable characters so you can't see them.
I ran this code against one of the cells containing TANZANIA to see what was actually in the cell
Code:
Sub CheckOfCharacters()
    Dim i As Integer, str As String
str = ActiveCell.Value
'MsgBox ActiveCell.Font.Name
    For i = 1 To Len(str)
       Debug.Print Mid(str, i, 1) & "  =  " & Asc(Mid(str, i, 1))
       Debug.Print Mid(str, i, 1) & "  =  " & AscW(Mid(str, i, 1))
    Next
End Sub
Running this code fixes it up
Code:
Sub Clean_C_Column()

    Dim lastRow As Long
    Dim rng As Range
    Dim cel As Range
    
With Sheets("Sheet1")
    lastRow = .Range("C" & .Rows.Count).End(xlUp).Row
    Set rng = .Range("C2:C" & lastRow)
    For Each cel In rng
        cel.Value = Replace(cel.Value, ChrW(8203), "")
    Next cel
End With

End Sub
Reply With Quote
  #3  
Old 01-26-2022, 07:02 PM
p45cal's Avatar
p45cal p45cal is offline Sort Filter Problem Windows 10 Sort Filter Problem Office 2019
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

You have 1 or 2 zero-width space characters at the left of many of your cells in the Country column. See Sheet1 (2) of the attached where I've deleted the other columns and added a formula to the right to show each character's unicode from those cells.
Those with 8203 are the zero-width spaces; 2 in front of TANZANIA and plenty of others.
They shouldn't be there and have come in as a result of how you brought the data into Excel (a copy/paste from a website?).
How to remove:
1. Best not to bring them in in the first place if possible - perhaps use Power Query to get that data?
2. Remove them in situ and en masse with a line of vba:
  • Select the cells on the sheet you want to clean up.
  • Go to the Immediate Pane of the VBE (keyboard: press Alt+F11 then Ctrl + G)
  • type and execute this line of code:
selection.Replace What:=ChrW(8203), Replacement:="", LookAt:=xlPart
Test it on column C of Sheet1 (2) of the attached first.
Then your sorting should be fine.
Attached Files
File Type: xlsx msofficeforums48388Excel_Problem_1.xlsx (101.4 KB, 7 views)
Reply With Quote
  #4  
Old 01-27-2022, 03:36 AM
PomDave PomDave is offline Sort Filter Problem Windows 10 Sort Filter Problem Office 2010
Advanced Beginner
Sort Filter Problem
 
Join Date: Sep 2011
Location: Australia
Posts: 43
PomDave is on a distinguished road
Default

Hi All,

Well I'd never have thought of anything like that, I would have started again, copied the data into a new sheet and still had the problem. I did import a spreadsheet a colleague sent me and copied data from that, so something must have happened then, because his sheet lists OK.

Anyway I appreciate the help and will get on to it in the morning.

Best Rgds
Dave R.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
SORT() and FILTER() functions missing Ricko_uk Excel 5 08-24-2020 11:44 AM
Size of filter sort box and open at 1st available row for new data. mobile Excel 0 10-23-2016 03:39 AM
Filter, Sort, Macro... what to do? zingle Excel 3 10-17-2015 06:49 AM
Forgotten Sort and Filter Criteria rbdmg Mail Merge 0 10-31-2011 09:14 PM
sort & filter icon not available or icon not highlighted survivo01 Excel 1 08-08-2011 03:41 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 02:22 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft