Solved

Sorting with Merge cells? Is it possible...

Posted on 2011-02-19
10
250 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
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
 
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

Technology Partners: 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

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

691 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