VBA To Perform Weighted Ranking From Array Of Data

I'm trying to achieve a table of weighted ranking based on correlations, a table I already have like the one given below

John / Products Owned 1 2 3 4
Shoes Socks Polish Brush Lace
Trousers Shoes Socks Belt Polish
Wallet Belt Trousers Shoes Socks

What I'm trying to achieve is this:

for all the top 4 correlations given for each product owned, i need to consider the ones not owned by John and recommend overall rank of the following nature:

For instance, the rank of socks here will be (1+2+4)/3 = 2.33, the rank of belt will be (5+3+1)/3 = 3 [ I'm considering the rank of belt as 5 for shoes, since it doesn't occur in the top 4 ]. Shoes, here will not be ranked since it is already owned by John.

I'm struggling to translate the logic into code here. Currently I perform it in a cumbersome manner, copying all columns to one column, then performing a pivot to find repetitions and so on.

Perhaps there's a function that could scan a dynamic sized table here, find the unique items and number of times they're repeated?