Match name, value & identify errors

[Kikkoman]

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 A1:D5
[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
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Kikkoman" <[email protected]> wrote in message
news:[email protected]...
> 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]

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

(prev link may not have worked, sorry)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

[Kikkoman]

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" wrote:

> Quick sample link at:
> http://cjoint.com/?kDdYGSmxEL
>
> (prev link may not have worked, sorry)
> --
> Rgds
> Max
> xl 97
> ---
> Singapore, GMT+8
> xdemechanik
> http://savefile.com/projects/236895
> --
>
>
>

[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
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

[Kikkoman]

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

"Kikkoman" wrote:

> 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...
>

[Kikkoman]

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

"Kikkoman" wrote:

> 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]

You're welcome !
Thanks for posting back ..
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Kikkoman" <[email protected]> wrote in message
news:[email protected]...
> Max, thanks, maybe something are meant to be done manually....
> cheers.