Compare data in 2 columns for unique values

[Guest]

Is there any way to identify if there are multiple Sal Grades in Col B for
the Job Titles in Col A below? Perhaps a true false statement in a separate
column if the Sal Grade doesn't match the one above it.

Col A Col B
Job Title Sal Grade
Clerk A3
Clerk A1
Cook A2
Cook A2
Nurse B5
Nurse B4

 

[Dave Peterson]

You could use a formula like this in C2 to get the count of how many rows look
like the row 2:
=SUMPRODUCT(--($A$2:$A$99=A2),--($B$2:$B$99=B2))

If you really wanted true/false, you could use:
=(SUMPRODUCT(--($A$2:$A$99=A2),--($B$2:$B$99=B2)))>1

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

 

[Steve C]

Thanks Dave!

You could use a formula like this in C2 to get the count of how many rows look
like the row 2:
=SUMPRODUCT(--($A$2:$A$99=A2),--($B$2:$B$99=B2))

If you really wanted true/false, you could use:
=(SUMPRODUCT(--($A$2:$A$99=A2),--($B$2:$B$99=B2)))>1

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html