Here is a solution without VBA. Would recommend it, when this is something more rare than a monthly task.
This is how you setup your data - note, cols A-D are the "new cols".
Here are the formulas - set them up on row 2, then copy down:
A2=IF(ISBLANK(F2),INDEX(J:J,ROW()-COUNTIF(F:F,"*")+1),F2)
B2=IFERROR(T(VLOOKUP($A2,$F:$H,2,FALSE)),"")
C2=IFERROR(T(VLOOKUP($A2,$F:$H,3,FALSE)),"")
D2=IFERROR(T(VLOOKUP($A2,$J:$K,2,FALSE)),"")
A2
fills in all Names from your current activity-table, until it gets blank cells. Then you start filling in Names from your new activity-table, using COUNTIF
to get the Number of non-empty cells and ROW
and +1
to get the correct INDEX
/ Name.
Now you just have to make seperate lookups for your activities.
As you see, you will get duplicates, but they can be easily handled. My suggestion would be - copy the area A-D as values to another destination, then use the build-in duplicate eraser from the data tab.
Of course you could do this with VBA, however often this is a task, which is done by inexperienced users - and to create a user-safe dynamic version of this, is quite some code and needs a lot more specifics to be done. However, I want to remind you, that this is a basic database-task, and can be done a lot easier with database functions.
for loop
to add an x into Column D where there is a match. There might be a better / more efficient way tho...