Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Macro for work
Ok here's my issue....... I'm going to be getting a new job at work but in order for me to get it there's a certain formula or macro that I need to write and I'm completely lost. The situation is this, I work for a major computer company and every week we get a spreadsheet that has every individual service that our call center has setup. What I do is I go through all the information and basically fail or pass the service based on whether process is followed. So every week a new spreadsheet is added to the workbook with the previous week services, now I have a constant spread sheet called "Repeat Offenders", basically what I'm trying to do is make a formula or a macro that will search the I collumn (pass or fail column) from row 2 and down and the A column (the name collumn) from row 2 down so that the the services that I deam have failed on the weekly spreadsheets will be tallied next to the agents name on the repeat offenders spreadsheet which will just have a list of the agents names. I don't know what to do with this and any help would be greatly appreciated. -- steven_senko ------------------------------------------------------------------------ steven_senko's Profile: http://www.excelforum.com/member.php...o&userid=30417 View this thread: http://www.excelforum.com/showthread...hreadid=500849 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Macro for work
Sorry to be smart, but if we help, do we get the job? :)
Maybe can put it into somewhat simpler/logical language: You're looking for the number of times that an individual has failed. Basically you need a pivot table. :) The data range of your pivot table is determined by the last row of data that has information... I.e., if the names column has an entry at every row until there are no more entries, then if you determine the last row that has any data, you can set the range of your pivot table to be from the top of the spreadsheet to the bottom. Or just select any cell that has data in the header, and provided that every column to the end has a header, you can create a pivot table. Your Row field will the name column, your Drop Data information will be a column that has data in every row. Your Page Field will be the Pass/Fail column. The Drop Data should be a Count not a sum. When the page field 'Failed' is selected, you will have a count next to each name of the number of times that an individual failed. If you want to see pass and fail next to each other, then drag the pass/Fail column data into the columns field. Voila you're done. No programming necessary, and a minimal amount of work is to be done to accomplish your task. Work smarter, not harder. "steven_senko" wrote: Ok here's my issue....... I'm going to be getting a new job at work but in order for me to get it there's a certain formula or macro that I need to write and I'm completely lost. The situation is this, I work for a major computer company and every week we get a spreadsheet that has every individual service that our call center has setup. What I do is I go through all the information and basically fail or pass the service based on whether process is followed. So every week a new spreadsheet is added to the workbook with the previous week services, now I have a constant spread sheet called "Repeat Offenders", basically what I'm trying to do is make a formula or a macro that will search the I collumn (pass or fail column) from row 2 and down and the A column (the name collumn) from row 2 down so that the the services that I deam have failed on the weekly spreadsheets will be tallied next to the agents name on the repeat offenders spreadsheet which will just have a list of the agents names. I don't know what to do with this and any help would be greatly appreciated. -- steven_senko ------------------------------------------------------------------------ steven_senko's Profile: http://www.excelforum.com/member.php...o&userid=30417 View this thread: http://www.excelforum.com/showthread...hreadid=500849 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Macro for work
okay, maybe I should rephrase, lol, I'm a novice excel user, sorry if I seem dumb but I don't quite understand what you're trying to explain. I understand the concept but not how to put that concept in place. -- steven_senko ------------------------------------------------------------------------ steven_senko's Profile: http://www.excelforum.com/member.php...o&userid=30417 View this thread: http://www.excelforum.com/showthread...hreadid=500849 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Macro for work
This is not a VBA implementation, however... Go to the spreadsheet that has
your data. Review the data, if you want to include all of the data on the spreadsheet in your final data review, ensure that every column from the left of the data to the right of the data has something for a title... Like column that has Name at the top, and a column that has Pass/Fail (or similar) at the top A1 B1 Name: Pass/Fail: John Pass Tim Fail John Fail Joe Pass Tim Pass John Fail If the data is not continous, then you will need to select the full range of data that will be used to create your analysis section prior to creating the pivot table. So, to create your pivot table. 1. Select a cell that is in the midst of the data you want to analyze. If you determined that you will need to select the full range of data, then select the full range of data including the titles of the columns 2. Go to the toolbar and select Data 3. Depending on the version of Excel you are using, choose something that lists a PivotTable 4. From the window that appears, use the default option which is to create a pivot table from an excel spreadsheet. 5. Select Next 6. Again depending on the version of Excel being run, some of the following screens may differ. 7. Somewhere towards the beginning of this process you should be shown the data range that is being used to create your pivot table. 8. You can verify that the data range is complete for the data being review, or change it as appropriate 9. Move forward to where you are either presented with a window to drop fields, or you ultimately create a pivot table. If asked where to put your pivot table, go ahead and say on a new sheet. Later when you get more comfortable with this whole operation you may want to put the pivot table on an existing sheet. If you go creating multiple pivot tables on the same data, do yourself a favor and when the first window appears asking about the source of the pivot table, select the option to create the pivot table from an existing pivot table. File sizes are significantly smaller when doing this. But that's for later. 10. When you are presented with the option to put fields into your pivot table, this is where you start using what I was talking about Now, comes the part where you really create the pivot table. I.e., where the pivot table actually provides you useful information. There are four areas in which fields can be dropped. There is a filter area, on older versions of Office (98 and back) the filter field is located to the left of the pivot table in the wizard. On newer versions the filter 'Page' field is located above the pivot table. Drag your pass/fail column identifier to the filter/Page field. On the left of the pivot table, is the Row field, this will be the area you want to know who passed and/or who failed. Drag the name field into the Row Field. The Drop Data area, is basically a number that corresponds to what you are trying to filter data on. In your case, you want to drop a field into the Data area (Located on the lower right side of the pivot table) that is guaranteed to have some piece of data in it... AND is not used in any of your other areas. So you can not use the Name field again, nor the Pass/Fail field. 11. Now depending on how Excel decided to sort the data, the Data field may be considered a SUM or it may be a Count. You are looking for a Count of items. Once the field is dropped into the Data area, you should see somewhere either in the Data Area on older versions, or just above the row data field that the data is a Sum of Pass/Fail or Count of Pass/Fail. If you do not like what you see, then either double click on that "block" or right click and select field settings. Play around a little but you should find a section where it shows SUM or COUNT. Choose COUNT. 12. Now, the way things are initially setup. You will see a count for each person of whether they passed OR failed. If you go up to the Page Field, where there is a dropdown option, and select Failed, the pivot table will change to show the number of times that each individual failed. Voila... Done... As for VBA code, I've never created pivot tables in VBA, but it's not impossible to do all that I just said with VBA. In fact it should be rather easy. Probably could just record a macro when performing the above, modify some of the information to suit your needs and programming style, and you will have the job. Of course you should ask yourself, if you can not perform VBA programming and that is what is expected of you in this new job, are you the right person for the job? There's a lot of helpful people out there, but you can only count on yourself to get things done and done the way you want them... "steven_senko" wrote: okay, maybe I should rephrase, lol, I'm a novice excel user, sorry if I seem dumb but I don't quite understand what you're trying to explain. I understand the concept but not how to put that concept in place. -- steven_senko ------------------------------------------------------------------------ steven_senko's Profile: http://www.excelforum.com/member.php...o&userid=30417 View this thread: http://www.excelforum.com/showthread...hreadid=500849 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Macro for work
Ohh, I also went back and realized that you will be doing a pivot on several
spreadsheets, not just one... If you use the example that I described before, then you can modify your "routine" to encompass more than one spreadsheet. If every spreadsheet has the exact same columns, or at least the two columns in question next to each other, with a third that ALWAYS has data in every cell, then you can do a pivot table off of multiple spreadsheets. Each week that you add a worksheet, you can go back into the wizard (Right clicking on your pivot table.) and add the new worksheet into your pivot table. Seriously there is help out there on pivot tables, they are not too complicated to use, but they can be great tools for data analysis which is what you are basically performing. And think of it.. You'll be a big hero. :) A really easy solution to what looks like a difficult task. "steven_senko" wrote: okay, maybe I should rephrase, lol, I'm a novice excel user, sorry if I seem dumb but I don't quite understand what you're trying to explain. I understand the concept but not how to put that concept in place. -- steven_senko ------------------------------------------------------------------------ steven_senko's Profile: http://www.excelforum.com/member.php...o&userid=30417 View this thread: http://www.excelforum.com/showthread...hreadid=500849 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to update a column in a work based on another work sheet | New Users to Excel | |||
Macro works Macro does not work | Excel Discussion (Misc queries) | |||
Get Macro to work on MAC | Excel Programming | |||
why doesn't macro work? | Excel Programming | |||
Why would this macro work in 02 and not 97 | Excel Programming |