Solved

Excel 2010 macros/vba

Posted on 2016-10-04
7
51 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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

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,…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

860 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