I'm probably late on this, but here goes:
Pivot table is the easiest way. Just copy and paste the data into a new range if you're worried about pivot functions confusing your customer. I'm not sure what exactly you'd be filtering on in the new columns other than the person's name. It would be an odd use case to want to filter on particular quantities of the medication, so maybe there is something to your project that I'm missing.
Assuming you hate pivot tables, you could also use the sumproduct formula in your second table. Assuming you turn your ranges into tables (Control 'T') and name the top table "Original" and the bottom table "Converted", you'd enter the following formula in B11 through 13:
=SUMPRODUCT(--(Original[Name]=[@Name])*(Original[Product]=Converted[[#Headers],[Valtrex]])*(Original[Quantity])*1)
and this formula in C11 through 12
=SUMPRODUCT(--(Original[Name]=[@Name])*(Original[Product]=Converted[[#Headers],[Zovirax]])*(Original[Quantity])*1)
Sumifs function would also work.
The advantage to sumproduct and sumifs is that they don't require the helper column that @NoName added. All that said, Pivot Tables are infinitely superior for what you're trying to do here, especially if you have more than a couple of medications/products at issue. With pivot tables, you wouldn't have to write new formulas for each column.
Finally, let's say you're actually trying to merge two different sets rather than simply transform an existing data set (i.e., both data sets have unique information that has to be included in the merged data set). In that case, you'll want to learn to use PowerQuery/ the "Get Data" feature in excel. It is extremely powerful for everything from merging data sets to transforming existing data in much more complex ways. I'm not going to explain it all here, just saying it may be something you want to learn about and/or take a class on.