?
Solved

Sorting with Merge cells? Is it possible...

Posted on 2011-02-19
10
Medium Priority
?
251 Views
Last Modified: 2012-05-11
I am working on a project that will keep track of when employees are assigned to a particular unit. It simply tracks the date they work, the hours and where they worked.
I created this excel sheet because it is easy to print off and fill in the blanks in the field. Then come back and enter into the sheet.

I realize this would be in ACCESS, but I hate access and it hates me. So, I am trying to figure out if this is doable or not.

I would like to be able to sort by their Rank and ID. (column A and B). As you can see each employee has a "range" area that collects all the data. When I sort I want that area (A1-9 and T 1-9) sort as the range. But of course, excel won't do this for me because of my merge cells.

Any idea on if this some how can work? or should I just throw in the towel?

thanks
 EXPERT-EXCHANGE.xls
0
Comment
Question by:bvanscoy678
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
10 Comments
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34934889
No. Don't throw in the towel yet :)

What is the output that you expect?

Sid
0
 

Author Comment

by:bvanscoy678
ID: 34934985
It is a very simple task. I just need to keep a "log" of the date they work, hours and where. Also, in the column under ALS, that is just a class roster for  6 mandatory classes each year. So, it will be just to document what date they went to the class.

This is not a payroll sheet. I will sum their hours, but that is it. I just use sumproduct.

the only reason I need to be able to sort is as time goes by people will get promoted and also new paramedics will join the list. I always have to keep the different ranks together and also sort by their ID (its a seniority number). The worst case is I will have to manually do this by copy and pasting. I am just trying to avoid this.

Thanks

0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34934993
I will give you a basic idea on how to sort merged cell and then you can develop on that. Would that be ok?

Sid
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 2

Accepted Solution

by:
mscanlon06851 earned 2000 total points
ID: 34935275
It's always risky employing merged cells.  They may look nice, but you never know when they'll jump up and bite you.  Generally speaking, I feel much safer using the "Center Across Selection" horizontal alignment format.  

Please take a look at the attached file.

- I unmerged all merged cells
- I moved the location of formulas and entry cells so that they appear more-or-less centered vertically
- In columns H and I, I applied the "Center Across Selection" format
- I inserted a hidden column A (to be used stricktly for sorting purposes)
- I protected some cells so that they couldn't accidenatlly get changed (not password protected)
- I added a macro called "Sort_ID_Rank

The macro:
- unprotects the sheet
- sorts the workbook using the hidden column A
- protects the workbook

It's best to use the macro for sorting because the sorting only works properly if you sort on the hidden column A.  Also, it's critical that Row 1 not be included in the sort.

Hope this helps.


 SortSample.xls
0
 

Author Comment

by:bvanscoy678
ID: 34935317
I tried to open the file, but it won't open and tells me it is corrupt. I have the macro security on the lowest setting.
Any ideas?

I will read up on center across selection in the mean time.

thanks
0
 
LVL 2

Expert Comment

by:mscanlon06851
ID: 34935331
Here's another copy.  


SortSample.xls
0
 

Author Comment

by:bvanscoy678
ID: 34935355
That is extremely cleaver! I see that each row is individual and you used the ID and the Rank in  series so it will sort each line.
I would have never thought of that!

I will read up a bit on the center across selection so I make sure I do it correctly.

I appreciate you time and talents with this project. Never fails to blow me away what people can do in excel.

thanks
Brent
0
 

Author Closing Comment

by:bvanscoy678
ID: 34935357
Yes, very very helpful.
0
 
LVL 2

Expert Comment

by:mscanlon06851
ID: 34935399
Brent, at the risk of stating the obvious:  when adding new segments, copy/paste an existing segment then change the cell contents as appropriate.  Also, I'd make frequent backup copies to protect you in case at some point the sorting screws up the line order.  It's a lot harder to try to implement a fix than to update a recent backup.

Mark
0
 

Author Comment

by:bvanscoy678
ID: 34937260
Copy and pasting is what I have done. I also added a formula that concatenates the id number, rank and adds a number. That way when my 3 users add someone, they don't need to worry about Column A. I have my computer back up my files automatically every night. I learned that lesson a few years ago the hard way.

Thanks for the comments.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question