Compare data in 2 columns for unique values
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 |
Compare data in 2 columns for unique values
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 he 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 wrote: 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 |
Compare data in 2 columns for unique values
Thanks Dave!
"Dave Peterson" wrote: 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 he 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 wrote: 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 |
All times are GMT +1. The time now is 08:17 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com