As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.
Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.
Course Of The Month
Become a Premium Member and unlock a new, free course in leading technologies each month.
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
![[Live Webinar] The Cloud Skills Gap](https://filedb.experts-exchange.com/files/public/2017/5/29/3249c5dc-214e-4f8f-afab-ef354630e19f.png)
Active 1 day ago
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
As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.
Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
A little background as to how I came to I design this code:
Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Do you use a spreadsheet like Microsoft's Excel? Have you ever wanted to link out to a non excel file on your computer or network drive? This is the way I found to do it!
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month6 days, 15 hours left to enroll
622 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.