Help with Macro for work

[steven_senko]

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 all the weekly spreadsheets in the I collumn (pass or fail column) from row 2 and down for (fail) and the A column (the name collumn) from row 2 down for the agents name 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.

[GB]

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

[steven_senko]

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.

[GB]

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

[GB]

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