RE: Multiple column comparisons

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,
--
Gary Brown
gary_brown@xxxxxxxxxxxxx
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 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>
> > --
> > Gary Brown
> > gary_brown@xxxxxxxxxxxxx
> > 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 spread*** 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?
.

Quantcast