Solved

Excel 2010 macros/vba

Posted on 2016-10-04
7
38 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 10

Assisted Solution

by:HuaMinChen
HuaMinChen earned 125 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 8

Assisted Solution

by:Koen
Koen earned 125 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 43

Accepted Solution

by:
Saqib Husain, Syed earned 250 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 10

Expert Comment

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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
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 in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

863 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now