Advertisement

There's no such thing as a stupid question, but they're the easiest to answer.
Login
Search

Advertisement

Business Applications Business Applications
Search Search
Search for:
Tech Support Guy > > >

Solved: Excel sorting problem


(!)

fahad_m85's Avatar
fahad_m85 fahad_m85 is offline
Member with 129 posts.
THREAD STARTER
 
Join Date: Apr 2009
Location: Pakistan
Experience: Intermediate
26-Sep-2009, 05:29 AM #1
Solved: Excel sorting problem
dear friends
when i sort data in my excel 2007 sheet it give a message

"The cell range that you attempted to sort or remove duplicates from includes merged cells. However not all cells in the range are merged to the same size.
To complete this operation, unmerge all the merged cells in the range, or make each group of merged cells the same size as the largest group."

what can i do in this situtaion
The Villan's Avatar
Member with 2,226 posts.
 
Join Date: Feb 2006
Location: Lincolnshire UK
26-Sep-2009, 04:11 PM #2
Upload a version of it on this thread then we can see what the problem is. No sensitive data though.
fahad_m85's Avatar
fahad_m85 fahad_m85 is offline
Member with 129 posts.
THREAD STARTER
 
Join Date: Apr 2009
Location: Pakistan
Experience: Intermediate
29-Sep-2009, 08:22 AM #3
please find the attached file
there is also another problem when i protect my sheet and also check the option
"sort"
in
"Allow all users of this worksheet to:"
window it dont sort the data
why it is?
Attached Files
File Type: xlsx SAMPLE.xlsx (67.4 KB, 326 views)
The Villan's Avatar
Member with 2,226 posts.
 
Join Date: Feb 2006
Location: Lincolnshire UK
29-Sep-2009, 08:56 AM #4
I have had a look at you spreadsheet. By merging cells you are creating problems for Excel.

To sort data or use data filter, you must not have blank rows or columns.

By merging for example column C & D you are making a big mistake.

You must have a field heading in its own cell so Doc Type is in C6 and D6 is blank. You have merged that. What you need to do is make a spreadsheet where you only have headings using on ecell each and not having these blank cells in between. You can wrap text in a cell, but dont merge 2 cells.

So
A6 DATE
B6 DOC TYPE
C6 NARRATION
D6 KHJ
E6 MSH
F6 OTC
G6 DEBIT
H6 CREDIT
I6 BALANCE

You then put your data underneath that. However your Balance column should not be included in the sort.

You have unfortunately designed this spreadsheet as though it was a word processing document and made it nice and fancy, but taken away all the functionality of the spreadsheet.

There are rules to be adhered to if you are going to avoid pitfalls as you are experiencing.

Unfortunately I have an appointment shortly and will not be back for about 3 hours or so.

I am quite willing to work with you on good spreadsheet design when I get back if that is OK

Cheers
Les
The Villan's Avatar
Member with 2,226 posts.
 
Join Date: Feb 2006
Location: Lincolnshire UK
29-Sep-2009, 09:02 AM #5
Incidentally, you should always create your data area with data and formulas first and get that right and then worry about the headings and formatting etc last.

That is a tip based on experience, so you are free to take that on board or not.
fahad_m85's Avatar
fahad_m85 fahad_m85 is offline
Member with 129 posts.
THREAD STARTER
 
Join Date: Apr 2009
Location: Pakistan
Experience: Intermediate
30-Sep-2009, 01:41 AM #6
dear due to unavoidable it is neccessary for me to create my ledger like this because i dont have only one ledger it is only the sample i have about 120 sheets like this
so that when i create a new ledger of a party i only give the party name in it then all fields create automatically due to formulas like party address, contact no. file no. ledger no. etc
so if i follow your instruction then there is no much space for fields specially party address and contact no. the data is hide in the cells or if i increase the column width then the size of sheet is distrubed and it is not look very well on A4 page and the image of company and specially my image is fallen
so have you any solution for my sheets
The Villan's Avatar
Member with 2,226 posts.
 
Join Date: Feb 2006
Location: Lincolnshire UK
30-Sep-2009, 02:23 AM #7
IMHO you should be using Access Database or an Accounting Package. You then produce reports from that.

However, you have chosen the Excel route,so that doesn't help at the moment.

I am not sure I can do anything, as you have stated, it needs to remain the way it is.

Let me ask some further questions

Why do you need to sort it, if it is in Invoice number order?
The Villan's Avatar
Member with 2,226 posts.
 
Join Date: Feb 2006
Location: Lincolnshire UK
30-Sep-2009, 02:58 AM #8
Another question

What area of the spreadsheet are you trying to sort?

Is it all columns including the balances?
fahad_m85's Avatar
fahad_m85 fahad_m85 is offline
Member with 129 posts.
THREAD STARTER
 
