Remember Me?

#1
November 21st 07, 05:28 PM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Nov 2007 Posts: 4
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

#2
November 21st 07, 07:43 PM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Jul 2006 Posts: 35,218
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
#3
November 29th 07, 05:26 PM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Sep 2006 Posts: 119
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

 Thread Tools Search this Thread Search this Thread: Advanced Search Display Modes Linear Mode

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post [email protected] Excel Discussion (Misc queries) 0 March 13th 07 05:12 PM [email protected] Excel Discussion (Misc queries) 0 March 13th 07 04:22 PM [email protected] Excel Discussion (Misc queries) 2 March 7th 07 03:54 PM sa02000 Excel Worksheet Functions 1 June 12th 06 09:42 PM kuansheng Excel Worksheet Functions 15 February 1st 06 11:49 PM

All times are GMT +1. The time now is 07:26 PM.