Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 80
Default Sort isn't working correctly

The cells in col A and in col B contain the same data.
All cells have a "General" format.
Both columns are unsorted.

I highlight col A and use DATA/SORT/SORT BY COLUMN A/SORT ON VALUES/ORDER A to Z then click OK.

I highlight col B and use DATA/SORT/SORT BY COLUMN B/SORT ON VALUES/ORDER A to Z then click OK.

I expect the cells in both columns to be sorted in the same order but they aren't. *(For example: 2008-ITALY001.jpg is in A137 and in B61).

Why isn't sort working correctly?
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 86
Default Sort isn't working correctly

On 11/4/2016 10:28 PM, GARYWC wrote:
The cells in col A and in col B contain the same data.
All cells have a "General" format.
Both columns are unsorted.

I highlight col A and use DATA/SORT/SORT BY COLUMN A/SORT ON VALUES/ORDER A to Z then click OK.
I highlight col B and use DATA/SORT/SORT BY COLUMN B/SORT ON VALUES/ORDER A to Z then click OK.
I expect the cells in both columns to be sorted in the same order but they aren't. (For example: 2008-ITALY001.jpg is in A137 and in B61).
Why isn't sort working correctly?


Can you post the picture somewhere online and then provide a link to it?
Dropbox would work.

Just to be sure, did you run any code to verify that the data is exactly
the same in both columns, e.g.
http://www.techrepublic.com/blog/mic...of-excel-data/


  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 80
Default Sort isn't working correctly

https://1drv.ms/f/s!AtYv9NwmLzr4dw6JzHm1n3joiBM

  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default Sort isn't working correctly

On 5/11/2016 4:57 PM, GARYWC wrote:
https://1drv.ms/f/s!AtYv9NwmLzr4dw6JzHm1n3joiBM

It's probably because the data is not the same in both columns. Column
'A' contains both the file name and the date and size etc whereas Column
'B' only contains the file name. Because of the extra data in column 'A'
it will sort differently.
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 80
Default Sort isn't working correctly

Cols A and B now contain only the filename. Here is the new file:

https://d.docs.live.net/9a32780eab1e6d22/bad-sort.xlsx

The sort still isn't working correctly.


  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 80
Default Sort isn't working correctly

Cols A and B now contain only the filename. Here is the new file:

https://1drv.ms/x/s!AiJtHqsOeDKab4uQi4gvqTjjy_8

The sort still isn't working correctly.



  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 80
Default Sort isn't working correctly

Note: The columns in the new file are NOT sorted. You can sort them and see how they get sorted.
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 80
Default Sort isn't working correctly

Solved!

  #9   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 86
Default Sort isn't working correctly

On 11/5/2016 12:40 PM, GARYWC wrote:
Solved!


How, exactly?
  #10   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 80
Default Sort isn't working correctly

When extracting the data with EXIFTOOL, I used the -csv parameter to output a comma-separated values (CSV) file. I then opened the CSV file in Excel and ticked "Delimited" and unticked the "tab" delimiter and ticked the "comma" delimiter. Apparently, that "corrupted" the filenames so Excel couldn't sort the data correctly.

I re-extracted the data with EXIFTOOL with the -csv parameter. When I opened the CSV file, I ticked "Delimited" and left the "tab" delimiter ticked. This time, the filenames did not become corrupted and Excel sorted the data correctly.


  #11   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 80
Default Sort isn't working correctly

I specified that the extracted data be output to a comma-separated values (CSV) file. In Excel, I opened the CSV file by ticking "Delimited", unticking the "tab" delimiter and ticking the "comma" delimiter. *Apparently, that "corrupted" the filenames so Excel couldn't sort the data correctly.

I re-extracted the data by specifying the data be output to a comma-separated values (CSV) file. In Excel, I opened the CSV file by again ticking "Delimited" but leaving the "tab" delimiter ticked. *This time, the filenames did not become corrupted and Excel sorted the data correctly.
  #12   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 86
Default Sort isn't working correctly

On 11/5/2016 10:44 PM, GARYWC wrote:
I specified that the extracted data be output to a comma-separated values (CSV) file. In Excel, I opened the CSV file by ticking "Delimited", unticking the "tab" delimiter and ticking the "comma" delimiter. Apparently, that "corrupted" the filenames so Excel couldn't sort the data correctly.

I re-extracted the data by specifying the data be output to a comma-separated values (CSV) file. In Excel, I opened the CSV file by again ticking "Delimited" but leaving the "tab" delimiter ticked. This time, the filenames did not become corrupted and Excel sorted the data correctly.


Thanks, glad you sorted it.


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Sort isn't working correctly GARYWC Excel Programming 0 November 5th 16 05:57 AM
how to sort correctly with this formula being used Brad[_3_] New Users to Excel 2 June 9th 09 09:15 PM
Numbers won't sort correctly. FernW Excel Discussion (Misc queries) 10 April 1st 05 01:29 PM
Numbers won't sort correctly. FernW Excel Discussion (Misc queries) 1 March 3rd 05 03:46 PM
How to sort a list correctly? Melani New Users to Excel 1 November 29th 04 04:17 PM


All times are GMT +1. The time now is 08:58 AM.

Powered by vBulletin® Copyright ©2000 - 2023, Jelsoft Enterprises Ltd.
Copyright 2004-2023 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"