I have column A, which has data in groups, for example, Andrew (a), Brian (b), Chloe (a), Diana (b), and so on (about 50 entries like this). In column B, they all have their respective scores, but the matching groups are of the same colours in cell A. For example, all (a)s are red, and all (b)s are blue, and so on. Then I make a bar chart for each individual, where the Y-axis are their names, and the X-axis are their individual scores. So I formatted the bar colours to follow their group name, and I initially did this using data points (it was tedious). Occasionally I will sort by the score to do some analysis. When this sorting changes, my colours on the bar charts get "scrambled". Is there another way to assign bar chart colours that would help me address this issue?
[Sample Table & Bar Chart]
| Name | Score |
|---|---|
| Andrew (a) | 234 |
| Brian (b) | 352 |
| Chloe (c) | 456 |
| Diana (a) | 543 |
| Emanuel (b) | 241 |
| Fiona (c) | 356 |
| Gabriel (a) | 256 |
| Hermione (b) | 245 |
| Illiyana (c) | 345 |
| Jacob (a) | 145 |
| Kane (b) | 123 |
| Louis (c) | 134 |