Actuarial Outpost
 
Go Back   Actuarial Outpost > Actuarial Discussion Forum > Software & Technology
FlashChat Actuarial Discussion Preliminary Exams CAS/SOA Exams Cyberchat Around the World Suggestions

2017 ACTUARIAL SALARY SURVEYS
Contact DW Simpson for a Personalized Salary Survey

Reply
 
Thread Tools Search this Thread Display Modes
  #1  
Old 02-05-2019, 02:33 PM
Cavaliere Cavaliere is offline
Member
SOA
 
Join Date: May 2016
Posts: 295
Default 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.

Thank you in advance!
Reply With Quote
  #2  
Old 02-05-2019, 02:48 PM
Patience's Avatar
Patience Patience is offline
Member
SOA AAA
 
Join Date: Sep 2001
Location: a kinder, gentler place
Favorite beer: Scotch
Posts: 48,541
Default

Quote:
Originally Posted by Cavaliere View Post
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"
Reply With Quote
  #3  
Old 02-05-2019, 02:50 PM
Patience's Avatar
Patience Patience is offline
Member
SOA AAA
 
Join Date: Sep 2001
Location: a kinder, gentler place
Favorite beer: Scotch
Posts: 48,541
Default

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"
Reply With Quote
  #4  
Old 02-05-2019, 02:53 PM
Gandalf's Avatar
Gandalf Gandalf is offline
Site Supporter
Site Supporter
SOA
 
Join Date: Nov 2001
Location: Middle Earth
Posts: 31,073
Default

Quote:
Originally Posted by Patience View Post
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")
Reply With Quote
  #5  
Old 02-05-2019, 02:54 PM
Childish Gambino's Avatar
Childish Gambino Childish Gambino is online now
Member
SOA
 
Join Date: Jul 2014
Posts: 27,116
Default

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
__________________
the president replied, "I don't care. I believe Putin."
Reply With Quote
  #6  
Old 02-05-2019, 03:15 PM
llcooljabe's Avatar
llcooljabe llcooljabe is online now
Member
CAS
 
Join Date: Aug 2002
Posts: 23,082
Default

Data/sort & Filter/Advanced/

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
Reply With Quote
  #7  
Old 02-05-2019, 03:37 PM
BruteForce's Avatar
BruteForce BruteForce is offline
Member
SOA AAA
 
Join Date: Apr 2013
Studying for More Money
Favorite beer: Wurzel Bier
Posts: 11,292
Default

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 View Post
I would really hate to bring Pokémon to a gun fight.
Reply With Quote
  #8  
Old 02-05-2019, 03:48 PM
Fat Ninja's Avatar
Fat Ninja Fat Ninja is offline
Member
SOA AAA
 
Join Date: Jun 2011
Studying for FSA
Posts: 173
Default

Quote:
Originally Posted by BruteForce View Post
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.
Reply With Quote
  #9  
Old 02-05-2019, 04:46 PM
phpBB's Avatar
phpBB phpBB is offline
Member
 
Join Date: Sep 2001
Posts: 83
Default

C1: =A1&B1
D1: =(COUNTIF($A:$A,A1)-COUNTIF($C:$C,C1)) > 0
Reply With Quote
  #10  
Old 02-05-2019, 04:47 PM
Cavaliere Cavaliere is offline
Member
SOA
 
Join Date: May 2016
Posts: 295
Default

Quote:
Originally Posted by Gandalf View Post
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.
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
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

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off


All times are GMT -4. The time now is 12:20 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
*PLEASE NOTE: Posts are not checked for accuracy, and do not
represent the views of the Actuarial Outpost or its sponsors.
Page generated in 0.14729 seconds with 9 queries