Filtering for Unique Records with multiple-column criteria

[Guest]

I can't seem to get the filter for unique records to work. I have a
spreadsheet with about 20 columns and about 3000 rows. I want to filter on
mailing addresses, which is four columns. If I create a
separate worksheet containing data only from the columns I want to filter for
the unique records, and then choose Advanced Filter, and the list range and
criteria range are the same, and click "Unique Records Only", it works. But
if I try to filter the complete worksheet with all 20 columns, and set up the
filtered columns as the criteria and all the columns as the list range, it
just gives me back the same set of rows, including duplicates.

What am I doing wrong?

 

[Guest]

Create a helper column which is a concatenation of the four columns; i.e.,
=CONCATENATE(A1,B1,C1,D1) etc and run the unique records filter on that.

Dave

 

[Debra Dalgleish]

In the criteria area, leave the heading cell blank
In the cell below, create a formula that refers to the first row of data
in the four columns. For example:

=SUMPRODUCT(--(C$2:C2=C2),--(D$22=D2),--(E$2:E2=E2),--(F$2:F2=F2))=1

When you run the Advanced Filter, for the criteria area, choose the
blank heading cell and the cell with the formula.

 

[Guest]

Thanks, Dave -- I created the concatenated column but I'm still not having
success.

These are the steps I'm taking:

1. Select all 20 columns of the worksheet.
2. Click on Advanced Filter.
3. Click on "Filter the list, in place."
4. Leave the list range as the all the cells in the spreadsheet.
5. Select the concatenated column as the Criteria range.
6. Click on "unique records only."
7. Click OK.

Am I doing something wrong?

 

[T. Valko]

Just select a single column, the column you want to filter on.

Biff

 

[Guest]

Hi

Copy all the columns to a different location in the Excel sheet. This
is basically to run an advance query which will fetch all the records in the
excel sheet even if you have misspled while typing.

Assuming that you have copied all the columns to a single spread sheet, you
know the column heading now, save the excel sheet.

Now I wiil be using the Advance filtering techniques so that all the column
information are retrieved even if the typed cells are misspelled, which
normally is not the case when you do a simple filtering technique. The
filtered values may not be correct.

To retieve the values;

1. Select all records and the column headings and define a name for the
range List. This is the List range name.
2. Select all column headings and paste in a seperate row anywhere in the
spreadsheet.
2. Select the pasted column heading along with an three empty row and define
a
name. This is the criteria range name.

Note: You can specify more criteira in addition rows.

3. Define a criteria on the second row for all columns, if necessary. For
example S* will display all details specific to "S'.
4. Click Data-> Filter-> Advanced Filter
5. Type the List name
6. Type the Criteria Name
7. Click copy to another location option - Mandatory
8. Click Copy to
9. Click the cell below the criteria range

All data will be displayed specific to the query you requested. This is very
usefull if the spreadsheet data are mistyped or mis-spelled. Now you have
done a database funtion on your excle spread sheet.

Challa Prabhu