?
Solved

Excel 2010 macros/vba

Posted on 2016-10-04
7
Medium Priority
?
77 Views
Last Modified: 2016-10-25
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
Comment
Question by:centralmike
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 11

Assisted Solution

by:HuaMinChen
HuaMinChen earned 500 total points (awarded by participants)
ID: 41829182
You should have loop to scan each record one by one by also assigning relevant sequence number to duplicated record.
0
 

Author Comment

by:centralmike
ID: 41829222
How would you do that? Do have a sample on how to accomplish this task?
0
 
LVL 9

Assisted Solution

by:Koen
Koen earned 500 total points (awarded by participants)
ID: 41829395
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
Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

 
LVL 43

Accepted Solution

by:
Saqib Husain, Syed earned 1000 total points (awarded by participants)
ID: 41831536
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
 

Author Comment

by:centralmike
ID: 41833025
MACRO WORKED GREAT
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 41833029
What do you want to do with the first part?
0
 
LVL 11

Expert Comment

by:HuaMinChen
ID: 41858244
Author said the codes were working fine.
0

Featured Post

Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

594 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question