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

DW Simpson Global Actuarial & Analytics Recruitment
Download our 2017 Actuarial Salary Survey
now with state-by-state salary information!


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

Thanks for all your input
Reply With Quote
  #12  
Old 02-05-2019, 06:33 PM
mathmajor's Avatar
mathmajor mathmajor is offline
Member
SOA AAA
 
Join Date: Dec 2010
Location: Nowhere in particular
Studying for Japanese
College: B.S. Applied Math
Favorite beer: La Croix Grapefruit
Posts: 10,059
Default

yall are lazy

put this in C1
=IF(SUMPRODUCT(($A$1:$A$8=A1)*($B$1:$B$8=B1))=1,FA LSE,TRUE)
drag down, adjust ranges if needed
__________________
3.14159 26535 89793 23846 26433 83279 50288 41971 69399 37510 58209 74944 59230 78164 06286 20899 86280 34825 34211 70679 82148 08651 32823 06647 09384 46095 50582 23172 53594 08128 48111 74502 84102 70193 | THIS IS CNN | 85211 05559 64462 29489 54930 38196 44288 10975 66593 34461 28475 64823 37867 83165 27120 19091 45648 56692 34603 48610 45432 66482 13393 60726 02491 41273 72458 70066 06315 58817 48815 20920 96282 92540
Reply With Quote
  #13  
Old 02-05-2019, 06:52 PM
llcooljabe's Avatar
llcooljabe llcooljabe is offline
Member
CAS
 
Join Date: Aug 2002
Posts: 23,082
Default

Quote:
Originally Posted by mathmajor View Post
yall are lazy

put this in C1
=IF(SUMPRODUCT(($A$1:$A$8=A1)*($B$1:$B$8=B1))=1,FA LSE,TRUE)
drag down, adjust ranges if needed
won't a countifs work just as well but easier to understand?

Countifs($a$1:$a$8,a1,$b$1:$b$8,1), and then filter on the 1's.

alternatively take your formula out of the if, and also filter on the 1's
__________________
www.GoodNewsNow.info
Propoganda
Reply With Quote
  #14  
Old 02-06-2019, 09:07 AM
phpBB's Avatar
phpBB phpBB is offline
Member
 
Join Date: Sep 2001
Posts: 83
Default

Quote:
Originally Posted by mathmajor View Post
yall are lazy

put this in C1
=IF(SUMPRODUCT(($A$1:$A$8=A1)*($B$1:$B$8=B1))=1,FA LSE,TRUE)
drag down, adjust ranges if needed
Did you verify that your solution works before calling other people lazy?
Reply With Quote
  #15  
Old 02-06-2019, 12:12 PM
Cavaliere Cavaliere is offline
Member
SOA
 
Join Date: May 2016
Posts: 295
Default

Quote:
Originally Posted by phpBB View Post
C1: =A1&B1
D1: =(COUNTIF($A:$A,A1)-COUNTIF($C:$C,C1)) > 0
This seems to be working so far. Thank you!
Reply With Quote
  #16  
Old 02-06-2019, 01:25 PM
mathmajor's Avatar
mathmajor mathmajor is offline
Member
SOA AAA
 
Join Date: Dec 2010
Location: Nowhere in particular
Studying for Japanese
College: B.S. Applied Math
Favorite beer: La Croix Grapefruit
Posts: 10,059
Default

Quote:
Originally Posted by phpBB View Post
Did you verify that your solution works before calling other people lazy?
yes, mom

I thought s/he wanted a one-formula method.

and I was calling the remove duplicates/sort thing lazy lol
__________________
3.14159 26535 89793 23846 26433 83279 50288 41971 69399 37510 58209 74944 59230 78164 06286 20899 86280 34825 34211 70679 82148 08651 32823 06647 09384 46095 50582 23172 53594 08128 48111 74502 84102 70193 | THIS IS CNN | 85211 05559 64462 29489 54930 38196 44288 10975 66593 34461 28475 64823 37867 83165 27120 19091 45648 56692 34603 48610 45432 66482 13393 60726 02491 41273 72458 70066 06315 58817 48815 20920 96282 92540
Reply With Quote
  #17  
Old 02-06-2019, 01:32 PM
Childish Gambino's Avatar
Childish Gambino Childish Gambino is offline
Member
SOA
 
Join Date: Jul 2014
Posts: 27,111
Default

Quote:
Originally Posted by mathmajor View Post
yes, mom

I thought s/he wanted a one-formula method.

and I was calling the remove duplicates/sort thing lazy lol
I don't know if it's lazy but I don't think it's a good idea to go on with partial information with advice like

Step 1) Destroy the data
__________________
the president replied, "I don't care. I believe Putin."
Reply With Quote
  #18  
Old 02-06-2019, 01:43 PM
phpBB's Avatar
phpBB phpBB is offline
Member
 
Join Date: Sep 2001
Posts: 83
Default

Quote:
Originally Posted by mathmajor View Post
yes, mom
You should have checked twice.

Quote:
I thought s/he wanted a one-formula method.
Probably prefers a one-formula method that works.
Reply With Quote
  #19  
Old 02-06-2019, 02:49 PM
Knoath Knoath is offline
Member
CAS
 
Join Date: Oct 2015
Posts: 63
Default

Use an array. If the minimum B column value for a particular A column value is different to the maximum, then you have more than one distinct value. Copy this formula down column C (using Ctl + Shift + Enter, when you enter it in C1) -
{=IF(MIN(IF($A$1:$A$8=A1,$B$1:$B$8))<>MAX(IF($A$1: $A$8=A1,$B$1:$B$8)),1,0)}
Reply With Quote
  #20  
Old 02-06-2019, 04:06 PM
Knoath Knoath is offline
Member
CAS
 
Join Date: Oct 2015
Posts: 63
Default

Quote:
Originally Posted by phpBB View Post
Is your formula correct? I get all 0's with that.
Yes, you're right - it doesn't work with alphas. If you convert the alphas to their ascii code you can do it -

{=IF(MIN(IF($A$1:$A$8=A1,CODE($B$1:$B$8)))<>MAX(IF ($A$1:$A$8=A1,CODE($B$1:$B$8))),1,0)}
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 07:50 AM.


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.46334 seconds with 9 queries