Excel: Compare two columns

[pruleone]

Does anyone know or have good idea how to make following comparison simpler than manually?

Input data:
V1 V2 V3 V4 V5 V6
E1 1 4 3 2 6 5
E3 2 1 3 4 5 6

Output table:
V1 V2 V3 V4 V5 V6
V1 0 1 2 2 2 2
V2 1 0 1 1 2 2
V3 0 1 0 1 2 2
V4 0 1 1 0 2 2
V5 0 0 0 0 0 1
V6 0 0 0 0 1 0

Explanation:
Logic of table is following - I check V1 values and compare them with V2 values to find out how many cases from them V1 value is less than V2 value.
In this example one value in V1 is less than V2 and opposite. So into table I will write 1.
And so I compare all cases to each other.


As I have those comparisons to make quite a lot than started to think about possible non-manual solution

 

[SkipVought]

pruleone,

You've been around long enough to have noticed the TGML markup, that would make the examples more understandable...
[pre]
.
V1 V2
E1 1 4
[/pre]

And I still can't understand how your first example relates to the second???

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

okay, I have a solution.

1) Name the ranges in your source table using the names in TOP row. The range names will be V1_, V2_ etc.

2) I set up the second table in I1:O7

3) the formula in J2, copied thru all data rows/columns of this table...
[tt]
J2: =SUMPRODUCT(--(INDIRECT(J$1&"_")>INDIRECT($I2&"_")))
[/tt]

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]