hi. I have a spreadsheet with names, ID#s for those names, and direct
deposit info. For each person, they have multiple rows. One column
indicates A or B, which tells if the amount going into an account is a part
of the whole, or the balance. Another column indicates the priority of said
amount.

In order for payroll to run correctly, all "B" amounts MUST have a greater
priority number than all "A" accounts, as when it runs, if it first sees a B
amount, it just takes the whole balance, sweeps it into the account, and
doesn't leave anything for any of the "A"s

I'm at a loss for how to return the values of people with B balances with a
priority lower than their A balances. Any ideas in Excel or Access?

You using SOLOMON?
Anyway, sort by ID # (Ascending) and then Priority (Ascending) so that the
Employee information stays contiguous and yet the 'A' will always precede the
'B'. This can easily be done using DataSort in Excel or using a query in
Access.
Good Luck form a Payroll specialist for 35 years (God! did I really say 35
years!!!???) :O
--
Gary Brown

If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.


"J. Catz." wrote:

hi. I have a spreadsheet with names, ID#s for those names, and direct
deposit info. For each person, they have multiple rows. One column
indicates A or B, which tells if the amount going into an account is a part
of the whole, or the balance. Another column indicates the priority of said
amount.

In order for payroll to run correctly, all "B" amounts MUST have a greater
priority number than all "A" accounts, as when it runs, if it first sees a B
amount, it just takes the whole balance, sweeps it into the account, and
doesn't leave anything for any of the "A"s

I'm at a loss for how to return the values of people with B balances with a
priority lower than their A balances. Any ideas in Excel or Access?

Gary - PeopleSoft, actually.

Thanks for the advice, but I'm actually auditing PPS to show errors - just
sorting won't fix the problem, as I'm trying to find the people who have
improper coding so we can go back and change their priority numbers for
them....

If I just use a sort, I have to manually look through 10,000 records and
visually find the ones where the B values are less than the As.....

Any advice?

"Gary L Brown" wrote:

You using SOLOMON?
Anyway, sort by ID # (Ascending) and then Priority (Ascending) so that the
Employee information stays contiguous and yet the 'A' will always precede the
'B'. This can easily be done using DataSort in Excel or using a query in
Access.
Good Luck form a Payroll specialist for 35 years (God! did I really say 35
years!!!???) :O
--
Gary Brown

If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.


"J. Catz." wrote:

hi. I have a spreadsheet with names, ID#s for those names, and direct
deposit info. For each person, they have multiple rows. One column
indicates A or B, which tells if the amount going into an account is a part
of the whole, or the balance. Another column indicates the priority of said
amount.

In order for payroll to run correctly, all "B" amounts MUST have a greater
priority number than all "A" accounts, as when it runs, if it first sees a B
amount, it just takes the whole balance, sweeps it into the account, and
doesn't leave anything for any of the "A"s

I'm at a loss for how to return the values of people with B balances with a
priority lower than their A balances. Any ideas in Excel or Access?

Always have advice :O)

Sort by ID # (Ascending) and then Priority (Ascending)
so that the Employee information stays contiguous and
yet the 'A' will always precede the 'B'. This can
easily be done using DataSort.

Assumptions for illustration purposes:

A B C D
1 id# type priority test
2 54 A 1
3 54 B 2
4 2911 A 2 Review
5 2911 B 1 Review
6 4139 A 2 Review
7 4139 B 1 Review
8 4298 A 1
9 4298 B 2
10 4442 A 2 Review
11 4442 B 1 Review
12 5075 A 1
13 5075 B 2

Create the following formula in Column D, copy it down
your list, then filter on Column D.

=IF(OR(AND(A2=A1,C2<=C1),AND(A2=A3,C2=C3)),"Revie w","")


After creating the formulas and letting them calculate, you might want
to PasteSpecialValue Column D to save time and memory.
Especially if you intend to re-sort the data later!

HTH,
--
Gary Brown

If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.


"J. Catz." wrote:

Gary - PeopleSoft, actually.

Thanks for the advice, but I'm actually auditing PPS to show errors - just
sorting won't fix the problem, as I'm trying to find the people who have
improper coding so we can go back and change their priority numbers for
them....

