Forum Discussion

SeaBiskit's avatar
SeaBiskit
Copper Contributor
Jun 04, 2024

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 knowle...
excel
mathetes's avatar
mathetes
Gold Contributor
Jun 04, 2024
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.
SeaBiskit's avatar
SeaBiskit
Copper Contributor
Jun 04, 2024
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
Sebastian
  • mathetes's avatar
    mathetes
    Gold Contributor
    Jun 04, 2024

    SeaBiskit 

     

    I'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. 

Resources