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

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

#11
02-05-2019, 04:52 PM
 Cavaliere Member SOA Join Date: May 2016 Posts: 295

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.

#12
02-05-2019, 06:33 PM
 mathmajor 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

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
#13
02-05-2019, 06:52 PM
 llcooljabe Member CAS Join Date: Aug 2002 Posts: 23,082

Quote:
 Originally Posted by mathmajor 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
#14
02-06-2019, 09:07 AM
 phpBB Member Join Date: Sep 2001 Posts: 83

Quote:
 Originally Posted by mathmajor 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?
#15
02-06-2019, 12:12 PM
 Cavaliere Member SOA Join Date: May 2016 Posts: 295

Quote:
 Originally Posted by phpBB C1: =A1&B1 D1: =(COUNTIF(\$A:\$A,A1)-COUNTIF(\$C:\$C,C1)) > 0
This seems to be working so far. Thank you!
#16
02-06-2019, 01:25 PM
 mathmajor 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

Quote:
 Originally Posted by phpBB 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
#17
02-06-2019, 01:32 PM
 Childish Gambino Member SOA Join Date: Jul 2014 Posts: 27,111

Quote:
 Originally Posted by mathmajor 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."
#18
02-06-2019, 01:43 PM
 phpBB Member Join Date: Sep 2001 Posts: 83

Quote:
 Originally Posted by mathmajor 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.
#19
02-06-2019, 02:49 PM
 Knoath Member CAS Join Date: Oct 2015 Posts: 63

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)}
#20
02-06-2019, 04:06 PM
 Knoath Member CAS Join Date: Oct 2015 Posts: 63

Quote:
 Originally Posted by phpBB 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)}