A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Discussion (Misc queries)
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Compare data in 2 columns for unique values



 
 
Thread Tools Display Modes
  #1  
Old November 21st 07, 04:28 PM posted to microsoft.public.excel.misc
Steve C[_2_]
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
Ads
  #2  
Old November 21st 07, 06:43 PM posted to microsoft.public.excel.misc
Dave Peterson
external usenet poster
 
Posts: 35,220
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  
Old November 29th 07, 04:26 PM posted to microsoft.public.excel.misc
Steve C
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
>

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

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 04:12 PM
Compare different columns and put unique data in the next available column [email protected] Excel Discussion (Misc queries) 0 March 13th 07 03:22 PM
Compare 2 columns and put unique data in column 3 [email protected] Excel Discussion (Misc queries) 2 March 7th 07 02: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 10:49 PM


All times are GMT +1. The time now is 06:11 AM.


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