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
0
forum1 (79)
6/1/2005 12:38:42 AM
excel.misc 78881 articles. 5 followers. Follow

6 Replies
252 Views

Similar Articles

[PageSpeed] 40

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" <forum@nospam.OfficeKB.com> wrote in message 
news:ff277daf33264d5496023ddef01757e1@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!!
>
> -- 
> Message posted via http://www.officekb.com 


0
nothanks4548 (968)
6/1/2005 2:37:08 AM
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
0
forum (466)
6/1/2005 5:08:50 PM
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" <forum@OfficeKB.com> wrote in message 
news:68fe3292bcc04f538d32e17a3896d4ad@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?
>
> -- 
> Message posted via http://www.officekb.com 


0
nothanks4548 (968)
6/1/2005 8:24:32 PM
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" 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
0
ec357201 (5290)
6/1/2005 10:49:54 PM
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
0
forum (466)
6/2/2005 12:33:11 AM
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
0
ec357201 (5290)
6/2/2005 12:02:58 PM
Reply:

Similar Artilces:

How Excel VBA corporate with MSN Alerts?
How Excel VBA corporate with MSN Alerts? I want MSN Alerts to remind me when an Excel cell value more than 5. Can I make it? For detail: 1.aa.xls and sheets bb has a cell, C5.( in Computer A ) 2.when value of C5 = p` (>5) �� MSN Alerts sent value p` to Computer B. 3.and shake my msn window (ex.cc@msn.com) Can it done in Excel VBA design? Is it free? Thanks for your reading. ...

--Help please
Hi... Using:Excel 2000 & IE 6. Problem: I use Excel for a list. I use borders in Excel. I can save the wooksheet as a HTML file. Both the Excel file & the HTML file look the same on the screen. The problem is when I print the HTML file...The borders don't print. Can this be fixed? The list kinda needs the borders. Thanks for reading ------------------------------------------------------------------ Tony Wilson Email: tony@tonydisabled.com Web Site: http://www.tonydisabled.com I think I'll answer the other one you posted right after. ...

Help PLEASE #5
Hi, I’m looking to bind to one OU which I have the code for connect to 100 existing contacts which I have the code for and mail enable each and every one of them. Can someone help find the vbscript snippet to mail enable all the contacts in Exchange 2003 Thank you, Juan Use the exchmbx util from Joeware. http://www.joeware.net/win/free/tools/exchmbx.htm something like exchmbx -b myContact -me myContact@yourdomain.com I assume you can connect to the OU and enumerate and loop through the contacts. -- -tom "Juan" <Juan@discussions.microsoft.com> wrote in message ...

