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.

 Compare data in 2 columns for unique values
 Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

## Compare data in 2 columns for unique values

#1
November 21st 07, 04:28 PM posted to microsoft.public.excel.misc
 Steve C[_2_] external usenet poster 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
Clerk A3
Clerk A1
Cook A2
Cook A2
Nurse B5
Nurse B4
#2
November 21st 07, 06:43 PM posted to microsoft.public.excel.misc
 Dave Peterson external usenet poster Posts: 35,220
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
> Clerk A3
> Clerk A1
> Cook A2
> Cook A2
> Nurse B5
> Nurse B4

--

Dave Peterson
#3
November 29th 07, 04:26 PM posted to microsoft.public.excel.misc
 Steve C external usenet poster 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 Display Modes Linear Mode

 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 User Control Panel Private Messages Subscriptions Who's Online Search Forums Forums Home Excel Newsgroups     Excel Discussion (Misc queries)     Setting up and Configuration of Excel     New Users to Excel     Excel Worksheet Functions     Links and Linking in Excel     Charts and Charting in Excel     Excel Programming About ExcelBanter     About this forum

 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 01:18 AM.