Match values and display row difference under column

**sans** · 05-30-2012, 03:00 PM

Hello,

I would like to compare each range with all of its previous ranges, find the matched values and display the row difference under the appropriate column.
I am attaching an example sheet as its a little difficult to describe.

The example uses 7 values in the ranges that are being checked. However I also have sets of data where the ranges have 4 values, 8, 10 values, 12 values etc. If it is possible to help me with a UDF where I can use a formula to select the data it would be most convenient. Please do let me know if I am not being clear at any point in the example.

Thank you for looking,
Sans

**aussieboykie** · 05-30-2012, 04:57 PM

You can use an array formula to do this type of calculation. For example, {=COUNT(IF($C14:$I14=$C13:$I13,1,FALSE))} in cell L14 in your example will return 1 as the count of matches with the row above. This is not quite what you want and of course you need to repeat the calculation for all preceding rows. However, I imagine that this can be done using a combination of array formulae and VB but that's currently a step beyond my level of expertise.

Anyway, maybe this is enough to get you started.

Regards, AB

Match values and display row difference under column

LinkBack

Thread Tools

Rate This Thread

Display

Match values and display row difference under column

Re: Match values and display row difference under column

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions