Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.
Solved
Excel 2010 macros/vba
Posted on 2016-10-04
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
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
2-
2
-
2
- +1
7 Comments
Active today
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
Active today
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?
Featured Post
Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Suggested Courses
Course of the Month4 days, 8 hours left to enroll
660 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.