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


Not looking for a job? Tell us about your ideal job,
and we'll only contact you when it opens up.
https://www.dwsimpson.com/register


Reply
 
Thread Tools Search this Thread Display Modes
  #1  
Old 01-04-2019, 03:44 PM
ALivelySedative's Avatar
ALivelySedative ALivelySedative is offline
Member
CAS
 
Join Date: Dec 2013
Location: Land of the Pine
College: UNC-Chapel Hill Alum
Favorite beer: Red Oak
Posts: 3,417
Default Another silly excel question

I have received a document from another non-analytical department that, instead of using an indicator in a separate column, has simply bolded a field meeting a certain criteria, IE:

Code:
Status     Name
R     Name1
R     Name2
R     Name3
R     Name4
where these are all of the same status, but the bolded implies newly put into said status as of this year. Is there any type of lookup that works based on font/formatting?? I'm guessing VBA handles this which I don't use much.

ETA: the third 'R' is bolded....hard to see when in code brackets
__________________
Stuff | 6 | ACAS | FCAS stuff
Reply With Quote
  #2  
Old 01-04-2019, 03:49 PM
ALivelySedative's Avatar
ALivelySedative ALivelySedative is offline
Member
CAS
 
Join Date: Dec 2013
Location: Land of the Pine
College: UNC-Chapel Hill Alum
Favorite beer: Red Oak
Posts: 3,417
Default

This kinda works, but I'd rather have a formula so I don't have to do all the clicky clicky.
https://www.extendoffice.com/documen...bold-text.html
__________________
Stuff | 6 | ACAS | FCAS stuff
Reply With Quote
  #3  
Old 01-04-2019, 03:56 PM
MayanActuary's Avatar
MayanActuary MayanActuary is offline
Member
SOA
 
Join Date: Jul 2010
Posts: 1,832
Default

Use VBA. This should look through the first 100 rows in column A to check if they are bold, and then output to column E the answer. You can adjust columns and number of rows as needed.

Sub BoldChecker()

for x =1 to 100

If Cells(x,"A").Font.Bold = True Then
Cells(x,"E").Value = "Bold"
Else
Cells(x,"E").Value = "Not Bold"
End If

Next x

End Sub
Reply With Quote
  #4  
Old 01-04-2019, 03:57 PM
ALivelySedative's Avatar
ALivelySedative ALivelySedative is offline
Member
CAS
 
Join Date: Dec 2013
Location: Land of the Pine
College: UNC-Chapel Hill Alum
Favorite beer: Red Oak
Posts: 3,417
Default

That'll do, thanks.
__________________
Stuff | 6 | ACAS | FCAS stuff
Reply With Quote
  #5  
Old 01-04-2019, 05:14 PM
Knoath Knoath is online now
Member
CAS
 
Join Date: Oct 2015
Posts: 97
Default

If you really don't want to use VBA, you can use Find and Replace to identify bold Rs.

Copy the contents of the Status column into a new column, then highlight the new column. Open Find and Replace, type R in the find textbox, then click on the format button on the far right and specify that the font/fontstyle is bold. In the replace section type in a new value such as RX, then click on Replace All.

This will replace all the bold Rs with your new value and you can then use a lookup on this new value.
Reply With Quote
  #6  
Old 01-10-2019, 08:13 PM
Dr T Non-Fan Dr T Non-Fan is offline
Member
SOA AAA
 
Join Date: Sep 2001
Location: Just outside of Nowhere
Posts: 97,592
Default

Tell them to fix the data, and get back to you.
__________________
"Facebook is a toilet." -- LWTwJO

"45 es un titere" -- Seal of The President of The United States of America protest art
Reply With Quote
  #7  
Old 01-11-2019, 10:10 AM
yoyo's Avatar
yoyo yoyo is offline
Member
CAS
 
Join Date: Dec 2001
Posts: 23,968
Default

Quote:
Originally Posted by Dr T Non-Fan View Post
Tell them to fix the data, and get back to you.


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 10:36 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.19072 seconds with 9 queries