If I just use a sort, I have to manually look through 10,000 records and
visually find the ones where the B values are less than the As.....

Any advice?

"Gary L Brown" wrote:

You using SOLOMON?
Anyway, sort by ID # (Ascending) and then Priority (Ascending) so that the
Employee information stays contiguous and yet the 'A' will always precede the
'B'. This can easily be done using DataSort in Excel or using a query in
Access.
Good Luck form a Payroll specialist for 35 years (God! did I really say 35
years!!!???) :O
--
Gary Brown

If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.


"J. Catz." wrote:

hi. I have a spreadsheet with names, ID#s for those names, and direct
deposit info. For each person, they have multiple rows. One column
indicates A or B, which tells if the amount going into an account is a part
of the whole, or the balance. Another column indicates the priority of said
amount.

In order for payroll to run correctly, all "B" amounts MUST have a greater
priority number than all "A" accounts, as when it runs, if it first sees a B
amount, it just takes the whole balance, sweeps it into the account, and
doesn't leave anything for any of the "A"s

I'm at a loss for how to return the values of people with B balances with a
priority lower than their A balances. Any ideas in Excel or Access?

Ok, great idea, BUT (sorry), people can have more than one A value (more than
one account they're sending a percentage or amount into each pay), or no A
value at all....so that makes it much more complicated. Apologies, I should
have been more clear in my original post; any other ideas?

It looks like this... You'll note all these people are ok, but again, I
have 10,000 total rows. Fun!

ID Priority Type
271258 1.00 A
271258 999.00 B
271266 999.00 B
271267 0.00 A
271267 999.00 B
271270 999.00 B
271271 100.00 A
271271 999.00 B
271272 999.00 B
271273 1.00 A
271273 999.00 B
271310 100.00 A
271310 999.00 B
271320 999.00 B
271321 0.00 A
271321 3.00 A
271321 4.00 A
271321 999.00 B
271323 999.00 B
271326 999.00 B


"Gary L Brown" wrote:

Always have advice :O)

Sort by ID # (Ascending) and then Priority (Ascending)
so that the Employee information stays contiguous and
yet the 'A' will always precede the 'B'. This can
easily be done using DataSort.

Assumptions for illustration purposes:

A B C D
1 id# type priority test
2 54 A 1
3 54 B 2
4 2911 A 2 Review
5 2911 B 1 Review
6 4139 A 2 Review
7 4139 B 1 Review
8 4298 A 1
9 4298 B 2
10 4442 A 2 Review
11 4442 B 1 Review
12 5075 A 1
13 5075 B 2

Create the following formula in Column D, copy it down
your list, then filter on Column D.

=IF(OR(AND(A2=A1,C2<=C1),AND(A2=A3,C2=C3)),"Revie w","")


After creating the formulas and letting them calculate, you might want
to PasteSpecialValue Column D to save time and memory.
Especially if you intend to re-sort the data later!

HTH,
--
Gary Brown

If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.


"J. Catz." wrote:

Gary - PeopleSoft, actually.

Thanks for the advice, but I'm actually auditing PPS to show errors - just
sorting won't fix the problem, as I'm trying to find the people who have
improper coding so we can go back and change their priority numbers for
them....

If I just use a sort, I have to manually look through 10,000 records and
visually find the ones where the B values are less than the As.....

Any advice?

"Gary L Brown" wrote:

You using SOLOMON?
Anyway, sort by ID # (Ascending) and then Priority (Ascending) so that the
Employee information stays contiguous and yet the 'A' will always precede the
'B'. This can easily be done using DataSort in Excel or using a query in
Access.
Good Luck form a Payroll specialist for 35 years (God! did I really say 35
years!!!???) :O
--
Gary Brown

If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.


"J. Catz." wrote:

hi. I have a spreadsheet with names, ID#s for those names, and direct
deposit info. For each person, they have multiple rows. One column
indicates A or B, which tells if the amount going into an account is a part
of the whole, or the balance. Another column indicates the priority of said
amount.

