compare two columns in excel and remove duplicates

Compare two columns in excel and remove duplicates code#

If WorksheetFunction.CountIf(r, Cells(k, 'E')) > 1 Thenįinally after running the macro and your job is over you can delete the column E if necessary. In the original file also you can copy the data in sheet 1 in sheet 3 also in addition to SAVING THE DATA SOMEWHERE ELSE SAFELY.ĭim j As Integer, k As Integer, r As Range xlCompare gives you a rich set of options to control what you are searching for. However, if you dont need to compare formulas, you can exclude this in the options. to test for a rerun of the macro delete data in sheet 1 and copy it in from sheet3 and again run the macro xlCompare finds differences in the text of formulas, and the text of an array formula. Once the macro is run the sixth row will be deleted. Note: In the above formula, whichever cell it is finding as duplicate in Column A, it will. E2 will be copied down the rows in column E By using ISERROR and MATCH formula we can compare 2 columns. easy way of copying down large no of rows is take the cursor to the right bottom of the cell E2. In E2 type this formula(E1 is having some heading)Ĭopy E2 down. Moving on to A2, the duplicate value is found in B4 so 4th row is deleted. Starting from cell A1, the duplicate value is found in B3 so 3rd row is deleted. If the values in column1 matches any cell in column2, the row should be deleted. so KEEP THE DATA SAFELY SOME WHERE FOR EASY RETRIEVAL.Iin this sample workbook you copy the data in another sheet e.g. Compare two columns in excel and delete the duplicate row. First, (1) check Data has header row to get columns description and (2) uncheck Column D Product Price under. Google Sheets takes the whole data range into account. Set CompareRange Range ('C1:C5') NOTE: If the.

Compare two columns in excel and remove duplicates code#

Enter the following code in a module sheet: Sub FindMatches () Dim CompareRange As Variant, x As Variant, y As Variant Set CompareRange equal to the range to which you will compare the selection. (ii) xlNo The selected range has no header. Press ALT+F11 to start the Visual Basic editor. It has 3 possible values- (i) xlYes The selected range has a header.

Header To determine whether the selected range has a header or not. Click anywhere in the data range (B2:D17) and in the Menu, go to Data > Remove duplicates. Columns The array of indexes that represents column numbers from where we want to remove duplicates. This is a hybrid soltuion-sheet solution and macro You can also remove duplicate rows based on one or more columns in Google Sheets.