Copy/Paste/Count String Data from LookUp List with VBA

[p45cal]

I realise you asked for VBA which Paul has successfully provided, however, in the attached there's a Power Query solution.
In sheet Sample (2), your source data in a proper Excel table at cell A1
A Power Query table at cell I1.
If the data in the source table changes you can refresh the result table by right-clicking it and choosing Refresh (like you do with pivot tables.

I hope columns I to M appear as you want them to.
You'll see in this result table that column N (headed String) also repeats the string for every row in the results, which isn't too bad for this table, but if it were to be applied to to long text data as in cell F2 of the Sample sheet, it could become tiresome.

So on sheet Sample, in the Power Query results table at cell M1, I've used the same technique but removed the duplicates.
The source table for this is your data at cells D1:F2
Thankfully, this shows the same results as Paul's code which is in columns H to K. I hope columns M:N are as you want them.

If this is of interest, come back and I'll explain some more!