In order for payroll to run correctly, all "B" amounts MUST have a greater
priority number than all "A" accounts, as when it runs, if it first sees a B
amount, it just takes the whole balance, sweeps it into the account, and
doesn't leave anything for any of the "A"s

I'm at a loss for how to return the values of people with B balances with a
priority lower than their A balances. Any ideas in Excel or Access?

A B C D
1 ID Priority Type Test
2 271258 1.00 A
3 271258 999.00 B
4 271266 999.00 B
5 271267 0.00 A
6 271267 6.00 A Review
7 271267 5.00 B Review
8 271267 999.00 B

The formula...
=IF(OR(AND(A2=A1,B2<B1),AND(A2=A3,B2B3)),"Review" ,"")

Will take care of multiples as shown in ID 27167.
1) Sort on ID then Type then Priority.
2) Apply this formula
3) Set filter
4) Filter on D

HTH,
--
Gary Brown

If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.


"J. Catz." wrote:

Ok, great idea, BUT (sorry), people can have more than one A value (more than
one account they're sending a percentage or amount into each pay), or no A
value at all....so that makes it much more complicated. Apologies, I should
have been more clear in my original post; any other ideas?

It looks like this... You'll note all these people are ok, but again, I
have 10,000 total rows. Fun!

ID Priority Type
271258 1.00 A
271258 999.00 B
271266 999.00 B
271267 0.00 A
271267 999.00 B
271270 999.00 B
271271 100.00 A
271271 999.00 B
271272 999.00 B
271273 1.00 A
271273 999.00 B
271310 100.00 A
271310 999.00 B
271320 999.00 B
271321 0.00 A
271321 3.00 A
271321 4.00 A
271321 999.00 B
271323 999.00 B
271326 999.00 B


"Gary L Brown" wrote:

Always have advice :O)

Sort by ID # (Ascending) and then Priority (Ascending)
so that the Employee information stays contiguous and
yet the 'A' will always precede the 'B'. This can
easily be done using DataSort.

Assumptions for illustration purposes:

A B C D
1 id# type priority test
2 54 A 1
3 54 B 2
4 2911 A 2 Review
5 2911 B 1 Review
6 4139 A 2 Review
7 4139 B 1 Review
8 4298 A 1
9 4298 B 2
10 4442 A 2 Review
11 4442 B 1 Review
12 5075 A 1
13 5075 B 2

Create the following formula in Column D, copy it down
your list, then filter on Column D.

=IF(OR(AND(A2=A1,C2<=C1),AND(A2=A3,C2=C3)),"Revie w","")


After creating the formulas and letting them calculate, you might want
to PasteSpecialValue Column D to save time and memory.
Especially if you intend to re-sort the data later!

HTH,
--
Gary Brown

If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.


"J. Catz." wrote:

Gary - PeopleSoft, actually.

Thanks for the advice, but I'm actually auditing PPS to show errors - just
sorting won't fix the problem, as I'm trying to find the people who have
improper coding so we can go back and change their priority numbers for
them....

If I just use a sort, I have to manually look through 10,000 records and
visually find the ones where the B values are less than the As.....

Any advice?

"Gary L Brown" wrote:

You using SOLOMON?
Anyway, sort by ID # (Ascending) and then Priority (Ascending) so that the
Employee information stays contiguous and yet the 'A' will always precede the
'B'. This can easily be done using DataSort in Excel or using a query in
Access.
Good Luck form a Payroll specialist for 35 years (God! did I really say 35
years!!!???) :O
--
Gary Brown

If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.


"J. Catz." wrote:

hi. I have a spreadsheet with names, ID#s for those names, and direct
deposit info. For each person, they have multiple rows. One column
indicates A or B, which tells if the amount going into an account is a part
of the whole, or the balance. Another column indicates the priority of said
amount.

In order for payroll to run correctly, all "B" amounts MUST have a greater
priority number than all "A" accounts, as when it runs, if it first sees a B
amount, it just takes the whole balance, sweeps it into the account, and
doesn't leave anything for any of the "A"s

I'm at a loss for how to return the values of people with B balances with a
priority lower than their A balances. Any ideas in Excel or Access?