Numbers will not sort

[babypink2807]

Its the most infuriating issue, I have two lots of numbers in one column I wish to sort then use a forumla. The first lot had the little grteen flag, so converted them by text to numbers all fine. The second lot was from my CRM, whch auto exports to Excel, fine. I copied and pasted the latter in to the 1st excel sheet. There is nothing at all showing the little green flag. I checked the format and all states numbers. But t will NOT sort. When I use it to find duplicates by using conditional formatting it wont pick ones up it should. I have tried text to column, the 2nd set of data is all at the bottom it will not sort Interestingly when I do apply the duplicate conditional formatting it only shows up in the 2nd set of data and not the 1st

The original data is in black, to distinguish between the two sets the 2nd set is purple. Which I thought should not make a difference. Originally I tried a VLOOKUP and its only then I realised there was something wrong

 

[Mattlake]

Hi Babypink

This is a silly question, there isnt a gap in the rows between the two sets of data? this would stop sorting and filters

Regards

Matthew

 

[babypink2807]

Hi Babypink

This is a silly question, there isnt a gap in the rows between the two sets of data? this would stop sorting and filters

Regards

Matthew

Not as silly as mine lol.... There isnt a gap. I have just tried the "Clean" formula which worked fine, but not on the 2nd lot of data it came back as #VALUE. So no idea what is going on

 

[babypink2807]

does anyone have any suggestions at all?

 

[Joe4]

Please post a sample of your data for us to see.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.