Solved

Sorting with Merge cells? Is it possible...

Posted on 2011-02-19
10
245 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
  • 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
 
LVL 2

Accepted Solution

by:
mscanlon06851 earned 500 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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

706 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now