ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   "Criteria Range" in the "Data/Filter/Advanced Filter" to select Du (https://www.excelbanter.com/excel-worksheet-functions/25773-%22criteria-range%22-%22data-filter-advanced-filter%22-select-du.html)

TC

"Criteria Range" in the "Data/Filter/Advanced Filter" to select Du
 
Excel 2003/ Is there a way I can use the "Criteria Range" in the
"Data/Filter/Advanced Filter" tool to select duplicates in a multi-column,
many-line spreadsheet by selecting based on only certain columns of
information. ie: columns including, Name, Address, City, State etc, as well
as columns containing data such as phone numbers which make all records
unique.

Jason Morin

TC-

Let's assume that your target columns are A, B, C, D and F. Place this in an
open cell (say J13):

=SUMPRODUCT(--(A2&B2&C2&D2&F2=$A$2:$A$500&$B$2:$B$500&$C$2:$C$50 0&$D$2:$D$500&$F$2:$F$500))1

And use $J$12:$J$13 as your criteria range, ensuring that *J12* is empty.

HTH
Jason
Atlanta, GA


"TC" wrote:

Excel 2003/ Is there a way I can use the "Criteria Range" in the
"Data/Filter/Advanced Filter" tool to select duplicates in a multi-column,
many-line spreadsheet by selecting based on only certain columns of
information. ie: columns including, Name, Address, City, State etc, as well
as columns containing data such as phone numbers which make all records
unique.



All times are GMT +1. The time now is 01:34 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com