Camparing figures in a matrix

[Sandip Shah]

Hi,

I have to compare a value which is based on 2 criteria. Let me explain
with an example

CO1 CO2 Value
AA 51 (5739.83)
AA 64 23895.92
AA 78 (23895.92)
AC AD (51977.30)
50 92 (3455.07)
51 AA 5739.83
51 AC 1233572.10
51 AD (116922.61)

The above is a sample data. In reality the data runs into thousands of
lines.

The combination of CO1 and CO2 for eg. AA and 51 respectively has a
value of (5739.83). This needs to be matched with the opposite
combination of 51 and AA the value for which is 5739.83.

Through some Macro or Function, the matched value needs to be shown
next to the "Value" column. This will enable to obtain a exception
listing for unmatched items.

Regards
Sandip.

 

[Dave Peterson]

This would be the way I'd approach it.

I'm assuming that your values are in columns A:C.

I'd insert a couple of new columns in D:E.

In D2, I put this formula:
=A2&"."&B2&"."&C2
and drag down

In E2, this formula:
=B2&"."&A2&"."&-C2
and drag down
notice that columns A and B are reversed and the number in C is negated.

And if your data has dots in them, then use a different "separator" character.

=A2&char(1)&b2&char(1)&c2
will work ok--but the char(1) will look like a box--but that won't matter.

Then in F2, put a formula that looks at the value in D2 and tries to find a
match in column E:

=IF(ISNUMBER(MATCH(D2,$E$2:$E$9,0)),"yes","no")
(and drag down--and adjust the range. My data stopped on row 9.)

I got this when I did it:

co1 co2 val co1.co2.val co2.co1.-val D found in E
AA 51 -5739.83 AA.51.-5739.83 51.AA.5739.83 yes
AA 64 23895.92 AA.64.23895.92 64.AA.-23895.92 no
AA 78 -23895.92 AA.78.-23895.92 78.AA.23895.92 no
AC AD -51977.3 AC.AD.-51977.3 AD.AC.51977.3 no
50 92 -3455.07 50.92.-3455.07 92.50.3455.07 no
51 AA 5739.83 51.AA.5739.83 AA.51.-5739.83 yes
51 AC 1233572.1 51.AC.1233572.1 AC.51.-1233572.1 no
51 AD -116922.61 51.AD.-116922.61 AD.51.116922.61 no

Then you could apply Data|Filter|autofilter and show just the Yes's or No's.

 

[Sandip Shah]

Thanks a lot... It worked perfectly.

Bye
Sandip