• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Macro to Rename Duplicates

jgj1988

New Member
I've built a macro to pull specific data out of a standardized template. The macro inserts a column into column A of the template sheet with "=B1&C1" formulas in order to create more specific identifiers for use with Vlookups. It turns out some of the cells in B1 and C1 are sometimes the same.

What would I need to do to make my macro say something similar to:

if any cells in column A are duplicates then make the 2nd duplicate cell formula =B1&C1&-1 and then the thrid duplicate =B1&C1&-2 and so on.

Could even add the "-1" to the first duplicate, if that would be easier as long as there are no duplicate cells left.

Thanks for any help,

James
 
Hi James ,

You can make it easier for others to just add something to your macro instead of coding the whole thing , if you can post your macro.

Narayan
 
Hi, jgj1988!

Give a look at the uploaded file.

Adopting the criteria of 0 for the original value, 1 for the 1st duplicate and so on, adapt this snippet to your macro:
Code:
    Dim I As Integer, A As String
    For I = 2 To <whatsoeverisyourmaxrosnumber>
        A = "=RC[1]&RC[2]&""-""&" & _
            Application.WorksheetFunction.CountIfs( _
                    Range(Cells(1, 2), Cells(I, 2)), Cells(I, 2), _
                    Range(Cells(1, 3), Cells(I, 3)), Cells(I, 3)) - 1
        Cells(I, 1).FormulaR1C1 = A
    Next I

But you'll have an ambiguity when different chained values from B & C columns retrieve the same value (yellow shaded rows, i.e., B2=11 & C2=2 vs. B4=1 & C4=12.

To fix this use this other code which embeds another minus sign for column separation:
Code:
Sub Y()
    Dim I As Integer, A As String
    For I = 2 To 10
        A = "=RC[1]&""-""&RC[2]&""-""&" & _
            Application.WorksheetFunction.CountIfs( _
                    Range(Cells(1, 2), Cells(I, 2)), Cells(I, 2), _
                    Range(Cells(1, 3), Cells(I, 3)), Cells(I, 3)) - 1
        Cells(I, 1).FormulaR1C1 = A
    Next I
End Sub

Just advise if any issue.

Regards!
 

Attachments

  • Macro to Rename Duplicates (for jgj1988 at chandoo.org).xlsm
    16.1 KB · Views: 3
Thanks SirJB7, this is very helpful. I went with the first macro you sent me since my data will be text and the ambiguity you mentioned will be extremely rare.

But I was hoping you could help explain how the range for searching for duplicates works. The way my template is setup for looking for data is that there are two sections, a "current scenario" on top and a "proposed scenario" underneath. The text columns in these two sections are identical, its the data in the columns after that changes and is what I am using the vlookups looking for. The locations(Rows) of this data changes based on the template but I have been able to set variables in order to separate this data. But I only want to find duplicates within the current scenario and then the proposed scenario. Does this make sense?
 
Code:
Worksheets("sku detail").Range("a:a").EntireColumn.Insert



    Range("a7:a" & findtotal).FormulaR1C1 = "=RC[+1]& RC[+2]"
     findtotalz = WorksheetFunction.Match("Total Current Scenario", [c:c], False)
     findend = WorksheetFunction.Match("Total Proposed Scenario", [c:c], False)
         
      Dim Y As Integer, A As String
     
    For Y = 2 To findtotal
        A = "=RC[1]&RC[2]&""-""&" & _
            Application.WorksheetFunction.CountIfs( _
               Range(Cells(1, 2), Cells(Y, 2)), Cells(Y, 2), _
                Range(Cells(1, 3), Cells(Y, 3)), Cells(Y, 3)) - 1
                Cells(Y, 1).FormulaR1C1 = A
    Next Y
         
   Range("a" & findtotalz, "a" & findend).FormulaR1C1 = "=RC[+1]& RC[+2]&""P"""
   
    For H = findtotal To findend
        A = "=RC[1]&RC[2]&""P""&""-""&" & _
            Application.WorksheetFunction.CountIfs( _
                    Range(Cells(H, 2), Cells(H, 2)), Cells(H, 2), _
                    Range(Cells(H, 3), Cells(H, 3)), Cells(H, 3)) - 1
        Cells(H, 1).FormulaR1C1 = A
    Next H
 

Attachments

  • details.xlsx
    9.7 KB · Views: 2
I attached a piece of my code, and some of the adjustments i tried to make for it to work. in this case i only want to look for duplicates in cells a4 - a11 and then again from a22-a30.
 
Back
Top