VBA To Replace Duplicate Values with Zero (Complex)
 

Hi Trisha

i might be missing the point here, but if you added up the total hours for
the employee and then deducted the total of B*, C*, L* won't you get the
answer you want?

Cheers
JulieD

"Trisha B." wrote in message
...
We have a series of reports generated by an Access 2000 database in our
Payroll department which are sent to our Financial Services department.
The
report consists of several columns (only three of which concern me) and
10,000+ rows.

The columns a Column A the employee ID, Column B the pay code, and
Column C the number of hours worked at that pay code (there are no column
headings on the report).

The rows contain every payroll code for every employee for a given
two-week
pay period, and the number of hours worked at that code (e.g., "A" is
regular
day shift, "AE" is regular evening shift, "BE" is time and a half evening
shift, and it goes on).

The problem discovered yesterday is that overtime hours are being reported
twice, once as code AE and again as code BE. This also occurs with code
CE
(double time evening shift) as well as with K's (Inservice Days) and L's
(Inservice Overtime days).

The original developer of the database is not available and, for reasons I
won't go into here, the preference is that I adjust the report, not the
database. Hence my request for your help.

A very small representation of the worksheet would be very similar to this
(forgive the spacing, I know it never posts as written):

A B C
1 1122 AE 8.00
2 1122 AE 1.00
3 1122 BE 1.00
4 1122 JD 8.00
5 1234 AE 8.00
6 1234 CE 8.00
7 1234 AF 24.00
8 1234 SL 8.00
9 1234 SLE 8.00

What has been requested of me is:

1. To write code that will loop through each employee's recordset for the
given pay period,

2. Find any instances where the value of Column B = any B* (* indicating a
wildcard) or C*and, if found,

3. Also find the corresponding A* value whose hours worked (Column C) is
equal to the hours worked for the B* or C* value and convert the hours
that
relate to the matching A* to 0.00 then

4. Repeat the process if there is a K and L (K becomes the A, L the B*,
C*).

In the example above, in row 3, column B has a value of BE so they want me
to automate finding an A* pay code in the first four lines (as define the
employee ID) and also find the matching value in column C (so it would be
line 2, column B) and convert line 2 column C from 1.00 to 0.00.

After it does that, it would then go on to the next employee and repeat
the
process. Employees have different numbers of rows, some as few as three,
others in excess of 10, for each pay period with no breaks between rows.

We have approximately 10 old worksheets for this to be applied to and all
future worksheets will need the VBA code run against them.

Any assistance with this would be greatly appreciated.

Many thanks in advance

VBA To Replace Duplicate Values with Zero (Complex)
 

Julie,

They'd get the total, but not correct recordset values. The hours for A* or
K should be 0.00 if B* and C* or L* respectively exist, so the totals would
be correct, but the detail data would not. (This is a case where the report
is more than the sum, or difference, of its parts.)

Financial Services needs the correct detail data, and the A* and K hours pay
coding is what's incorrect, and they want it corrected. But I do appreciate
your thoughts (and reading through the description of the problem!). If
they'd let me work on the actual database, I'd have a better idea of what I'm
doing, but I use VBA in Excel about four times a year, which is not enough to
be proficient.

Thanks for responding,
Trisha

"JulieD" wrote:

Hi Trisha

i might be missing the point here, but if you added up the total hours for
the employee and then deducted the total of B*, C*, L* won't you get the
answer you want?

Cheers
JulieD

"Trisha B." wrote in message
...
We have a series of reports generated by an Access 2000 database in our
Payroll department which are sent to our Financial Services department.
The
report consists of several columns (only three of which concern me) and
10,000+ rows.

The columns a Column A the employee ID, Column B the pay code, and
Column C the number of hours worked at that pay code (there are no column
headings on the report).

The rows contain every payroll code for every employee for a given
two-week
pay period, and the number of hours worked at that code (e.g., "A" is
regular
day shift, "AE" is regular evening shift, "BE" is time and a half evening
shift, and it goes on).

The problem discovered yesterday is that overtime hours are being reported
twice, once as code AE and again as code BE. This also occurs with code
CE
(double time evening shift) as well as with K's (Inservice Days) and L's
(Inservice Overtime days).

The original developer of the database is not available and, for reasons I
won't go into here, the preference is that I adjust the report, not the
database. Hence my request for your help.

A very small representation of the worksheet would be very similar to this
(forgive the spacing, I know it never posts as written):

