Excel thread: can I use conditional formatting here?

May 29, 2019 11:17 AM

I want to set up a macro or conditional formatting or something to be able to have a column in List A check List B and then change colors based on info from List B. More below...

Sorry, a lot of detail here. Not sure how to explain this more abstractly.

We run citation reports for faculty and are being asked to supply Journal Impact Factors (JIF) in these reports. The program that we use won't run a report with this information IN the citation report. We've come up with a temporary solution, but I'm curious if this can be automated in some way. Additionally, JIF is a somewhat problematic metric, so we'd rather steer them to quartile rankings.

If I make a master list of journal titles along with their JIF and quartile ranking (List B), and I then have a citation report with a list of journal titles (List A), can I do something to that column of journal titles (A) to make it search the master list (B)? My idea is that if the journal title is found in the master list (B) it would see what quartile ranking it has and change color accordingly (in list A). So a cell with a title with a Q1 ranking would turn green, a Q2 ranking would turn orange, etc.

Can I do this in Excel? How? If this is a much bigger project than I'm thinking or would actually require something more complex to execute, that's helpful to hear too.

Test documents here if you want to see/play with them.

posted by LKWorking to Computers & Internet (8 answers total)

It’s easy if you split up list B to separate Q1, Q2, etc.

Set up a conditional formatting rule, and use formula =COUNTIF([Q1 list],[first cell of list A]=1 and set your Q1 format. Do likewise for Q2, etc. You should highlight list A when you do this and use a non-fixed (no $’s) reference for the first cell of list A.

COUNTIF returns the count of cells in a list that match the criteria you specify. In this case, it’s that the cell you’re formatting matches the Q1 or Q2 list. You might want to do >0 instead of =1 if there might be duplicates in the Q1 or Q2 list.
posted by Huffy Puffy at 11:49 AM on May 29, 2019 [1 favorite]

Best answer: If you have both sets of data as worksheets (or tabs, however you want to call them) in one single Excel file you can use INDEX and MATCH to reproduce most of what you want.

Say that the citation report is saved in a tab called 'savedrecs' and your titles and their quartile ranking are saved in another tab called 'titles' in the same spreadsheet.

You can insert a column A in 'savedrecs' and put this formula in A3:
=INDEX(titles!$E$2:$E$10,MATCH(savedrecs!D3,titles!$A$2:$A$10,0))
which looks at D3 (source title CELL) and then looks for that same information in the 'titles' tab and returns the results of the Quartile rank column.

Note: this will throw an error if the source title in your 'savedrecs' is not listed on the 'titles' tab, which some of yours are not. This might be good for you to know.

Then you can throw whatever conditional formatting you want on column A.

I have mocked up a version of this and I'm putting it on Dropbox and sending you a link via MeMail.
posted by komara at 12:36 PM on May 29, 2019 [1 favorite]

Best answer: I would add a column to list A that uses VLookup to find the quartile ranking on List B. Then I would use conditional formatting to change the color of the record in list A based on the quartile in the new column.
posted by soelo at 1:29 PM on May 29, 2019 [3 favorites]

Response by poster: These are great! I'm combining these ideas as we speak. Thank you.
posted by LKWorking at 2:40 PM on May 29, 2019

I'd use vlookup as well, but you can do it without adding the additional column if you use vlookup directly in the conditional formatting. Bonus: don't have to worry about error-checking for cases where the journal in list A doesn't appear in list B, because the conditional formatting will just treat that as FALSE rather than throwing up an error. You'll need one rule for each quartile.
posted by DevilsAdvocate at 5:21 PM on May 29, 2019

I included the extra column so that users can see why the record is colored the way it is (or is not colored) and they don't have to remember that green is 1, orange is 2, etc.
posted by soelo at 7:12 AM on May 30, 2019

Also - just a general tip - formulas are good, conditional formatting is good, but stay away from Macro's/VBA where possible, using those techniques will limit your ability to run the spreadsheet on servers (Office Online/SharePoint) and across platforms (Android, iOS/Apple). (Not a problem if you are only the single person who will ever be working with your spreadsheet)
posted by jkaczor at 7:43 AM on May 30, 2019

Response by poster: I have two working options now, thank you very much for your help!
posted by LKWorking at 9:45 AM on May 30, 2019