Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Match name, value & identify errors
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... |
#2
|
|||
|
|||
Match name, value & identify errors
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" wrote in message ... 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... |
#3
|
|||
|
|||
Match name, value & identify errors
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 -- |
#4
|
|||
|
|||
Match name, value & identify errors
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 -- |
#5
|
|||
|
|||
Match name, value & identify errors
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 -- |
#6
|
|||
|
|||
Match name, value & identify errors
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... |
#7
|
|||
|
|||
Match name, value & identify errors
You're welcome !
Thanks for posting back .. -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "Kikkoman" wrote in message ... Max, thanks, maybe something are meant to be done manually.... cheers. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Match Index | Excel Worksheet Functions | |||
Look up data in colum a and find match in colum b | Excel Discussion (Misc queries) | |||
Look up data in colum a and find match in colum b | Excel Discussion (Misc queries) | |||
Find a match that;s not exact | Excel Worksheet Functions | |||
Vlookup, Index & Match | Excel Worksheet Functions |