Excel 2003 - Merged Cells Size Restriction When Sorting
I suspect the answer to this is No, but I want to check anyway.
I have a report that has been dumped to Excel and formatted using Merged Cells.
I need to sort a number column in this report, but it won't let me because the size of the merged cells do not match. I tried unmerging the cells, but that totally messes up all the formating.
Is there any slick trick way around the merged cells size restriction with respect to column sorting?
Re: Excel 2003 - Merged Cells Size Restriction When Sorting
Quote:
I suspect the answer to this is No, but I want to check anyway.
Yes there is a way. Using VBA code... Would that help?
Re: Excel 2003 - Merged Cells Size Restriction When Sorting
Quote:
Originally Posted by
koolsid
Yes there is a way. Using VBA code... Would that help?
I could put a button on the report, run some code, and then just delete the button, so yeah...if the VBA code did what I needed, and maintained the existing formatting, then that should work.
What slick trick do you have up your sleeve? :D
Re: Excel 2003 - Merged Cells Size Restriction When Sorting
Quote:
Originally Posted by
Hack
What slick trick do you have up your sleeve? :D
Ha Ha
1) Put the values of let's say Col "A" in an Array
2) Sort the array
3) Put them back :D
Lemme know if you want an example?
Re: Excel 2003 - Merged Cells Size Restriction When Sorting
Well, I can do that...but I have data in columns A through I
Colums D,E,and F are merged as are J,K,L and M
C: is a dollar column
A: is a text column which corresonds to the dollar amount in column C:
I need C: sorted in descending order, but it has to maintain its relationship with the rest of the columns so just sorting the one column would kind of mess things up.
I can remove all sensitive information and attach a generic example if you want.
Re: Excel 2003 - Merged Cells Size Restriction When Sorting
Quote:
I can remove all sensitive information and attach a generic example if you want.
Perfect :)
1 Attachment(s)
Re: Excel 2003 - Merged Cells Size Restriction When Sorting
I need "Drug Payments" sorted in descending order.
Thanks...if you can pull this off you will be on my Christmas Card List :D
1 Attachment(s)
Re: Excel 2003 - Merged Cells Size Restriction When Sorting
Something like this?
If the format changes then the vba code needs to be changed as well...
I have created a 'Test Sheet' so that the 'Original Sheet' can be used to compare with the 'Test Sheet'.
Sid
Edit:
I tested it again... so don't forget to put perfume on the card.... :afrog:
1 Attachment(s)
Re: Excel 2003 - Merged Cells Size Restriction When Sorting
I'm not making out my card list just yet. :D
Seriously...I know Sort is a valid property so I'm not sure why this is happening.
Re: Excel 2003 - Merged Cells Size Restriction When Sorting
Give me 15 mins... I think I know what the problem is...
Sid
Re: Excel 2003 - Merged Cells Size Restriction When Sorting
Re: Excel 2003 - Merged Cells Size Restriction When Sorting
Quote:
Originally Posted by
koolsid
Update the Zip file
:confused: Update it with what?
Re: Excel 2003 - Merged Cells Size Restriction When Sorting
My Bad... I replaced the zip file (wanted to say updated the file)
Re: Excel 2003 - Merged Cells Size Restriction When Sorting
Hmmm...the drug name gets sorted, but the drug payment does not.
Re: Excel 2003 - Merged Cells Size Restriction When Sorting
5 mins... let me recheck
Edit:
How stupid of me... Hmph!!!! I have Updated the Zip File
1 Attachment(s)
Re: Excel 2003 - Merged Cells Size Restriction When Sorting
All of the columns sort together, but as you can see the numbers are not in order.
Maybe this is an Excel thing.
Re: Excel 2003 - Merged Cells Size Restriction When Sorting
What is your sort criteria?
DRUG NAME - Descending
and then? (you can suggest Two more)
Re: Excel 2003 - Merged Cells Size Restriction When Sorting
The user wants the drug payment amount sorted descending as criteria 1 and drug name goes with the payment amount as do all the rest of the columns.
I'm thinkin' I might have been right and this just isn't gonna happen.
I'll play around with it a bit.
Re: Excel 2003 - Merged Cells Size Restriction When Sorting
Quote:
I'm thinkin' I might have been right and this just isn't gonna happen.
Hack, Excel gives you 3 criteria to sort on. based on your above query, I had sorted it only on 1 criteria i.e the drug name. Can you tell me the third one?
1st is drug payment amount
2nd is drug name
Which is the 3rd?
Re: Excel 2003 - Merged Cells Size Restriction When Sorting
This code takes under 0.7 second on my PC (and it won't cost you a cent.:))
vb Code:
Sub SortMerge()
Dim r1 As Long
Dim r2 As Long
Dim t As Single: t = Timer
With Sheet1
On Error Resume Next
r1 = WorksheetFunction.Match("DRUG NAME", .Columns(1), 0)
If r1 = 0 Then MsgBox "Header ""DRUG NAME"" not found.": Exit Sub
r2 = WorksheetFunction.Match("GRAND TOTAL", .Columns(1), 0) - 2
If r2 = 0 Then MsgBox """GRAND TOTAL"" not found.": Exit Sub
On Error GoTo 0
Application.ScreenUpdating = False
With .Range("A" & r1 & ":M" & r2)
'-- need to unmerge before sorting
.UnMerge
'-- don't care about DRUG NAME column: sort by "DRUG PAYMENTS" only
.Sort Key1:=.Cells(1, 3), Order1:=xlDescending, Header:=xlYes
'-- re-merge across
.Columns("A:B").Merge Across:=True
.Columns("C:F").Merge Across:=True
.Columns("I:M").Merge Across:=True
End With
Application.ScreenUpdating = True
MsgBox "Done!" & vbCrLf & Format(Timer - t, "0.000") & " secs."
End With
End Sub
Re: Excel 2003 - Merged Cells Size Restriction When Sorting
I feel you gave up too soon! :D
What you want is absolutely achievable. See post 19
Sid