Excel - Count Duplicates with user specified columns

Question

0.00/5 (No votes)

See more:

I am trying to create a macro in excel that prompts a user to input a column to check and a column to output the results to.

for example I would like to check column a for duplicates and output a count to column f

I've tried everyhting but cannot get this working:

Sub LookForDuplicates()

Dim LastRow As Long

Dim column1 As String
    'display an input box asking for column
    column1 = InputBox( _
    "Please enter column to ckeck")
    'if no file name chosen, say so and stop
    If Len(column1) = 0 Then
    MsgBox "No column entered"
    
    Exit Sub
    End If

Dim column2 As String
    'display an input box asking for column
    column2 = InputBox( _
    "Please enter column to insert results")
    'if no file name chosen, say so and stop
    If Len(column2) = 0 Then
    MsgBox "No column entered"

    Exit Sub
    End If

'-------------------------------------------------------

'This is the original version of my script with set columns which works great.....

  'LastRow = Range("B" & Rows.Count).End(xlUp).Row
  '   With Range("E1")
  '  .FormulaR1C1 = "=COUNTIF(C2,RC[-3])"
  '  .AutoFill Destination:=Range("E1:E" & LastRow)
  '   Range("E1").Select
  '  ActiveCell.FormulaR1C1 = "Duplicates"
 '-----------------------------------------------------   
    LastRow = Range(column1 & Rows.Count).End(xlUp).Row
     With Range(column2 & "1")
    .FormulaR1C1 = "=COUNTIF(C2,RC[-3])"
    .AutoFill Destination:=Range(column2 & "1" & ":" & column2 & LastRow)
     Range(column2 & "1").Select
    ActiveCell.FormulaR1C1 = "Duplicates"
    
  End With
End Sub

When the script runs it prompts for the column to check and the column for the output as expected but if fills the column with zero's even though there are duplicates.

It seems to be an issue with the fill formula, which ever column is selected to check for duplicates the formula output always reads:

=COUNTIF($B:$B,E2)

It makes no difference which column I selected to check this one should of been A not B. As soon as I change it to A it works, but it will not fill correctly.

So i guess this line:

.FormulaR1C1 = "=COUNTIF(C2,RC[-3])"

Is incorrect but I cannot get it working.

Posted 5-Mar-13 3:46am

Paul Hayman

Updated 5-Mar-13 23:40pm

v3

Add a Solution

Comments

ZurdoDev 5-Mar-13 10:23am

What specifically is not working?

Paul Hayman 5-Mar-13 10:26am

Sorry:

When the script runs it prompts for the column to check and the column for the output as expected but if fills the column with zero's even though there are duplicates.

2 solutions

Solution 1

First of all, you need to know something about cell referencing: Overview of formulas[^]. Then have a look here at: COUNTIF formula[^]. Define criteria as constant calue or absolute reference to the cell.

Posted 6-Mar-13 9:58am

Maciej Los

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Paul Hayman · Accepted Answer · 2013-03-06T21:46:00

Solution if anybody else might find this useful:

The issue was even though column 1 was entered as a Column Reference H for example the COUNTIF function required this as a numeric reference so added an extra variable on the column1 value to the numeric value and modified the formula to suit. All working now:

Dim LastRow As Long

Dim column1 As String
    'display an input box asking for column
    column1 = InputBox( _
    "Please enter column to ckeck")
    'if no file name chosen, say so and stop
    ColumnNumber = Columns(column1).Column
 '   MsgBox ColumnNumber
    If Len(column1) = 0 Then
    MsgBox "No column entered"
    
    
    Exit Sub
    End If

Dim column2 As String
    'display an input box asking for column
    column2 = InputBox( _
    "Please enter column to insert results")
    'if no file name chosen, say so and stop
    If Len(column2) = 0 Then
    MsgBox "No column entered"

    Exit Sub
    End If

'-------------------------------------------------------

'This is the original version of my script with set columns which works great.....

  'LastRow = Range("B" & Rows.Count).End(xlUp).Row
  '   With Range("E1")
  '  .FormulaR1C1 = "=COUNTIF(C2,RC[-3])"
  '  .AutoFill Destination:=Range("E1:E" & LastRow)
  '   Range("E1").Select
  '  ActiveCell.FormulaR1C1 = "Duplicates"
 '-----------------------------------------------------
    LastRow = Range(column1 & Rows.Count).End(xlUp).Row
     With Range(column2 & "1")
    .FormulaR1C1 = "=COUNTIF(C" & ColumnNumber & ",C" & ColumnNumber & ")"
    .AutoFill Destination:=Range(column2 & "1" & ":" & column2 & LastRow)
     Range(column2 & "1").Select
    ActiveCell.FormulaR1C1 = "Duplicates"
    
  End With
End Sub