Your question, your audience. Choose who sees your identity—and your question—with question security.
Excel 2010 macros/vba
I would to create two macros or vba code modules. That will check the records in a column and find duplicates. The second macro would find a duplicate and increase the sequence number by 1 in the duplicate row. I am including a sample spreadsheet below. Tab "A" I would to find just the duplicates. Tab "B" would find the duplicates and renumber the sequence number. The sequence number is in brackets.
mytest_macros.xlsx
mytest_macros.xlsx
Asked:
2-
2
-
2
- +1
3 Solutions
LVL 11
You should have loop to scan each record one by one by also assigning relevant sequence number to duplicated record.
for your B sheet:
you could just sort the records and then add a column (say B)
put formula =if(a2=a1;b1+1;0) in cell B2 and copy down
that will give you the number/occurrence (starting at zero). than add a column C where you put the formula:
=REPLACE(A2;FIND("[";A2);3
no coding needed
For A, I have no clue what you are trying to achieve
you could just sort the records and then add a column (say B)
put formula =if(a2=a1;b1+1;0) in cell B2 and copy down
that will give you the number/occurrence (starting at zero). than add a column C where you put the formula:
=REPLACE(A2;FIND("[";A2);3
no coding needed
For A, I have no clue what you are trying to achieve
For the part 2 you can use this macro
Sub numberdups()
Dim cel As Range
Dim n As Long
Dim i As Long
For i = Range("A2").End(xlDown).Ro
Set cel = Range("A" & i)
n = WorksheetFunction.CountIf(
cel.Value = Replace(cel.Value, "[0]", "[" & n & "]")
Next i
End Sub
For the first part how do you want to display the duplicates? Color them?
Sub numberdups()
Dim cel As Range
Dim n As Long
Dim i As Long
For i = Range("A2").End(xlDown).Ro
Set cel = Range("A" & i)
n = WorksheetFunction.CountIf(
cel.Value = Replace(cel.Value, "[0]", "[" & n & "]")
Next i
End Sub
For the first part how do you want to display the duplicates? Color them?
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
Join & Write a Comment Already a member? Login.
Featured Post
Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.
Tackle projects and never again get stuck behind a technical roadblock.
Join Now