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

Upload your resume securely at
to be contacted when our jobs meet your skills and objectives.

Thread Tools Search this Thread Display Modes
Old 02-06-2019, 05:51 PM
Colymbosathon ecplecticos's Avatar
Colymbosathon ecplecticos Colymbosathon ecplecticos is offline
Join Date: Dec 2003
Posts: 6,020

Originally Posted by Knoath View Post
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."
Reply With Quote
Old 02-06-2019, 05:56 PM
yoyo's Avatar
yoyo yoyo is offline
Join Date: Dec 2001
Posts: 22,703

Reply With Quote
Old 02-06-2019, 06:57 PM
Knoath Knoath is offline
Join Date: Oct 2015
Posts: 63

Originally Posted by Colymbosathon ecplecticos View Post
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.
Reply With Quote
Old 02-11-2019, 11:44 PM
rgreenlee's Avatar
rgreenlee rgreenlee is offline
Join Date: Jun 2014
Location: in a van down by the river
Posts: 1,054

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.


Column A_____Column 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!
If you load your dataset into SQL table called "MyDataSet" this becomes simple:

A.AccountNumber,A.ColumnB as ColumnB1,ISNULL(B.ColumnB,'') as ColumnB2
MyDataSet AS A
MyDataSet AS B
A.AccountNumber=B.AccountNumber AND A.ColumnB>B.ColumnB
The output will be the recordset of all account numbers along with every combination of mismatched columns. You can use an inner join, but you may miss some records for which there is a unique value of ColumnB on the account.

You could also tweak this to serve your end purpose, but this concept is extremely useful. There are many times in which I use SQL to do stuff like this - although it doesn't fit well into processes you want to automate with one press of a button.

An Excel Idea:

You can place them into a pivot table and Let the account number be the row. Use ColumnB as an aggregate and select "Min" instead of "Count" (or sum). Add Column B again as an aggregate and select "Max" instead of "Count" (or sum).

You will have a pivot table consisting of:


If Excel does not agree to you using the datatype of columnB in an aggregate, create a unique numeric identifier for each unique value in column B by numbering them from 1 to 100 (or whatever the number of unique ColumnB values). Then lookup to get that number next to your identifier and perform the same aggregate function in the pivot table on the unique integer.

You will then have a pivot table consisting of:

AccountNumber,ColumnBMinIndexNumber,ColumnBMaxInde xNumber

From there you can lookup the original columnB value for each min and max and you have the desired result.

The biggest drawback to the excel approach is when you have more than two unique columnB records with the same account number, you will be missing at least one from the finished product.

Last edited by rgreenlee; 02-12-2019 at 08:09 AM..
Reply With Quote

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:51 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.12562 seconds with 11 queries