Forum Discussion

lcovey's avatar
lcovey
Copper Contributor
Jun 08, 2023
Solved

How to find matching values in 2 cells

I have an excel sheet with 10,000 rows.  I need to find any duplicates that have the same values in both A & B. For example: A1 is #6805 & B1 is $4.95  (I need to find any other rows that have this ...
Formulas and Functions
  • Lorenzo's avatar
    Lorenzo
    Jun 08, 2023

    lcovey 

    On reflection... In attached file see query Variant where all steps are done with Power Query User Interface

NikolinoDE's avatar
NikolinoDE
Gold Contributor
Jun 08, 2023

lcovey 

Alternative approach…

If you want to find exact matches between two cells, you can use the EXACT function in combination with the COUNTIFS function. Here is how you can modify the previous formula to include an exact match:

  1. Assuming your data is in columns A and B, enter the following formula in cell C1:

=IF(COUNTIFS(A:A,A1,B:B,B1,C:C,"<>")>0,"Duplicate","")

  1. Copy the formula in cell C1 and paste it down to the remaining rows in column C to apply the formula to the entire dataset.

The formula now includes an additional condition using the "<>" operator in the COUNTIFS function. This condition checks if there are any other rows with the same values in both A and B but different values in column C. If such rows exist, it marks the current row as "Duplicate" in column C.

This modification ensures that the match is based on exact values in cells A and B while ignoring any differences in column C.

Remember to adjust the ranges (A:A, B:B, C:C) in the formula to match the actual range of your data.

 

Resources