PDA

View Full Version : [SOLVED] Multiple Criteria Duplicates



Aussiebear
10-08-2014, 08:01 PM
Microsoft offers the following advice when trying to remove duplicates based on multiple criteria

Excel Developer Reference
Range.RemoveDuplicates Method
Removes duplicate values from a range of values.
Version Information
Version Added: Excel 2007

Syntax
expression.RemoveDuplicates(Columns, Header)
expression A variable that represents a Range object.

Parameters
Name Required/Optional Data Type Description
Columns Optional Variant Array of indexes of the columns that contain the duplicate information. If nothing is passed then it assumes all columns contain duplicate information.
Header Optional XlYesNoGuess Specifies whether the first row contains header information. xlNo is the default value; specify xlGuess if you want Excel to attempt to determine the header.


The following code sample removes duplicates with all columns from a range.

ActiveSheet.Range("A1:C100").RemoveDuplicates

The following code sample removes duplicates with the first 2 columns

ActiveSheet.Range("A1:C100").RemoveDuplicates Columns:=Array(1,2), Header:=xlYes

© 2007 Microsoft Corporation. All rights reserved.

Is there really no limit to the number of criteria,(other than the total number of possible columns)?

snb
10-09-2014, 03:32 AM
I assume you have something in mind that triggers your question.
Can you share ?
What if someone's answer to your question is 'yes' and what if it's 'no' ?

Aussiebear
10-09-2014, 03:35 PM
Nothing really in mind, just that most people use single criteria, a few use multiple criteria, but I've never heard of anyone wanting large multiples before. I imagine it would slow the system down.

SamT
10-09-2014, 06:59 PM
Test it for us. Enter some data in A1, Ctrl+R it across the board, then Ctrl+D it down some 30 odd rows. Then, change one cell in one row. I did it in a couple of seconds before I saw that it was a 2007 Function.

Aussiebear
10-10-2014, 03:14 AM
Tested using Excel 2011 mac version, Range A1:IZ1137 with 6 unique rows...... If that was rocket fuel, we are good to go to the end of the solar system.