Solved

Sorting with Merge cells? Is it possible...

Posted on 2011-02-19
10
249 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
Industry Leaders: 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!

 
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

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

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

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

733 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