Compare Two Columns In Excel For Duplicates Mac

Compare Two Columns In Excel For Duplicates Code Is Not#

Compare Two Columns In Excel For Duplicates Code Is Not

Then i could find the duplicates but not indicate which one of them that has the highest revision.So my question is if itВґs how can i filter through my sheet, find the duplicates and see which of the duplicates that has the highest 'Revision' value of the two and set the highest to 'Applicable' and the lowest to 'Removed' in 'Status'. I want i to look like this:Dim lastRow As Long 'Declaring the lastRow variableDim MatchReqprodID As Long 'store the match index values of the given valueDim MatchRevision As Double 'store the match index values of the given valueDim RevisionColumnCompare As Integer 'Column number in sheetDim ReqprodIDColumnCompare As Integer 'Column number in sheetDim CompareReqprodID As Long 'Compare is to loop through all the records in the column using For loopDim CompareRevision As Long 'Compare is to loop through all the records in the column using For loopLastRow = Range('B' & Rows.Count).End(xlUp).RowIf Cells(CompareReqprodID, ReqprodIDColumnCompare) '' Then 'skipping if it is blank.'getting match index number for the value of the cellMatchReqprodID = WorksheetFunction.Match(Cells(CompareReqprodID, ReqprodIDColumnCompare), Range('B1:B' & lastRow), 0)'MatchRevision = Application.WorksheetFunction.Large(RevisionColumnCompare, 1)'if the match index is not equals to current row number, then it is a duplicate value' Cells(CompareReqprodID, ApplicableColumn) = 'Removed'' Cells(MatchReqprodID, ApplicableColumn) = 'Applicable'Cells(CompareReqprodID, ApplicableColumn) = 'Applicable'Cells(MatchReqprodID, ApplicableColumn) = 'Removed'This code is not stable and it does not work now, It worked before if MatchRevision and If CompareRevision MatchRevision Then function is removed. The one of the duplicates in (column B) that has the highest number in (column C) (number between 0-10) should still have 'Applicable' in (column E) but the lowest number should get 'Removed' in (column E) instead. Look for duplicates anywhere in (column B), if there are duplicates set 'Applicable' in (column E) on the same rows as the found duplicates.

If the current revision number matches the minimum number then this is the lowest in the set so set the status to 'Removed' If the minimum and maximum numbers are the same then there are no duplicates, so set the status to blank Determine the maximum revision number for each group

In all other cases this is in between the maximum and minimum number for the group, so set the status to blank.I did this as a test and it worked fine, but the manual copying of formulae isn't ideal.LastRow = ThisWorkbook.Worksheets('Tabelle1').Range('B' & Rows.Count).End(xlUp).RowFrequency = Application.WorksheetFunction.CountIf(Range('B:B'), Range('B' & currentRow).Value)Range('E' & currentRow).Value = 'Removed'If Range('E' & currentRow).Value = 'Removed' Or Range('E' & currentRow).Value = 'Applicable' ThenCurrentID = CLng(Range('B' & currentRow).Value)FirstValue = CLng(Range('C' & currentRow).Value)If CLng(Range('B' & innerRow).Value) = currentID ThenIf CLng(Range('C' & innerRow).Value) firstValue ThenRange('E' & innerRow).Value = 'Applicable'You need to add the reference to 'Microsoft Scripting Runtime' (Tools -> References) sub FindDUB()Dim ApplicableColumn As Integer 'Column number in sheetFirstRow = 'SET HERE THE FIRST ROW OF DATA RANGE IN YOUR COLUMNId = Cells(row,ReqprodIDColumnCompare).value'Don't know if you want to compare also EQUALS cells, change as you want it.If Cells(checkedId(id),RevisionColumnCompare).value < Cells(row,RevisionColumnCompare).valueCells(checkedId(id),ApplicableColumn).value = 'Removed'Cells(checkedId(key),ApplicableColumn).

Compare Two Columns In Excel For Duplicates Mac