Hi all, I have an Excel spreadsheet that is linked to an SQL Database for our timeclock. When I click the refresh button the Excel sheet is populated with the Name, Date, & Hours worked for the day so each user will have multiple rows of data as this is done on a weekly basis. I have this sorted by Name first, then Time descending, so the same users time will all be together in descending time order but they are all right on top of each other. What I need is for Excel to automatically insert a blank row under each group of Names that will autosum the hours worked, then add another blank row under that to separate each Name.
So basically:
---------------------
Mark 11/3/2009 7:29
Mark 11/2/2009 7:31
Mark 11/1/2009 7:45
John 11/3/2009 8:51
John 11/2/2009 8:32
John 11/1/2009 8:13
Mary 11/3/2009 6:40
Mary 11/2/2009 6:45
Mary 11/1/2009 6:53
---------------------
Becomes:
--------------------------
Mark 11/3/2009 7:29
Mark 11/2/2009 7:31
Mark 11/1/2009 7:45
Mark Hrs Worked 22:45 <-------- Total
<-------- Blank
John 11/3/2009 8:51
John 11/2/2009 8:32
John 11/1/2009 8:13
John Hrs Worked 25:36 <-------- Total
<-------- Blank
Mary 11/3/2009 6:40
Mary 11/2/2009 6:45
Mary 11/1/2009 6:53
Mary Hrs Worked 20:18 <-------- Total
<-------- Blank
--------------------------
Thanks in advance!
Jon
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
I dont think this is going to work using a Pivot Table. I have like 10 other columns that go along with the record that arent used for calculations but they need to be there & easily readable. Using the pivot table makes every record expandable so it doesnt put the data next to each other. Look at what happens when I add another field.
Ok by using the Old Pivot Table format I was able to get them next to each other but now I'm having a problem with the totals. This is the exact file that I'm using (I should have uploaded it before). All I need is for the format to stay the way that it looks right now but on the blank lines that split each user I need it to total that last column. Thanks again for the help with this.
Business Accounts
Answer for Membership
by: roryaPosted on 2009-11-03 at 14:46:57ID: 25734728
It wouldn't be quite the layout you describe, but would a pivot table be acceptable? It would be very easy to set up and wouldn't involve breaking your linked querytable.