compare data in columns

Question

How do you remove duplicate when comparing data in 2 different columns?

Answer 1

Hi,

Assumption:

1. Both columns are on the same worksheet; and
2. You want to compare data against the 2 columns rather than in each column

Select the first range, hold down the control key and select the second range. Go to Conditional formatting > Highlight duplicates > OK. Now filer on any column by colour and delete those rows.

Answer 2

Hi @Jerry Kots,

Thank you for posting your question in the Microsoft Q&A forum.

To remove duplicate when comparing two data columns, you can try using these workarounds:

1- Conditional Formatting

You can use Conditional Formatting in Excel to highlight the duplicate cells and delete them manually.

• In my test sheet, I created two data columns like this. To find duplicate value between two columns, I will select column B > in Home tab > Conditional Formatting > New rule

• Add the formula to compare cell =COUNTIF(A:A, $B1)>0 and format it to be colored in red

• After pressing OK, the duplicates will be colored, and I can delete them manually.

2- Using helper columns

This is the same approach as conditional formatting, but this option will use formula in a help column to identify rows containing duplicates.

• In a helper column (in my test sheet I use column D), use this formula =IF(COUNTIF(A:A,B1)>0, "Duplicate","") in cell D1 and drag down the formula
• It will show results on rows that containing duplicate cells of column B.

3- Use Excel built-in remove duplicates

• Copy both columns into a single column
• Select the new combined one and go to Data > Remove Duplicates

If you're having large data sheet, there is an option for using PowerQuery. However, for further assistance, can you share us the sample data so we can take a look and understand the situation clearly to provide the most appropriate solution for your request.

Please understand that our initial response does not always resolve the issue immediately. However, with your help and more detailed information, we can work together to find a solution.

Thank you for your understanding and cooperation. I'm looking forward to your reply.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".    

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

To remove duplicates when comparing data in two different columns in Excel, you can use the removeDuplicates method from the Excel JavaScript API. This method allows you to specify the columns you want to check for duplicates. Here’s how you can do it:

1. Select the Range: First, select the range of cells that includes the two columns you want to compare.
2. Use removeDuplicates Method: Call the removeDuplicates method on the selected range. You need to provide an array of column indices that you want to check for duplicates. For example, if you want to check the first and second columns in the selected range, you would pass [0, 1] as the argument.
3. Include Header Option: If your range includes a header row and you want to ignore it while checking for duplicates, set the includeHeader parameter to true.

Here’s a sample code snippet:

await Excel.run(async (context) => {
    let sheet = context.workbook.worksheets.getItem("Sheet1");
    let range = sheet.getRange("A1:B10"); // Adjust the range as needed

    let deleteResult = range.removeDuplicates([0, 1], true);
    deleteResult.load();

    await context.sync();

    console.log(deleteResult.removed + " entries with duplicate values removed.");
    console.log(deleteResult.uniqueRemaining + " unique entries remain in the range.");
});

This code will remove rows with duplicate entries based on the values in the specified columns and provide feedback on how many entries were removed.

---

References:

• Remove duplicates using the Excel JavaScript API