Colour Code missing data - Microsoft Community Hub
Hi there! My level of experience with Excel would be that of "I know what question to ask Google / Chat GPT / Gemini to get an answer" and then implement that answer. Some basic VBA and formula knowledge. My current challenge is this... I have 3 columns of data in columns A, B, C. Column A is a name, Column B is a list of training they have completed and Column C is the list of training that they SHOULD have completed. I have the 'filter' on the headers so I can select a name that shows me their completed training. What I would like is a way to, when I filter by a name (Column A) and it shows me the person's completed training (Column B), it highlights in RED in Column C what training is missing from Column B as matched against Column C and what training is NOT missing in green as matched against Column C. Not sure if creating rules or a VBA or some formulas would achieve this result. Your assistance would be greatly appreciated. Thank you. - Sebastian
@SeaBiskit, I'm attaching an Excel version of your file here so others can see it without having to go to Google Drive. My first reaction though is "Huh?!" First, it's because the arrangement of the columns doesn't look at all like you described it. Not a big deal, but... More significant: the data table in your "Set" column--describing the courses to be taken--is clean (because it's one course per cell). The data in column A--presumably the courses they have taken--is jumbled together when it's more than one and often not in the list prescribed at all. So my first thought is that this data on what people have taken needs to be cleaned up. If you want to have a solid and reliable database, I'd recommend that the records be cleaned so that for each individual there is one row per course taken and that it include the date the course was taken. Especially as you do more training of the individuals, having all that a given person has taken in one cell will just make things murky. On the other hand, a clean record that shows one row for each course that John Doe has taken, and I've started an example of what I mean in the attached. I've also started but unable to get conditional formatting working correctly--somebody else no doubt can--to highlight those taken, those not.
