sorting data from merged documents

[Patricia]

OK, here's a bizarre one.

In order to save some time, I asked someone in my office
to help me update an existing file. Instead of using the
file I supplied, she created her own file and merged it
with mine.

Now I can't sort the rows of data from the merged spread
sheet with the original. It's as if the program is
treating the new rows as a single unit instead of
individual rows; the block of new data always appears at
the top of the spread sheet when it should numerically
sort into various rows through the middle. The new data
also does not show up in the columns' filter drop-down
lists.

I've tried changing the cell formats for the entire sheet
so that everything would match. I've tried copying and
pasting values only onto a separate worksheet and re-
formating the entire thing. Neither solved the problem.
Is there anything else I can try before I start cutting
and pasting each individual row into place? This is a
spread sheet I use fairly often, and I'd like to be able
to fix it if possible.

 

[Ken Wright]

Sounds like part of the data is text and part is numeric. Check the very bottom
of the Autofilter list and see if it is there. If you want data that is now
TEXT numbers to be numeric, copy an empty cell, select the range of data
concerned and do Edit / paste Special / Add. It should now all sort properly.

 

[Patricia]

Ken:

I used your advice to convert all the data to numeric and
then re-formatted the entire worksheet as text. The cells
need to be formatted as text because of some necessary
leading zeroes in some entries.

The Good All the entries are now listed in the
filter drop-down list.

The Bad The entries still won't sort properly.
Half of the 1000 - 1200 range (the new data) is still at
the top, and half (the pre-existing data) somewhere in the
middle where it's supposed to be.

Related Question: I changed the cell format several
times, both for the individual columns involved and as a
blanket change in format for the entire worksheet, before
posting the original message. Why were the cells still of
different formats? Is this something peculiar to merged
documents?

Regards,

Patricia

-----Original Message-----
Sounds like part of the data is text and part is

numeric. Check the very bottom

 

[Ken Wright]

You can't just format the cells as text and actually have them convert to text.
You will need to reformat them using a macro. If you are comfortable with VBA
then try the following simple routine, but Backup your data first:-

Sub Maktext()
Dim cel As Range
For Each cel In ActiveSheet.UsedRange
If cel.HasFormula = False Then
cel.Value = "'" & cel.Value
End If
Next cel
End Sub

If this is something you do often, or are not sure how to use a macro, then try
the following:-

Put the code in your personal macro workbook. Hit Tools / Macros / Record New
Macro (Choose to store it in your personal macro Workbook), type a 1 in any
cell, and hit the 'Stop recording' button.

Now Hit ALT+F11 and this will open the VBE (Visual Basic Editor)
Top left you will hopefully see an explorer style pane. Within this pane you
need to search for
your workbook's name, and when you find it you may need to click on the + to
expand it. Within
that you should see the following:-

VBAProject(Personal.xls)
Microsoft Excel Objects
Sheet1(Sheet1)
ThisWorkbook
Modules
Module1

Double click on the module1 and you should see some code like this:-

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 07/01/2004 by Ken Wright
'

'
ActiveCell.FormulaR1C1 = "1"
Range("D9").Select
End Sub

Select all that, delete it and then paste in the code I gave you.
Now just hit File / Save Personal.xls and then do File / Close and return to
Microsoft Excel.

You can now use Tools / Macros / Macro / Maktext on any other Excel file.

If still not comfortable then by all means send me your data if you can and I'll
happily clean it up for you. You need to take the NOSPAM bit out of my email
address and depending on your data, be aware that I work for Lockheed Martin in
my day job.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

Ken:

<snip>

 

[Patricia]

Ken:

Yay! It's fixed!

Thank you!

Patricia

-----Original Message-----
You can't just format the cells as text and actually have them convert to text.
You will need to reformat them using a macro. If you are comfortable with VBA
then try the following simple routine, but Backup your data first:-

Sub Maktext()
Dim cel As Range
For Each cel In ActiveSheet.UsedRange
If cel.HasFormula = False Then
cel.Value = "'" & cel.Value
End If
Next cel
End Sub

If this is something you do often, or are not sure how to use a macro, then try
the following:-

Put the code in your personal macro workbook. Hit Tools / Macros / Record New
Macro (Choose to store it in your personal macro Workbook), type a 1 in any
cell, and hit the 'Stop recording' button.

Now Hit ALT+F11 and this will open the VBE (Visual Basic Editor)
Top left you will hopefully see an explorer style pane. Within this pane you
need to search for
your workbook's name, and when you find it you may need to click on the + to
expand it. Within
that you should see the following:-

VBAProject(Personal.xls)
Microsoft Excel Objects
Sheet1(Sheet1)
ThisWorkbook
Modules
Module1

Double click on the module1 and you should see some code like this:-

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 07/01/2004 by Ken Wright
'

'
ActiveCell.FormulaR1C1 = "1"
Range("D9").Select
End Sub

Select all that, delete it and then paste in the code I gave you.
Now just hit File / Save Personal.xls and then do File / Close and return to
Microsoft Excel.

You can now use Tools / Macros / Macro / Maktext on any other Excel file.

If still not comfortable then by all means send me your data if you can and I'll
happily clean it up for you. You need to take the NOSPAM bit out of my email
address and depending on your data, be aware that I work for Lockheed Martin in
my day job.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

---------------------------------------------------------- ------------------
It's easier to beg forgiveness than ask permission
---------------------------------------------------------- ------------------




<snip>


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.558 / Virus Database: 350 - Release Date: 02/01/2004


.

 

[Ken Wright]

LOL - My pleasure, and thanks for letting me know.