Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Damsel in distress needs VBA help!!!
Hi, I have 45 customer service reps weekly schedules in an excel
spreadsheet. They all are on one of 4 teams and are listed alphabetically as so: Doe,John start time break 1 lunch break 2 Monday 8:00 10:15 1:30 3:15 Tuesday Weds etc.. I need them to be sorted by team. I think a macro for each team would be the easiest way to do this. I need code that would go through the alphabetical list and search for an agents name and delte that row and the 10 rows below it. I have no idea where to begin or even how to be able to set this up for 45 people! Please someone help!! -- Message posted via http://www.officekb.com |
#2
|
|||
|
|||
Lisa,
You will probably need to get this into a bona fide database-style table: Name Weekday break 1 lunch break 2 Doe Monday 8:00 10:15 1:30 3:15 Doe Tuesday 8:00 10:15 1:30 3:15 etc. Each record stands alone, and has all the necessary information. Now you can do all kinds of stuff with it with Excel tools. Sort, filter, subtotal, pivot table summaries, etc. -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "Lisa H via OfficeKB.com" wrote in message ... Hi, I have 45 customer service reps weekly schedules in an excel spreadsheet. They all are on one of 4 teams and are listed alphabetically as so: Doe,John start time break 1 lunch break 2 Monday 8:00 10:15 1:30 3:15 Tuesday Weds etc.. I need them to be sorted by team. I think a macro for each team would be the easiest way to do this. I need code that would go through the alphabetical list and search for an agents name and delte that row and the 10 rows below it. I have no idea where to begin or even how to be able to set this up for 45 people! Please someone help!! -- Message posted via http://www.officekb.com |
#3
|
|||
|
|||
I would love to do that but the format that I receive it in is this way and
that is the way it is easiest for the supervisors to keep, so I can't change that format. Any other ideas? -- Message posted via http://www.officekb.com |
#4
|
|||
|
|||
Lisa,
Nope. Tools such as sorting, summarizing, etc. require a table format, but it's not particularly suitable for presentation. Excel won't change the layout for you. Access does stuff like that. A macro could be written for your particular layout. Here's something you may wish to do. To sort them manually, select all of the rows of a particular group, then edge-drag it to where it belongs, holding Shift. It will give you an insert line, where it'll tuck the group in, and it will delete the vacated rows, all in one operation. Don't let go of Shift until you've let go of the mouse button. If you miss, just do Undo (Ctrl-z), and go again. -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "Lisa H via OfficeKB.com" wrote in message ... I would love to do that but the format that I receive it in is this way and that is the way it is easiest for the supervisors to keep, so I can't change that format. Any other ideas? -- Message posted via http://www.officekb.com |
#5
|
|||
|
|||
I think I'd add another worksheet (hidden???) or in another workbook that
assigned names to teams: Doe,John TeamA Smith,John TeamB .... Then I'd insert a new column A and use a formula to return the team name into that column. It sounds like there are 11 rows per person. I'm gonna guess that your real data starts in row 2 (headers in row 1). Then I'd use a formula like this in A2 and drag down: =IF(MOD(ROW(),11)<2,A1,VLOOKUP(B2,Sheet2!A:B,2,FA LSE)) Then I could apply Data|Filter|Autofilter to show/hide the teams I wanted to see/hide. In fact, I could show all the teams I don't want and delete those visible rows. I'd do my best to convince management to keep this workbook. You could hide the worksheet and that column if they objected too much. (Just unhide them when you need them.) "Lisa H via OfficeKB.com" wrote: Hi, I have 45 customer service reps weekly schedules in an excel spreadsheet. They all are on one of 4 teams and are listed alphabetically as so: Doe,John start time break 1 lunch break 2 Monday 8:00 10:15 1:30 3:15 Tuesday Weds etc.. I need them to be sorted by team. I think a macro for each team would be the easiest way to do this. I need code that would go through the alphabetical list and search for an agents name and delte that row and the 10 rows below it. I have no idea where to begin or even how to be able to set this up for 45 people! Please someone help!! -- Message posted via http://www.officekb.com -- Dave Peterson |
#6
|
|||
|
|||
Dave,
I have read some of the help you have given others on a few different websites. How did I know you would be the one to help me?! It works perfectly! Exactly the way I need it. I can keeep the extra worksheets because management doesn't look at the workbook just the finished pdf file that I send out of it. Thank you Dave for the wealth of knowledge. one of your many pupils Lisa -- Message posted via http://www.officekb.com |
#7
|
|||
|
|||
But I still agree with Earl's comments--but glad you got something that worked.
Laying out the data nicely is usually the best solution--darn users always get in the way! <vbg "Lisa H via OfficeKB.com" wrote: Dave, I have read some of the help you have given others on a few different websites. How did I know you would be the one to help me?! It works perfectly! Exactly the way I need it. I can keeep the extra worksheets because management doesn't look at the workbook just the finished pdf file that I send out of it. Thank you Dave for the wealth of knowledge. one of your many pupils Lisa -- Message posted via http://www.officekb.com -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|