This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(51.2, 76%, 14%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ERF%3C/text%3E%3C/svg%3E)
Hey there,
I'm trying to set up a training log in excel, basically it will be a list of users, and the training they've taken.
There will be a scrollable list of users, when you click a user I want excel to display which training they've taken.
I've attached a crude screenshot which details what I'm trying to do, any help is appreciated!
Hi @Ray Flits
What version of Excel is in use? If 365, do you have the FILTER function?
Based on your description, it may require VBA code to realize.
You could add a related tag like “office-vba-dev”, this may get more help.
If an Answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Since I haven't received your reply, I have added the tag “office-vba-dev” for you.
Wish your issue could be solved.
Thank you!
You're welcome~
2 options with Excel 365 & Training data formatted as a table named Training (not mandatory, will work the same with a range)
in E8:
=IF(ISBLANK(B8), "",
SUBSTITUTE(
TEXTJOIN(", ",TRUE,IF(INDEX(Training,MATCH(B8,Training[NAMES],0),0)<>0,Training[#Headers],"")),
"NAMES, ",""
)
)
in E9:
=IF(ISBLANK(B8), "",
SUBSTITUTE(
TEXTJOIN(", ",TRUE,IF(FILTER(Training,Training[NAMES]=B8)<>0,Training[#Headers],"")),
"NAMES, ",""
)
)
Corresponding workbook available here
Hi,
Here are 2 ways
In cell B9, enter this formula and drag down
=ARRAYTOTEXT(FILTER($B$1:$J$1,BYCOL(($A$2:$A$5=A9)*($B$2:$J$5="x"),OR)))
In cell G9, enter this formula
=GROUPBY(A2:A5,BYROW(B2:J5,LAMBDA(a,ARRAYTOTEXT(FILTER($B$1:$J$1,BYCOL(a="x",OR))))),ARRAYTOTEXT,,0)
Hope this helps.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(201.6, 3%, 29%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EI%3C/text%3E%3C/svg%3E)
In cell D12 apply this formula:
=ARRAYTOTEXT(FILTER($F$1:$M$1, BYCOL(--FILTER(IF(ISBLANK($F$2:$M$5), FALSE, TRUE), $E$2:$E$5 = $C12), SUM)))
The formula also works in cases where, in addition to text, numbers are entered in cells F2:M5.
Hope this helps.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(89.60000000000001, 51%, 18%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ES%3C/text%3E%3C/svg%3E)
This would be better and more easily done in Access. Excel is not a database.
