0

I currently have two different tables:

The first table is something like this:

ID Name Course name Course date
2213 Bruno CourseA 07/05/2021
2215 John CourseB 04/05/2021
2216 Carlos CourseB 04/05/2021
2215 John CourseA 08/05/2021

The second table looks like this:

ID Name CourseA CourseB
2213 Bruno
2215 John
2215 Carlos

My desired output is going to be like this (which is basically the second table mentioned above but now with the dates):

ID Name CourseA CourseB
2213 Bruno 07/05/2021
2215 John 08/05/2021 04/05/2021
2216 Carlos 04/05/2021

What is the easiest way I can do something like this? Thanks a lot!!!

Scott Craner
  • 148,073
  • 10
  • 49
  • 81
Bruno Tavares
  • 27
  • 5
  • did you try a pivot table? – Scott Craner Oct 18 '21 at 17:09
  • Yes, I tried but the first table I have is actually huge (more than 100k rows) and many employees are not active anymore =( so it is very hard to filter and select the ones that I want – Bruno Tavares Oct 18 '21 at 17:14
  • Then you will need to do something from my answer here: https://stackoverflow.com/questions/42492758/vlookup-using-2-columns-to-reference-another/42493697#42493697 – Scott Craner Oct 18 '21 at 17:16

2 Answers2

0

Try this in the CourseA column:

=IF(SUMIFS(Table1[Course date],Table1[ID],[@ID],Table1[Course name],"CourseA")=0,"",SUMIFS(Table1[Course date],Table1[ID],[@ID],Table1[Course name],"CourseA"))

Then just change the string criteria for the CourseB column.

Note: This assumes no duplicate rows in Table1.

Bryan Rock
  • 582
  • 1
  • 4
  • 13
0

Another option with array formula:

=IFERROR(INDEX($D:$D,MATCH($H2&"^"&I$1,$B:$B&"^"&$C:$C,0)),"")

after editing confirm it with Ctrl+Shift+Enter

enter image description here

basic
  • 11,673
  • 2
  • 9
  • 26