A B C
1 1122 AE 8.00
2 1122 AE 1.00
3 1122 BE 1.00
4 1122 JD 8.00
5 1234 AE 8.00
6 1234 CE 8.00
7 1234 AF 24.00
8 1234 SL 8.00
9 1234 SLE 8.00

What has been requested of me is:

1. To write code that will loop through each employee's recordset for the
given pay period,

2. Find any instances where the value of Column B = any B* (* indicating a
wildcard) or C*and, if found,

3. Also find the corresponding A* value whose hours worked (Column C) is
equal to the hours worked for the B* or C* value and convert the hours
that
relate to the matching A* to 0.00 then

4. Repeat the process if there is a K and L (K becomes the A, L the B*,
C*).

In the example above, in row 3, column B has a value of BE so they want me
to automate finding an A* pay code in the first four lines (as define the
employee ID) and also find the matching value in column C (so it would be
line 2, column B) and convert line 2 column C from 1.00 to 0.00.

After it does that, it would then go on to the next employee and repeat
the
process. Employees have different numbers of rows, some as few as three,
others in excess of 10, for each pay period with no breaks between rows.

We have approximately 10 old worksheets for this to be applied to and all
future worksheets will need the VBA code run against them.

Any assistance with this would be greatly appreciated.

Many thanks in advance

VBA To Replace Duplicate Values with Zero (Complex)
 

If anyone has been working on a solution for this, please know it has been
solved, albeit not through using any Microsoft product.

As it turns out, the Access 2000 database is in turn populated through a
third-party product (Kronos). There is a setting in Kronos that prevents the
duplicates from occurring so we won't have to eliminate them in the Excel
workbook.

Thanks to anyone who spent time on this.

Trisha

"Trisha B." wrote:

Julie,

They'd get the total, but not correct recordset values. The hours for A* or
K should be 0.00 if B* and C* or L* respectively exist, so the totals would
be correct, but the detail data would not. (This is a case where the report
is more than the sum, or difference, of its parts.)

Financial Services needs the correct detail data, and the A* and K hours pay
coding is what's incorrect, and they want it corrected. But I do appreciate
your thoughts (and reading through the description of the problem!). If
they'd let me work on the actual database, I'd have a better idea of what I'm
doing, but I use VBA in Excel about four times a year, which is not enough to
be proficient.

Thanks for responding,
Trisha

"JulieD" wrote:

Hi Trisha

i might be missing the point here, but if you added up the total hours for
the employee and then deducted the total of B*, C*, L* won't you get the
answer you want?

Cheers
JulieD

"Trisha B." wrote in message
...
We have a series of reports generated by an Access 2000 database in our
Payroll department which are sent to our Financial Services department.
The
report consists of several columns (only three of which concern me) and
10,000+ rows.

The columns a Column A the employee ID, Column B the pay code, and
Column C the number of hours worked at that pay code (there are no column
headings on the report).

The rows contain every payroll code for every employee for a given
two-week
pay period, and the number of hours worked at that code (e.g., "A" is
regular
day shift, "AE" is regular evening shift, "BE" is time and a half evening
shift, and it goes on).

The problem discovered yesterday is that overtime hours are being reported
twice, once as code AE and again as code BE. This also occurs with code
CE
(double time evening shift) as well as with K's (Inservice Days) and L's
(Inservice Overtime days).

The original developer of the database is not available and, for reasons I
won't go into here, the preference is that I adjust the report, not the
database. Hence my request for your help.

A very small representation of the worksheet would be very similar to this
(forgive the spacing, I know it never posts as written):

A B C
1 1122 AE 8.00
2 1122 AE 1.00
3 1122 BE 1.00
4 1122 JD 8.00
5 1234 AE 8.00
6 1234 CE 8.00
7 1234 AF 24.00
8 1234 SL 8.00
9 1234 SLE 8.00

What has been requested of me is:

1. To write code that will loop through each employee's recordset for the
given pay period,

2. Find any instances where the value of Column B = any B* (* indicating a
wildcard) or C*and, if found,

3. Also find the corresponding A* value whose hours worked (Column C) is
equal to the hours worked for the B* or C* value and convert the hours
that
relate to the matching A* to 0.00 then

4. Repeat the process if there is a K and L (K becomes the A, L the B*,
C*).

