- #Excel find duplicates except first instance means manual
- #Excel find duplicates except first instance means code
Id = Cells(row,ReqprodIDColumnCompare).value You need to add the reference to "Microsoft Scripting Runtime" (Tools -> References) sub FindDUB()ĭim ApplicableColumn As Integer 'Column number in sheetįirstRow = 'SET HERE THE FIRST ROW OF DATA RANGE IN YOUR COLUMN
#Excel find duplicates except first instance means manual
I did this as a test and it worked fine, but the manual copying of formulae isn't ideal.
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.Determine the minimum revision number for each group.So the logic (in case it isn't already clear) is as follows: It then also needs copying down to the end of your data table. This formula can be entered as normal, with a simple Enter.
The formula for Column G, that you type into Cell G2 is =MIN(IF(B:B=B2,C:C)), but again this needs to be entered with Ctrl-Shift-Enter to make it work properly.ĭrag the formulae for Columns F and G down to the end of your data, and they should populate with the lowest/ highest revision numbers per group.įrom this we can finally add the formula to fill in the Status column, which goes into cell E2 as =IF(F2=G2, "", IF(C2=G2,"Removed",IF(C2=F2,"Applicable",""))). The formula for column F, that you type into Cell F2 is =MAX(IF(B:B=B2,C:C)), but you have to hit Ctrl-Shift-Enter to add this formula. I then added column headers for E = Status, F = Max and G = Min. So Column A has nothing in it, Column B is your Product Id, Column C is your revision number and Column D is the owner. If you wanted to do this using a formula then you could do the following, but note that this is very manual in nature.įirst I created some sample data as follows: A B C D Range("E" & innerRow).Value = "Applicable" If CLng(Range("C" & innerRow).Value) firstValue Then If CLng(Range("B" & innerRow).Value) = currentID Then If Range("E" & currentRow).Value = "Removed" Or Range("E" & currentRow).Value = "Applicable" ThenĬurrentID = CLng(Range("B" & currentRow).Value)įirstValue = CLng(Range("C" & currentRow).Value) Range("E" & currentRow).Value = "Removed" LastRow = ThisWorkbook.Worksheets("Tabelle1").Range("B" & Rows.Count).End(xlUp).Rowįrequency = (Range("B:B"), Range("B" & currentRow).Value) 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". Then i could find the duplicates but not indicate which one of them that has the highest revision.
#Excel find duplicates except first instance means code
This code is not stable and it does not work now, It worked before if MatchRevision and If CompareRevision MatchRevision Then function is removed. ' Cells(MatchReqprodID, ApplicableColumn) = "Applicable"Ĭells(CompareReqprodID, ApplicableColumn) = "Applicable"Ĭells(MatchReqprodID, ApplicableColumn) = "Removed" ' Cells(CompareReqprodID, ApplicableColumn) = "Removed" 'if the match index is not equals to current row number, then it is a duplicate value 'MatchRevision = (RevisionColumnCompare, 1) MatchReqprodID = WorksheetFunction.Match(Cells(CompareReqprodID, ReqprodIDColumnCompare), Range("B1:B" & lastRow), 0) 'getting match index number for the value of the cell If Cells(CompareReqprodID, ReqprodIDColumnCompare) "" Then 'skipping if it is blank. LastRow = Range("B" & Rows.Count).End(xlUp).Row I want i to look like this:ĭim lastRow As Long 'Declaring the lastRow variableĭim MatchReqprodID As Long 'store the match index values of the given valueĭim MatchRevision As Double 'store the match index values of the given valueĭim RevisionColumnCompare As Integer 'Column number in sheetĭim ReqprodIDColumnCompare As Integer 'Column number in sheetĭim CompareReqprodID As Long 'Compare is to loop through all the records in the column using For loopĭim CompareRevision As Long 'Compare is to loop through all the records in the column using For loop 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.
Hey I'm trying to filter/match my sheet for duplicates and i have two criterias:
0 kommentar(er)
