|
MISSMATCHES
Finds the miss-matched rows (anti-join) in two ranges/arrays/tables/lists.
The matches can be done on one or more chosen columns.
Matches are not case-sensitive so “ID” matches with “id”.
Numbers are treated as text, so 42 matches with both “42” and 42.
You can specify which columns from the Left range or array to match with which columns from the Right range or array. Columns that are not used to match will still appear in the output.
MISSMATCHES Syntax
MISSMATCHES (Left, Right, Match_Type, Match_Cols, No_Match)
Left (Required)
The Left range/array/list/table.
Right (Required)
The Right range/array/list/table.
Match_Type (Optional: default = 3)
The type of output match requested:
1 = All rows from Left that have no match in Right
2 = All rows from Right that have no match in Left
3 = All rows from Left that have no match in Right, and (below) all rows from Right that have no match in Left
Match_Type is optional; if omitted or empty, the value 3 will be used.
Match_Cols (Optional: default = -1)
The columns to match on:
-1 = match columns on column headers. The first row of Left and of Right is assumed to contain column headers. The column headers do not have to be in the same sequence. Any columns whose headers that do not match are ignored by the matching process but are shown in the output.
0 = match on all columns in sequence
Array or range = Column numbers or header names to match on. If a single row then these column numbers/header names will be use for both Left and Right. If 2 rows then the first row gives the column numbers/header names for left and the second row gives the corresponding column numbers/header names from Right. For example {1,3;2;4} matches column 1 in Left with column 2 from Right and column 3 from Left with column 4 from Right, and {“Brand”;”Alternate Brand”} would look for a column header of “Brand” in Left and “Alternate Brand” in Right.
No_Match (Optional: default= #Null)
Value to use for rows that do not match.
|
