VBA to perform weighted ranking from an array of data

[pbarvind]

Hi People,

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

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

</pre>

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.

Being a relative VBA newbie (i've asked for a few times), 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?

I'm hoping there's an easier way to do this in code, and any help would be greatly appreciated.

Thanks so much,

 

[pbarvind]

I'm sorry, that pre tag didn't work quite as I expected.
<TABLE>
<TR>
<TD>John / Products Owned</TD>
<TD>1</TD>
<TD>2</TD>
<TD>3</TD>
<TD>4</TD>

</TR>
<TR>
<TD>shoes</TD>
<TD>socks</TD>
<TD>polish</TD>
<TD>brush</TD>
<TD>lace</TD>
</TR>
<TR>
<TD>trousers</TD>
<TD>shoes</TD>
<TD>socks</TD>
<TD>belt</TD>
<TD>polish</TD>
</TR>
<TR>
<TD>wallet</TD>
<TD>belt</TD>
<TD>trousers</TD>
<TD>shoes</TD>
<TD>Socks</TD>
</TR>

</TABLE>

Thanks again!

 

[pbarvind]

Managed to solve that hehe ... sorry for the waste of space