Forum Discussion
Colour Code missing data
Hi,
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 persons 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.
Thankyou.
Sebastian
3 Replies
- The answer will depend very much on how your data are arrayed, and the description you provide is a little too vague to know whether or not I'm picturing it as you have it. Could you post a copy of your actual workbook--more likely a reasonable representation of it, without the real names and any other identifiable data. Put that on OneDrive or GoogleDrive with a link pasted here that grants access.
- Hey mathetes,
Link to data is here
https://docs.google.com/spreadsheets/d/18vcgdJhzeS_zqAaOBkKs1fl0F7ftZeKK/edit?usp=sharing&ouid=117025028883126365766&rtpof=true&sd=true
Thankyou
SebastianI'm attaching an Excel version of your file here so others can see it without having to go to GoogleDrive.
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 bid 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 really have a solid and reliable data base, I'd recommend that the records be cleaned so that for each individual there is one row per course taken and that it include 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.
