[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 254
  • Last Modified:

Sorting with Merge cells? Is it possible...

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
bvanscoy678
Asked:
bvanscoy678
  • 5
  • 3
  • 2
1 Solution
 
SiddharthRoutCommented:
No. Don't throw in the towel yet :)

What is the output that you expect?

Sid
0
 
bvanscoy678Author Commented:
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
 
SiddharthRoutCommented:
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.

 
mscanlon06851Commented:
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
 
bvanscoy678Author Commented:
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
 
mscanlon06851Commented:
Here's another copy.  


SortSample.xls
0
 
bvanscoy678Author Commented:
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
 
bvanscoy678Author Commented:
Yes, very very helpful.
0
 
mscanlon06851Commented:
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
 
bvanscoy678Author Commented:
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

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 5
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now