In the example above, in row 3, column B has a value of BE so they want me
to automate finding an A* pay code in the first four lines (as define the
employee ID) and also find the matching value in column C (so it would be
line 2, column B) and convert line 2 column C from 1.00 to 0.00.

After it does that, it would then go on to the next employee and repeat
the
process. Employees have different numbers of rows, some as few as three,
others in excess of 10, for each pay period with no breaks between rows.

We have approximately 10 old worksheets for this to be applied to and all
future worksheets will need the VBA code run against them.

Any assistance with this would be greatly appreciated.

Many thanks in advance

VBA To Replace Duplicate Values with Zero (Complex)
 

Hi Trisha

thanks for letting us know .. .i was just about to sit down & attempt to nut
out a solution :) (hopefully anyway!) .. glad its solved.

Cheers
JulieD

"Trisha B." wrote in message
...
If anyone has been working on a solution for this, please know it has been
solved, albeit not through using any Microsoft product.

As it turns out, the Access 2000 database is in turn populated through a
third-party product (Kronos). There is a setting in Kronos that prevents
the
duplicates from occurring so we won't have to eliminate them in the Excel
workbook.

Thanks to anyone who spent time on this.

Trisha

"Trisha B." wrote:

Julie,

They'd get the total, but not correct recordset values. The hours for
A* or
K should be 0.00 if B* and C* or L* respectively exist, so the totals
would
be correct, but the detail data would not. (This is a case where the
report
is more than the sum, or difference, of its parts.)

Financial Services needs the correct detail data, and the A* and K hours
pay
coding is what's incorrect, and they want it corrected. But I do
appreciate
your thoughts (and reading through the description of the problem!). If
they'd let me work on the actual database, I'd have a better idea of
what I'm
doing, but I use VBA in Excel about four times a year, which is not
enough to
be proficient.

Thanks for responding,
Trisha

"JulieD" wrote:

Hi Trisha

i might be missing the point here, but if you added up the total hours
for
the employee and then deducted the total of B*, C*, L* won't you get
the
answer you want?

Cheers
JulieD

"Trisha B." wrote in message
...
We have a series of reports generated by an Access 2000 database in
our
Payroll department which are sent to our Financial Services
department.
The
report consists of several columns (only three of which concern me)
and
10,000+ rows.

The columns a Column A the employee ID, Column B the pay code,
and
Column C the number of hours worked at that pay code (there are no
column
headings on the report).

The rows contain every payroll code for every employee for a given
two-week
pay period, and the number of hours worked at that code (e.g., "A"
is
regular
day shift, "AE" is regular evening shift, "BE" is time and a half
evening
shift, and it goes on).

The problem discovered yesterday is that overtime hours are being
reported
twice, once as code AE and again as code BE. This also occurs with
code
CE
(double time evening shift) as well as with K's (Inservice Days) and
L's
(Inservice Overtime days).

The original developer of the database is not available and, for
reasons I
won't go into here, the preference is that I adjust the report, not
the
database. Hence my request for your help.

A very small representation of the worksheet would be very similar
to this
(forgive the spacing, I know it never posts as written):

A B C
1 1122 AE 8.00
2 1122 AE 1.00
3 1122 BE 1.00
4 1122 JD 8.00
5 1234 AE 8.00
6 1234 CE 8.00
7 1234 AF 24.00
8 1234 SL 8.00
9 1234 SLE 8.00

What has been requested of me is:

1. To write code that will loop through each employee's recordset
for the
given pay period,

2. Find any instances where the value of Column B = any B* (*
indicating a
wildcard) or C*and, if found,

3. Also find the corresponding A* value whose hours worked (Column
C) is
equal to the hours worked for the B* or C* value and convert the
hours
that
relate to the matching A* to 0.00 then

4. Repeat the process if there is a K and L (K becomes the A, L the
B*,
C*).

In the example above, in row 3, column B has a value of BE so they
want me
to automate finding an A* pay code in the first four lines (as
define the
employee ID) and also find the matching value in column C (so it
would be
line 2, column B) and convert line 2 column C from 1.00 to 0.00.

After it does that, it would then go on to the next employee and
repeat
the
process. Employees have different numbers of rows, some as few as
three,
others in excess of 10, for each pay period with no breaks between
rows.

We have approximately 10 old worksheets for this to be applied to
and all
future worksheets will need the VBA code run against them.

Any assistance with this would be greatly appreciated.

Many thanks in advance