count repeated numbers in position

[cds1]

I have a data wherein I want count a numbers repeated in same position, prior to current & next to the current position and so on for all columns in row compare to previous row.
a b c d f
1 tot repetition no repeated no repeated twice same position previous column next column +2 column +3 column -1 column -2 column -3 column
2 4 5 8 7 9
3 3 9 2 11 3 1 9 - - - - - 1 -
4 6 8 11 4 9 2 11,9 - - - 1 - 1 - - -
5 4 9 4 5 7 2 4,9 4 1 2
6 4 5 9 4 9 3 4,5,9 4,9 1 2 - 2 - 1

for ex : when we compare third row with second row 9 of b3 column repeated from f2 column of second row & total no repeated 1 ie., similarly forth row with third row 11 of c4 repeated from d3 column & 9 of f4 repeated from b3 column of third row and total no repeated 2 i.e., 11 & 9 . further fifth row with forth row 4 appears twice in fifth row, which is repeated from d5 of forth row & 9 of b5 repeated from f4 column of forth row. I want result with total numbers repeated & which numbers repeated, no repeated twice & previously repeated twice and position of repetition in row i.,e if it is in same position i.e, 0 difference in coumn , if it is next position ie., +1, if it is previous position then -1 , in a row



kindly help me to write formula or vba I have following formula to get columnwise result which ignore twice repeated nos


=IFERROR(MATCH(a3,a2:f2,0)-MATCH(a3,a3:f3,0),"NA")

Thanks in advance

[Richard Buttrey]

Please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context.

Show a before and after situation with manually calculated results, explaining which information is data and which is results, and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well.

To upload a file click the Go Advanced button at the foot of your post, look underneath the post area for the Manage Attachments section and take it from there.