Match name, value & identify errors

[Guest]

Thanks in advance,

Data:
------data 1------ --------data 2------
col A col B col C col D
yum 180 yum 180
yum 200 yum 630
yum 430
yum 832
yum 330

Objective: Match data 1 to data 2 and identify errors
Roll 1 - no problems, both names and value matches
Roll 2 & 3 matches of Data 1 matches to roll 2 of data 2 (or vice versa and
it could hv 3 values in matches to 1 value)
Roll 4 - Missing names & values in data 2,
Roll 5 - Missing names & values in data 1, (need to insert into these data
into data1 -highlighted)

I have about 800 rolls of data, trying to match them up one by one is a
freaking nightmare!!! HELP...

 

[Max]

One way, assuming data as posted is within A15
[Quick sample at: http://cjoint.com/?kDdYGSmxEL]

Put in F1, array-enter the formula (press CTRL+SHIFT+ENTER):
=IF(OR(C1="",D1=""),"",IF(ISNUMBER(
MATCH(C1&"_"&D1,$A$1:$A$4&"_"&$B$1:$B$4,0)),"",ROW()))
Copy F1 down to F5

(Adapt the ranges to suit: $A$1:$A$4, $B$1:$B$4)

Put in G1 (normal ENTER):
=IF(ISERROR(SMALL($F:$F,ROW())),"",
INDEX(C:C,MATCH(SMALL($F:$F,ROW()),$F:$F,0)))
Copy G1 across to H1, fill down to H5

Cols G & H will return the lines within cols C & D which do not match with
those within cols A & B. All results will be bunched neatly at the top, with
blank rows below. So we could then do a simple copy > paste special > values
of these lines to append below the last line in cols A & B

 

[Max]

Quick sample link at:
http://cjoint.com/?kDdYGSmxEL

(prev link may not have worked, sorry)

 

[Guest]

thanks Max,

A few problems:

1, Currently in the xls, g1 = 630, and treated as an error. However, it
should not be an error because cell b2+b3 = d2.
2, a4 & b4 was not picked up as an error
3, a6=yum; b6=100; c6 = yum; d6=200; No error is detected. The problem is
then the 200 will not be detect because it assumes the 200 = to the 200 in
cell b2.

i just wondered whether there are too many variables??

Thanks again.

 

[Max]

Your issue appears more complex than I had originally interp'd.
I don't think I have any further ideas to offer you, sorry.
Hang around awhile for possible insights from others

 

[Guest]

Max, thanks, maybe something are meant to be done manually....
cheers.

 

[Max]

You're welcome !
Thanks for posting back ..