Beware Wet Paint
New Member
- Joined
- Jul 9, 2013
- Messages
- 34
<style>table { }td { padding: 0px; color: black; font-size: 12pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Calibri,sans-serif; vertical-align: bottom; border: medium none; white-space: nowrap; }.xl63 { font-weight: 700; }.xl64 { text-align: center; }</style>
<colgroup><col style="mso-width-source:userset;mso-width-alt:7509;width:176pt" width="176"> <col style="mso-width-source:userset;mso-width-alt:3157;width:74pt" width="74"> <col style="width:65pt" width="65"> <col style="mso-width-source:userset;mso-width-alt:2858;width:67pt" width="67"> <col style="width:65pt" span="2" width="65"> <col style="mso-width-source:userset;mso-width-alt:3669;width:86pt" width="86"> </colgroup><tbody>
</tbody>
Hello, I was wondering if anyone would possibly be able to help me.
Above is a spreadsheet I created of colleagues explaining which skills they are trained in: the text in each cell which intersects a name with a skill is selected from a drop-down menu of validated criteria: 'trained, not trained, underage'.
In column G I have calculated the total number of skills each colleague is trained in by using the formula =COUNTIF(B2:F2,"Trained") etc.
In row 46 I have calculated the total number of colleagues trained in a skill by using the formula =COUNTIF(B2:B45,"Trained") etc.
I was wondering if there would be a way to use a formula to return a list of names of colleagues who are trained in various combinations of skills? E.g a formula which would produce a list of names of colleagues who are trained on both checkout and self-scan etc? I'm uncertain as to which function/functions I'd have to use to achieve this or if this is even possible?
Any help would be brilliant and I'm a relative newbie I'm afraid.
Thank you
A | B | C | D | E | F | G | |
1 | Name | Checkouts | Kiosk | Self-Scan | CSD | Trolleys | Number of skills |
2 | Simon | Trained | Trained | Trained | Not trained | Not trained | 3 |
3 | Heather | Trained | Trained | Trained | Trained | Not trained | 4 |
4 | Emma | Trained | Not trained | Not trained | Not trained | Not trained | 1 |
5 | Jasmine | Trained | Trained | Trained | Not trained | Not trained | 3 |
6 | Hannah | Trained | Trained | Trained | Trained | Not trained | 4 |
7 | Sharon | Trained | Trained | Trained | Not trained | Not trained | 3 |
8 | Susan | Trained | Trained | Trained | Trained | Not trained | 4 |
9 | James | Trained | Not trained | Not trained | Not trained | Not trained | 1 |
10 | Emily | Trained | Not trained | Not trained | Not trained | Not trained | 1 |
11 | Paul | Trained | Not trained | Not trained | Not trained | Not trained | 1 |
12 | Sharon | Trained | Not trained | Not trained | Not trained | Not trained | 1 |
13 | Amy | Trained | Not trained | Not trained | Not trained | Not trained | 1 |
14 | Franny | Trained | Not trained | Trained | Not trained | Not trained | 2 |
15 | Patrick | Trained | Underage | Underage | Underage | Trained | 2 |
16 | Zoe | Trained | Underage | Underage | Underage | Not trained | 1 |
17 | Anthony | Not trained | Not trained | Trained | Not trained | Not trained | 1 |
18 | Val | Trained | Not trained | Not trained | Not trained | Not trained | 1 |
19 | Lousie | Trained | Not trained | Trained | Not trained | Not trained | 2 |
20 | Natalie | Trained | Trained | Trained | Not trained | Not trained | 3 |
21 | Peter | Trained | Trained | Trained | Not trained | Not trained | 3 |
22 | Shirley | Trained | Not trained | Not trained | Not trained | Not trained | 1 |
23 | Karen | Trained | Not trained | Trained | Not trained | Not trained | 2 |
24 | Nick | Not trained | Not trained | Not trained | Not trained | Trained | 1 |
25 | Lee | Trained | Trained | Trained | Not trained | Not trained | 3 |
26 | Joshua | Trained | Underage | Underage | Underage | Trained | 2 |
27 | Ayesha | Trained | Trained | Trained | Trained | Not trained | 4 |
28 | Claire | Trained | Not trained | Trained | Not trained | Not trained | 2 |
29 | Betty | Trained | Not trained | Trained | Not trained | Not trained | 2 |
30 | Adam | Trained | Trained | Trained | Not trained | Not trained | 3 |
31 | Julie | Trained | Not trained | Trained | Not trained | Not trained | 2 |
32 | Martin | Trained | Not trained | Not trained | Not trained | Not trained | 1 |
33 | Hilary | Trained | Underage | Underage | Underage | Not trained | 1 |
34 | Jack | Trained | Not trained | Trained | Not trained | Trained | 3 |
35 | Laura | Trained | Trained | Trained | Not trained | Not trained | 3 |
36 | Anna | Trained | Underage | Underage | Underage | Not trained | 1 |
37 | Joe | Trained | Not trained | Trained | Not trained | Trained | 3 |
38 | Elle | Trained | Not trained | Trained | Not trained | Not trained | 2 |
39 | Jane | Trained | Not trained | Not trained | Not trained | Not trained | 1 |
40 | Chris | Trained | Not trained | Trained | Not trained | Trained | 3 |
41 | Maria | Trained | Not trained | Trained | Not trained | Not trained | 2 |
42 | Rory | Trained | Underage | Underage | Not trained | Trained | 2 |
43 | Jamie | Trained | Not trained | Trained | Not trained | Trained | 3 |
44 | Victoria | Trained | Not trained | Trained | Not trained | Not trained | 2 |
45 | Christian | Trained | Trained | Trained | Not trained | Trained | 4 |
46 | Total no of colleagues trained | 42 | 13 | 27 | 4 | 9 |
<colgroup><col style="mso-width-source:userset;mso-width-alt:7509;width:176pt" width="176"> <col style="mso-width-source:userset;mso-width-alt:3157;width:74pt" width="74"> <col style="width:65pt" width="65"> <col style="mso-width-source:userset;mso-width-alt:2858;width:67pt" width="67"> <col style="width:65pt" span="2" width="65"> <col style="mso-width-source:userset;mso-width-alt:3669;width:86pt" width="86"> </colgroup><tbody>
</tbody>
Hello, I was wondering if anyone would possibly be able to help me.
Above is a spreadsheet I created of colleagues explaining which skills they are trained in: the text in each cell which intersects a name with a skill is selected from a drop-down menu of validated criteria: 'trained, not trained, underage'.
In column G I have calculated the total number of skills each colleague is trained in by using the formula =COUNTIF(B2:F2,"Trained") etc.
In row 46 I have calculated the total number of colleagues trained in a skill by using the formula =COUNTIF(B2:B45,"Trained") etc.
I was wondering if there would be a way to use a formula to return a list of names of colleagues who are trained in various combinations of skills? E.g a formula which would produce a list of names of colleagues who are trained on both checkout and self-scan etc? I'm uncertain as to which function/functions I'd have to use to achieve this or if this is even possible?
Any help would be brilliant and I'm a relative newbie I'm afraid.
Thank you