Sorting in Excel

Question

I have a spreadsheet that I sort on a number field. This field has values like 23.67, 21.34, etc. Sometimes I enter the data into the spreadsheet and sometimes I cut and paste from another spreadsheet. I format the cell to be "number" with 2 decimals.

Often the result of this search doesn't sort it correctly. It will sort a group of numbers correctly and a separate group of number correctly, but there should only be one group of numbers. Upon inspection, in one of the groups of numbers, each cell has a tiny green triangle in the upper left corner of the cell. if I double click inside the cell and click out of it, the green triangle disappears and the next time I sort, that number goes to the other group correctly.

What gives? My spread sheet has 32,000 rows and it is cumbersome to double click in every cell to get it correct.

Answer 1

Re: cleaning

My free Professional_Compare workbook includes a

Clean Data Utility.
Download from OneDrive...

https://1drv.ms/u/s!Au8Lyt79SOuhZw2MCH7_7MuLj04?e=sAwbHU

'---
Nothing Left to Lose

Answer 2

Here is a MS Article to go with NLTL's screen capture https://support.microsoft.com/en-us/office/what-are-those-buttons-and-triangles-in-my-cells-203e34e6-ba90-4158-ae58-33397dda3ca9 The green triangle is a an error indicator, in this case indicating something that is numeric digits only, but is formatted as text.

Select the whole column

Click on the yellow diamond drop down icon and select "ignore Error"

If you have to do this with multiple columns, and have new data being added, import the data into PowerQuery.

Make sure the column is defined as numeric

Close and load back to new tab.

"Cleaning" data is one of the key functions of PowerQuery. Once the query is setup it can import new data, "clean" it and spit it back out to be used.

.

Do you want to learn about PowerQuery. It may seem intimidating, but this simple task is perfect as an introduction to PowerQuery.

Answer 3

Hi Steve L,

I'm Mukesh and I will be happy to help you out with your question.

The triangle is nothing but the Trace Error. The Trace Error Button image appears next to the cell in which a formula error occurs, and a green triangle appears in the upper-left corner of the cell.

-Select the entire sheet.
-Press F5 to open the "Go-To" dialogue box.
-In the lower-left of the Go-To dialogue box, click "Special".
-Select "Constants"
-Uncheck "Errors" and click ok.
-Now try running your sort and see if it throws the correct results.

I hope this information helps. If you have any questions please let me know and I will be glad to help you out.

--If the reply is helpful, please Upvote and Accept it as an answer--

Regards
Mukesh

Answer 4

Re: "

I suspect that the green triangle comes from #4 on the left side.

Probably because there is a space included with the number or the cells

are formatted as text.

Either one of those will screw up your sort.

'---
Nothing Left to Lose

https://1drv.ms/u/s!Au8Lyt79SOuhZw2MCH7_7MuLj04?e=sAwbHU

(free excel programs)