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

 Salary Surveys Property & Casualty, Life, Health & Pension Health Actuary JobsInsurance & Consulting jobs for Students, Associates & Fellows Actuarial Recruitment Visit DW Simpson's website for more info. www.dwsimpson.com/about Casualty JobsProperty & Casualty jobs for Students, Associates & Fellows

#11
02-05-2019, 05: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: 11,450

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
__________________
Pronouns: yore/you're
#12
02-05-2019, 05:52 PM
 llcooljabe Member CAS Join Date: Aug 2002 Posts: 23,271

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
#13
02-06-2019, 11:12 AM
 Cavaliere Member SOA Join Date: May 2016 Posts: 304

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!
#14
02-06-2019, 12: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: 11,450

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
__________________
Pronouns: yore/you're
#15
02-06-2019, 12:32 PM
 Childish Gambino Member SOA Join Date: Jul 2014 Posts: 29,211

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."
#16
02-06-2019, 01:49 PM
 Knoath Member CAS Join Date: Oct 2015 Posts: 82

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

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)}
#18
02-06-2019, 04:51 PM
 Colymbosathon ecplecticos Member Join Date: Dec 2003 Posts: 6,068

Quote:
 Originally Posted by Knoath 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)}
This assumes that column B entries are single characters.
__________________
"What do you mean I don't have the prerequisites for this class? I've failed it twice before!"

"I think that probably clarifies things pretty good by itself."

"I understand health care now especially very well."
#19
02-06-2019, 04:56 PM
 yoyo Member CAS Join Date: Dec 2001 Posts: 23,221

#20
02-06-2019, 05:57 PM
 Knoath Member CAS Join Date: Oct 2015 Posts: 82

Quote:
 Originally Posted by Colymbosathon ecplecticos This assumes that column B entries are single characters.
True. This would be much easier using SQL where you can use max or min on alphanumerics.