• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 79
  • Last Modified:

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
0
centralmike
Asked:
centralmike
  • 2
  • 2
  • 2
  • +1
3 Solutions
 
HuaMin ChenSystem AnalystCommented:
You should have loop to scan each record one by one by also assigning relevant sequence number to duplicated record.
0
 
centralmikeAuthor Commented:
How would you do that? Do have a sample on how to accomplish this task?
0
 
KoenChange and Transition ManagerCommented:
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;"[" & B2 & "]")

no coding needed

For A, I have no clue what you are trying to achieve
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
Saqib Husain, SyedEngineerCommented:
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).Row To 2 Step -1
        Set cel = Range("A" & i)
        n = WorksheetFunction.CountIf(Range("A1", cel.Offset(-1)), cel.Value)
        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?
0
 
centralmikeAuthor Commented:
MACRO WORKED GREAT
0
 
Saqib Husain, SyedEngineerCommented:
What do you want to do with the first part?
0
 
HuaMin ChenSystem AnalystCommented:
Author said the codes were working fine.
0
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

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now