Actuarial Outpost New Excel Question
 Register Blogs Wiki FAQ Calendar Search Today's Posts Mark Forums Read
 FlashChat Actuarial Discussion Preliminary Exams CAS/SOA Exams Cyberchat Around the World Suggestions

#1
02-05-2019, 01:33 PM
 Cavaliere Member SOA Join Date: May 2016 Posts: 307
New Excel Question

I'd first like to thank the people who helped me in my last post.

Here's my new issue:

I'm trying to search a bunch of accounts in column A, where there are plenty of duplicates, and find the ones where there are different values for column B.

Example:

Column A_____Column B
1____________a
1____________a
2____________c
2____________c
3____________d
3____________e
1____________a
1____________b

I would want to return number 1 and number 3 ( or have 1 and 3 be TRUE, and 2 be FALSE) because their second column values aren't all the same.

#2
02-05-2019, 01:48 PM
 Patience Member SOA AAA Join Date: Sep 2001 Location: a kinder, gentler place Favorite beer: Scotch Posts: 48,839

Quote:
 Originally Posted by Cavaliere I'd first like to thank the people who helped me in my last post. Here's my new issue: I'm trying to search a bunch of accounts in column A, where there are plenty of duplicates, and find the ones where there are different values for column B. Example: Column A_____Column B 1____________a 1____________a 2____________c 2____________c 3____________d 3____________e 1____________a 1____________b I would want to return number 1 and number 3 ( or have 1 and 3 be TRUE, and 2 be FALSE) because their second column values aren't all the same. Thank you in advance!
why is 1 true? a = a
__________________
"I've been through the desert on a horse with no name...
In the desert you can remember your name
'Cause there ain't no one for to give you no pain"
#3
02-05-2019, 01:50 PM
 Patience Member SOA AAA Join Date: Sep 2001 Location: a kinder, gentler place Favorite beer: Scotch Posts: 48,839

assuming you meant 3 & 4
4 being the repeated 1

are you sorted column A; Column B

if(A2=A1,if(B2=B1,"",A2),"")
__________________
"I've been through the desert on a horse with no name...
In the desert you can remember your name
'Cause there ain't no one for to give you no pain"
#4
02-05-2019, 01:53 PM
 Gandalf Site Supporter Site Supporter SOA Join Date: Nov 2001 Location: Middle Earth Posts: 31,225

Quote:
 Originally Posted by Patience why is 1 true? a = a
I suspect his intent is that 1 is not true, since although 1 has some duplicates in column B (more than one "a"), not all values in column B for 1 are the same (both "a" and "b")
#5
02-05-2019, 01:54 PM
 Childish Gambino Member SOA Join Date: Jul 2014 Posts: 29,595

A bit convoluted but I think it works (L8 is where the 1 is in my table below)

=COUNTIFS(\$A\$1:\$A\$8,L8,\$B\$1:\$B\$8,VLOOKUP(L8,\$A\$1:\$ B\$8,2,FALSE))=COUNTIF(\$A\$1:\$A\$8,L8)

1 FALSE
2 TRUE
3 FALSE
__________________
which side are you on
#6
02-05-2019, 02:15 PM
 llcooljabe Member CAS Join Date: Aug 2002 Posts: 23,289

Copy to another location: a1:b8 (using your fictitious data above)
Criteria range:
Copy to: some cell, e.g. c1

check off "unique records only"
__________________
www.GoodNewsNow.info
Propoganda
#7
02-05-2019, 02:37 PM
 BruteForce Member SOA AAA Join Date: Apr 2013 Studying for More Money Favorite beer: Wurzel Bier Posts: 11,432

Use the Remove Duplicates feature on the Data tab. You could write a macro to do that as well.
__________________
ASA

Quote:
 Originally Posted by Actuary321 I would really hate to bring Pokémon to a gun fight.
#8
02-05-2019, 02:48 PM
 Fat Ninja Member SOA AAA Join Date: Jun 2011 Studying for FSA Posts: 204

Quote:
 Originally Posted by BruteForce Use the Remove Duplicates feature on the Data tab. You could write a macro to do that as well.
Alt-A-M

I'm pretty terrible at excel shortcuts, but I use this so much I decided to learn it.
#9
02-05-2019, 03:47 PM
 Cavaliere Member SOA Join Date: May 2016 Posts: 307

Quote:
 Originally Posted by Gandalf I suspect his intent is that 1 is not true, since although 1 has some duplicates in column B (more than one "a"), not all values in column B for 1 are the same (both "a" and "b")
Correct if just one value of column b is different from the rest, i want to know what number it was.
#10
02-05-2019, 03:52 PM
 Cavaliere Member SOA Join Date: May 2016 Posts: 307

Quote:
 Originally Posted by BruteForce Use the Remove Duplicates feature on the Data tab. You could write a macro to do that as well.
Not trying to remove duplicates. Trying to spot when given a value in A, if all the values in B are the same. If they are, i leave them alone. If just one value is off, i want to know the number.

As for the people who submitted formulas, i'll try them later and see if they work.