Join Date: Apr 2009
Location: Pakistan
Experience: Intermediate
01-Oct-2009, 12:59 AM #9
Dear i dont want to sort balance i just want to sort dates because they are in desending order
so when i need to dispatch a ledger to a party i want that dates are in asending order so this is my need
The Villan's Avatar
Member with 2,226 posts.
 
Join Date: Feb 2006
Location: Lincolnshire UK
01-Oct-2009, 03:25 AM #10
OK I have been doing some interrogation of your spreadsheet.

There is a way round this, by using a macro that unmerges all cells below the merged headings
Then sort the data from the Date column to the Credit column
Then merge the cells back.

This could be created as a global macro that would work in each file.

I am not into Macro workanymore, but ther are people on this forum who are real smokey with it.

If you would like somebody to develope this with Macro's, please indicate YES in your next post.
The Villan's Avatar
Member with 2,226 posts.
 
Join Date: Feb 2006
Location: Lincolnshire UK
01-Oct-2009, 04:41 AM #11
I am assuming this is in Excel 2007

Your current example that needs to be sorted is from B7 to L500 which includes column headings from DATE to CREDIT and the sorting excludes the headings.

The maco needs to (in this example) be done something like this

Doc Type
Select C7 to C500 and unmerge the cells (it does not include the headings) so when unmerged you then have C7 to D500 which will need to be merged across when the sort has been done.

Narration
Select E7 to E500 and unmerge the cells (it does not include the headings) so when unmerged you then have E7 to F500 which will need to be merged across when the sort has been done.

Debit
Select J7 to J500 and unmerge the cells (it does not include the headings) so when unmerged you then have J7 to K500 which will need to be merged across when the sort has been done.

Credit
Select L7 to L500 and unmerge the cells (it does not include the headings) so when unmerged you then have L7 to N500 (note 3 cells across) which will need to be merged across when the sort has been done.

B7 to N500 or just the records as in this example B7 to N9 need to be sorted as per requirement.

The cells need to be merged again

Doc Type
Select C7 to D500 and "merge across" the cells (it does not include the headings) so when merged you then have C7 to C500

Narration
Select E7 to F500 and "merge across" the cells (it does not include the headings) so when merged you then have E7 to E500

Debit
Select J7 to K500 and "merge across" the cells (it does not include the headings) so when merged you then have J7 to J500

Credit
Select L7 to N500 and "merge across the cells (it does not include the headings) so when merged you then have L7 to L500 (note 3 cells across)

Thats it for just the sorting. Obviously questions need to be asked to see if this example is standard to all the workbooks.

My assumption would be that this would be a global macro that would take all control away from the user whilst it is being done and control being given back to the user afterwards.

I hope that helps for anybody wanting to take on this macro work.
fahad_m85's Avatar
fahad_m85 fahad_m85 is offline
Member with 129 posts.
THREAD STARTER
 
Join Date: Apr 2009
Location: Pakistan
Experience: Intermediate
01-Oct-2009, 09:43 AM #12
yes please reffer me thet who will make this macro for me
The Villan's Avatar
Member with 2,226 posts.
 
Join Date: Feb 2006
Location: Lincolnshire UK
01-Oct-2009, 09:48 AM #13
OK Give it a little time and hopefully somebody will be along who can help you further.

Fancy Pakistan allowing Australia to win. Now England have to play them in the semi final. Pakistan should beat New Zealand in the other semi final.
Zack Barresse's Avatar
Computer Specs
Member with 5,434 posts.
 
Join Date: Jul 2004
Location: Oregon, United States
Experience: I'ma learnin'!
03-Oct-2009, 06:27 PM #14
My advice: unmerge the cells. Period. Deal with the formatting. Merged cells are quite possibly the absolute worst invention ever added into Excel. If you're wanting a header of other sorts, you should be using a text box of sorts. Writing a macro to undo all of the merged cells, perform the action, then re-merge is ludacris.
The Villan's Avatar
Member with 2,226 posts.
 
Join Date: Feb 2006
Location: Lincolnshire UK
03-Oct-2009, 07:46 PM #15
I agree Zack, and have already posed that to Fahad. However, I do not think he is in a position to change things.

It all boils down to, can anybody do the macro or not.

If I had been looking at this 14 years ago, I would have managed it no problem, but today I have no need to write macros anymore or the desire.

I guess Fahad is left with two choices - change, as you and I suggest or just live with the fact he can't sort it, if nobody will write the maacro.
As Seen On

BBC, Reader's Digest, PC Magazine, Today Show, Money Magazine
WELCOME TO TECH SUPPORT GUY!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.


(clock)
THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 


WELCOME
You Are Using: Server ID
Trusted Website Back to the Top ↑