Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 4
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 119
Default 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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Compare several columns of data and display unique ones [email protected] Excel Discussion (Misc queries) 0 March 13th 07 05:12 PM
Compare different columns and put unique data in the next available column [email protected] Excel Discussion (Misc queries) 0 March 13th 07 04:22 PM
Compare 2 columns and put unique data in column 3 [email protected] Excel Discussion (Misc queries) 2 March 7th 07 03:54 PM
Compare and copy unique values sa02000 Excel Worksheet Functions 1 June 12th 06 09:42 PM
Compare multiple column of data and list out common and unique component in adj columns kuansheng Excel Worksheet Functions 15 February 1st 06 11:49 PM


All times are GMT +1. The time now is 09:30 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"