Matching identical data using data only once in the matching proce

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

When using the matching function of ISNA (or any other function), how can I
prevent two identical sets of data on one list from being used to match the
same single identical set of data on another list? I am trying to match one
for one set of data and not use the matched set of data again in any matching
function. For example, if the first list has document A for $100 listed twice
and second list has document A for $100 listed once, I only want one of the
document A for $100 from the first list to match the document A for $100 on
the second list. I want the second document A for $100 on the first list to
not match anything on the second list because the document A for $100 on the
second list has already been used in the matching process.
 
one possibility
If you are using a helper column with "Matched" / "Not Matched"
and your data is in sheet1 Column A and sheet2 column A

in B1 enter
=if(countif(A$1:A1,A1)>Countif('Sheet2'!A:A,A1),"Not matched", "Matched")
copy and paste down.
on Sheet 2 B1 enter
=if(countif(A$1:A1,A1)>Countif('Sheet1'!A:A,A1),"Not matched", "Matched")
copy and paste down
if the data is in several columns Column A and B
maybe
=if(sumproduct(--(A$1:A1=A1),--(B$1:B1=B1))>Sumproduct(--('Sheet2'!A$1:A1=A1),--('Sheet2'!B$1:B1=B1)),"Not matched", "Matched")
in C1 in Sheet 1 and equivalent in C1 sheet 2
copy and paste down
 
Back
Top