Solved

Excel 2010 macros/vba

Posted on 2016-10-04
7
36 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

705 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

15 Experts available now in Live!

Get 1:1 Help Now