need to find the file that Outlook emails are stored in
there is one file that contains all the outlook email and information. i need help to find it. also, how can i view Hidden Files? thanks arthur Open Windows Explorer and select Tools | Folder Options. Select Show Hidden Files and Folders and uncheck the option to hide protected operating system files. OK out and open the search panel (f3). If you are using Windows XP, you need to configure the search to look for hidden items. If all goes right, you should find your *.pst file(s) under \documents and settings\<userid>\local settings\application data\microsoft\outlook. -- Neo [...

Need to Change Column of Telephone numbers to just 10 characters
Hi I imported data from another program into excel. The telephone numbers are in the following format 333 333-3333. How do I change it to just having 10 digits without spaces of hyhens. I need it in this format to export to another system Jen Edit>replace find what -, leave replace with blank, then replace again and this time put a space in the find what and replace with nothing -- Regards, Peo Sjoblom "Jennifer Leen" <anonymous@discussions.microsoft.com> wrote in message news:7923E5C4-B199-464F-B34D-9B3B2C726376@microsoft.com... > Hi , > > I imported d...

Help Please
Have a file full of pictures created in Pub 2000. ( About one meg in size ) When I open it, only some pictures show .... as I scroll to the next page with pictures, no pictures show and I get a message saying Pub does not have enough memory to load graphics or embedded objects. What can I do now ? I don't think it is the video driver as it is updated and the original file was created on the same computer. Even tried to open the file on another computer with no luck. Decrease the resolution? Turn off detailed display, view, pictures. Increase your virtual memory? Link, rather t...

Excel VBA PARAMETERS
Hi again! Could somebody tell me the name of the file containing the VISUAL BASIC EDITOR INFORMATION that I could copy from one machine to an other machine? Thank's ahead you all! Could you please explain what is the purpose here? Mika "Bobby" <rpqcca@gmail.com> wrote in message news:09b9267e-9ac4-408c-b7df-348bb030715b@a39g2000pre.googlegroups.com... > Hi again! > Could somebody tell me the name of the file containing the VISUAL > BASIC EDITOR INFORMATION that I could copy from one machine to an > other machine? > Thank's ahead you all! On Dec 1,...

IF FORMULA need help
Please, I am trying really hard to work this out, but still I couldn't come to result I want. This is the example (invoices - paid, not paid...) column A..................... column B...................... column C DUE DATE....................PAID ON........................COMMENT 10.11.05.....................07.11.05........................paid, on time 15.11.05.....................20.11.05........................paid, late 20.11.05...................(empty cell)......................not paid, late 04.12.05...................(empty cell)......................not paid, not late So, you...

Simple Date problem -_- Please Help !
Hi everyone, I have list of times in hh:mm format from cell A1 to A730 e.g. 9:30, 13:00, 13:32, 12:12 etc... How do I put number of minutes passed from 9:00AM i.e. if A1 has 9:30 then B1 must display 30, if A2 has 14:12 then B2 displays 312 and so on. What kind of function do I need to use? Please help! Thanks alot :D format cell as [mm] and use =A1-TIME(9,0,0) James8309 wrote: > Hi everyone, > > I have list of times in hh:mm format from cell A1 to A730 > > e.g. 9:30, 13:00, 13:32, 12:12 etc... > > How do I put number of minutes passed from 9:00AM > >...

Formatting decimal to show in a cell comment with VBA
Can anyone help? This is my current code: Range("F11").Comment.Text Text:=" " & Range("F12") The F12 cell is define as a number with 2 decimal(i.e.: 4.60) If the number that is assign is 4.60 as an example, the result looking at the comment cell F11 is 4.6 the zero is always missing. Thank's ahead! Range("F11").Comment.Text Text:=" " & format(Range("F12"),"0.00") -- Kind regards, Niek Otten "Bobby" <rpqc@hotmail.com> wrote in message news:1131555122.075748.154430@z14g2000cwz.googlegr...

Short Sale Negotiators Needed! No License Required! [0/1]
Post Size: 117 B Files: 1 Parts: 1 Split size: 500 kB Format: yEnc This message is in yEnc format. If your newsreader cannot display this message, please visit http://www.ydecode.com/ and download yEnc decoder. Description: Make $$$$$$ MONEY $$$$$$ Being A Short Sales Negotiator! Earn $15-30,000 in 4 months! No Joke! No License Required! ...

problem with NdisReturnPackets ( ) please help me!
hello all: get BSOD on xp sp2. this code: //////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// NDIS_STATUS PtReceive( IN NDIS_HANDLE ProtocolBindingContext, IN NDIS_HANDLE MacReceiveContext, IN PVOID HeaderBuffer, IN UINT HeaderBufferSize, IN PVOID LookAheadBuffer, IN UINT LookAheadBufferSize, IN UINT PacketSize ) { PADAPT pAdapt = (PADAPT)ProtocolBindi...

http://blogs.technet.com/dst2007
I've seen this site listed buried in some posts, but I thought it might be helpful to have it listed as a main post heading. Many questions are answered there. -- Lee Owens http://leeowens337.blogspot.com ...

Rearranging Data Help...
I have a date in cell E41 (Saturday, April 9) with team names underneath it (cells E42-E48). I also have team names in cells F42-F48. Then in cell E50 I have the date Sunday, April 10 with team names under it also (cells E51-E57). There are also team names in cells F51-F57. I would like to have the date Saturday, April 9 entered in cell A44-A50 and have the teams from cells E42-E48 automatically entered into cells B44-B50 and the teams from cells F42-F48 automatically entered into cells C44-C50. Then have this procedure repeat for each date. If you don't understand I can ema...

HELP! Money 2005 will not load
I have two copies of Money 2005, one loaded onto my Notebook ok. It refuses to load on my desk computer (HP Pavilion), each time it gives a message sayintg that some files could not be loaded and to try again. I do with the same result. MS Support suggested that I run spyware software, then load Money 2005, whilst in 'cleanboot' mode ......... same result Then they suggested that my Dirctway satellite connect program was interferring with it, I uninstalled it and tried again.......same result Then MS gave up, saying I should take out all programs, (to a complete HP restore to get...

Text boxes-help
That is great Jon, thanks. But I am still perplexed. My program does th following: I have 4 columms of data (Dates, stock prices recommendations, target price):1) It graphs the Dates and stock pric columms, 2) when it finds data in the Recommendations columm, i creates a text box in the 5th columm (E), next to the target pric columm (D). Thus my text boxes are created automatically and so is m graph... The next part is that I will write a code so that columms A,C and D ar copied and pasted into the preexisting text boxes when ever a text bo is present and then dragged into the graphic (just b...

Date Issues
I have used one of the methods (via a function) posted on this forum to calculate a date that is 2 days prior to the start date. I called out this function "PrevWorkingDay" from a query. The date calculation appears to be ok. The problem that I am having is setting a date parameter base on this PrevDate on the query. The PrevDate does not seem to be a acting like a date should be. When the date parameter is >=12/25/2007, there is no result. When the date parameter is >=#12/25/2007#, the result is only for dates >=12/25/2007 but excludes 2008 (there are supposed t...

Help with counting user entered text values
Hi, Version: Excel 2000 Situation: In Column G, I will have testers entering in their initals. I would like to display the tester's initals in column H of another worksheet with the number of times that set of intals appeared in column G on column I of the other worksheet. The catch is that I do not have a list of the testers initals ahead of time, so I'll need to pull them from column H. Thanks for any and all help in advance, David Hi David, H10: DAL i10: =COUNTIF(G:G,H10) or if you don't want to see 0 if H10 is empty I10: =IF(H10="","",COU...

DELETE customer with no sales SQL help
I have searched but cannot find the SQL script to delete customers with no sales. I thought I saw it on here at one time. Can anyone help? The problem for me is, I have customers with WORK ORDERS waiting to be filled that I do not want to delete. Their work orders have been tendered but have not been turned into an invoice yet. (This is to delete customers that I imported from my other system a while ago that haven't been back) Thanks for any help. So far, the select query I have is: SELECT [Order].ID AS orderID, [Transaction].TransactionNumber, Customer.AccountNumber FROM [Tra...

VC 6.0 Class Wizard misbehaving. Help?
Hello - Platform: Windows 98 Second Edition, Visual C++ 6.0. The machine has 15 GB of disk available and 512M of RAM. I'm working on a project and decide one more CEdit is needed on the dialog. So a standard edit dialog is placed and the identifier changed to IDC_TIME_DELAY. Then Class Wizard is brought up to assign a variable. While IDC_TIME_DELAY shows up in the Message Maps list, it does not show up in the Member Variables list. Not at all. I've tried deleting the ..NCB file and restarting VC.. and that does not help. A little research through the MSDN and there is a h...

Need recommendations for 500GB external USB drive
My external drive just crashed and I use it solely for backup of my systems. Since I haven't had much luck with the reliability of external drives, I welcome suggestions for a drive that holds approximately 500GB. The one I have now is 466GB capacity. Thank you...... <rmo555@cox.net> wrote in message news:rb46h5tqmsitkmnud4ok09rfnsed2ij7uv@4ax.com... > My external drive just crashed and I use it solely for backup of my > systems. Why did it crash, when only used for back-up? A bit more info is needed, I think. Harry > Since I haven't had much l...

I think I need to create a macro?
Hi, I have a large amount of unformatted data that I need to format in excel and I don't know how! Currently the information is in the format Username Name Title Drive Group Group Group empty row Username Name Title Drive Group Group empty row Username etc The number of groups listed for each user varies. I need to reformat so that on the one row the first column is just the Username, the second is the Name, 3rd is Title, 4th is Drive, 5th is Group. If there is more than one Group (and there are often multiple) the next Group then needs to be in the same column as the f...

HELP-- SMTP Address has not been created in Exchange
Hi All, I've just upgraded my system from Exchange 2000 to 2003 with 2 windows2000 Domain controllers. After the upgrade finished, I tried to create a new user account in active ditrectory and a mail box. Everything seem perfect but, in the user e-mail address properties, there is no any e-mail address in there!!! I tried to update the recipients update service and nothing happen. I ran dcdiag for testing a connectivity with domain controllers. all test was passed. I dont' know hoe to solve this problem. please help me. by the way, the e-mail send and receiving is still workin...

Help with query 01-25-08
Hi, I have a table with the following fields and data Name Relevance Thompson 6 Thompson 6 Thompson 4 Thompson 6 Parker 6 If I sort the data by Relevance, I get Name Relevance Thompson 4 Parker 6 Thompson 6 Thompson 6 Thompson 6 But what I want is Name Relevance Thompson 4 Thompson 6 Thompson 6 Thompson 6 Parker 6 Many thanks Sort Descending By Name and descending by Relevance Or Ascending by Name and Descending by Relevance -- John Spencer Access MVP 2002-2005, 2007-2008 Center for Health Program Development and Management University of Maryland Baltimore Cou...

help searching text
Thanks in advance for your help. In column 1, I have a text string which includes a numeral somewhere in it. In column 2, I have numbers. In column 3, I want to search the text in string in column 1 to see if there is a "6" anywhere in the text and, if there is, I want to place the number in column 2 into the current cell. I thought a simple =if(A1 = "*6*", C1 = B1, 0) would work but it always returns FALSE and, therefore, zero. =IF(COUNTIF($A$1:$A$10,"*"&B1&"*")>0,B1,"Not Found") -- Regards, Peo Sjoblom "Nikko" &l...