compare two columns in excel different sheets

COMPARE TWO COLUMNS IN EXCEL DIFFERENT SHEETS HOW TO

Different Formatting for Multiple Duplicates

When you hit OK, and OK again, your formatting will be applied.Īny cell that returns a value larger than zero will have the new formatting applied. Change the font, border, number type, or fill with color so that your duplicates will stand out. Next, select the Format… button and that will open the Format Cells window, where you can select any type of formatting you wish. That will open a field where you can write or paste the formula that we talked about above. In the New Formatting Rule window, select the option that says Use a formula to determine which cells to format. On the Home tab of the ribbon, choose the Conditional Formatting drop-down menu and select New Rule. In our case it is all of the phone numbers on the “Current” worksheet. Now that we've looked at how the formula works, let's see how the conditional formatting is applied.įirst select the entire range of cells that you want the formatting applied to.

That's because when we apply the conditional formatting to our entire table, Excel will examine each cell individually to see if the criteria apply, but only if it's expressed as a relative reference (no dollar symbols in the reference). It's important that the B2 used for the criteria argument is expressed as a relative reference, not an absolute reference. With conditional formatting, we use those numbers to highlight the entries that are duplicates.įor our example, the formula looks like this: =COUNTIF(Historical!$B$2:$F$1001,B2) If it is NOT found on that sheet, it will return a zero. If the value that is found in cell B2 is also found in our designated range on the Historical tab, the COUNTIF function will return a number greater than zero. The criteria argument is simply the value that we are looking for. In my video tutorial, my range is from B2 to F1001 on the “Historical” sheet. Range is the group of cells that you want to look in to find a specific value. The first is range and the second is criteria. For this process we are using the COUNTIF function.ĬOUNTIF has two arguments. If so, it will return a value of the number of times that data is found. The formula we'll write is going to examine a cell to see if its contents can be found in another range that we specify.

COMPARE TWO COLUMNS IN EXCEL DIFFERENT SHEETS HOW TO

Click to enlargeįirst let's look at how to write the formula and then we will see how to apply the conditional formatting. You can do so using a formula and conditional formatting. Let's say you have two Excel worksheets that have overlapping data and you want to call attention to any cells that have duplicate entries. If Cell.Formula = ws1.Range(Cell.Address).Compare-Sheets-with-Conditional-Formatting-AFTER.xlsx Highlighting Duplicates Between Worksheets If Cell.Formula = ws2.Range(Cell.Address).Formula Then If Not wb2.Sheets(ws1.Name) Is Nothing Then MsgBox 'Workbook: ' & sBook & ' is not open.' 'Open a 2nd Workbook and run this macro again.' MsgBox 'Error: Only one Workbook is open' & vbCr & _ I need matching column values to be copied to new excel(third excel) in column A. I've a macro script to identify the matching column value in two open excel(Column A in first excel and column A in second excel). Workbooks('WorkBookName.xls').Worksheets('Sheet2') ' compare two different worksheets in two different workbooksĬompareWorksheets ActiveWorkbook.Worksheets('Sheet1'), _ ' compare two different worksheets in the active workbookĬompareWorksheets Worksheets('Sheet1'), Worksheets('Sheet2') This example macro shows how to use the macro above: Sub TestCompareWorksheets() 'Compare ' & ws1.Name & ' with ' & ws2.Name

MsgBox DiffCount & ' cells contain different formulas!', vbInformation, _ With Range(Cells(1, 1), Cells(maxR, maxC)) Sub CompareWorksheets(ws1 As Worksheet, ws2 As Worksheet)ĭim lr1 As Long, lr2 As Long, lc1 As Integer, lc2 As Integerĭim maxR As Long, maxC As Integer, cf1 As String, cf2 As StringĪpplication.StatusBar = 'Creating the report.'Ĭells(r, c).Formula = ''' & cf1 & ' ' & cf2Īpplication.StatusBar = 'Formatting the report.' The result is displayed in a new workbook listing all cell differences. With the macro below it is possible to compare excel sheets.

Compare two columns in excel different sheets