Sorting debacle

[Lablover]

So, here is a frustrating story. Today I spent about 12 hours creating
an Excel sheet with information about several hundred newspaper
articles. I am working on my master's thesis and articles come from two
databases, LexisNexis (LN) and America's Newspapers (AN). The articles
are in two separate Excel spreadsheets, one for AN and one for LN. I
need to combine the two worksheets and sort them by year, month, and
day. I did that by cutting and pasting them into the same document.
Despite the fact that the rows and columns of each are exactly the
same, when I go to sort by year, it sorts the LN articles by year and
the AN articles separately by year. It won't sort them together by
year. I am fearing a lot of cutting and pasting in my future. Any
suggestions you have would be greatly appreciated.

 

[Mike Middleton]

Lablover -

Maybe some of the dates are numerical and some of the dates are text, in
which case they would sort as you described.

If so, to fix it, make a copy of the worksheet, just in case. First, select
an empty cell, and choose Edit | Copy. Then select all of the dates, and
choose Edit | Paste Special | Add. This might coerce all entries to sortable
numerical dates.

- Mike
www.mikemiddleton.com

 

[Lablover]

Mike,
Thanks for responding. I tried what you suggested, but no luck. I
also selected the column and tried formatting cells as "text" and then
as "number". Neither way worked. I have had this problem before when
the cells have spaces in them in weird places so they don't exactly
align properly, but that isn't the case this time. If you have any
other ideas please let me know.
Thanks!
Liz

 

[JE McGimpsey]

Thanks for responding. I tried what you suggested, but no luck. I
also selected the column and tried formatting cells as "text" and then
as "number". Neither way worked. I have had this problem before when
the cells have spaces in them in weird places so they don't exactly
align properly, but that isn't the case this time. If you have any
other ideas please let me know.

Changing the format won't affect numbers entered as Text - once XL
thinks they're text, number formatting is no longer applied. Nor will
changing the number format to Text change numbers to text - though it
will, by default, change the alignment to Left.

That said, Mike's suggestion should coerce any text numbers to real
numbers (which is what dates are).

I can't tell from your original description exactly what's going on. You
say you want to sort by Year, Month, and Day, but then you say you sort
by Year - are your dates actual dates? or are you separating the dates,
with Year in one column, month in another, and day in a third?

Another problem that sometimes arises when one pastes data from a web
search is that web designers sometimes use non-breaking spaces (ascii
character 160) to separate data items. XL doesn't treat these as white
space when you try to coerce the data to numeric - they'll stay text.

This macro will remove those characters:

http://www.mvps.org/dmcritchie/excel/join.htm#trimall