Multiple column comparisons

J

J. Catz.

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

Gary L Brown

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 Data>Sort 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>
 
J

J. Catz.

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

Gary L Brown

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 Data>Sort.

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)),"Review","")


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

HTH,
 
J

J. Catz.

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
 
G

Gary L Brown

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,B2>B3)),"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,
 
J

J. Catz.

Sorry, Gary, thanks. I didn't think it through that it would comare two at a
time; I didn't need to group by ID number. This worked great. Thanks!
 
Top