Damsel in distress needs VBA help!!!

[Lisa H via OfficeKB.com]

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

 

[Earl Kiosterud]

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.

 

[Lisa H via OfficeKB.com]

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?

 

[Earl Kiosterud]

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.

 

[Dave Peterson]

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,FALSE))

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]

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

 

[Dave Peterson]

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>