Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Filter for unique values in multiple columns
I have a data list on 10 columns. I'd like to fitler and remove the the
duplicates based on col A only. Advance filter will filter single col. How can I keep unique codes on col A and remove all the duplicates codes in colA along with the rest of the records on the other 9 colunms? Thanks. -- when u change the way u look @ things, the things u look at change. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Filter for unique values in multiple columns
Advance Filter does not work on a range
Select your range with headers Choose Data|Filter|Advanced Filter Select Unique Records Click on Col A after clicking on Criteria Range... "sahafi" wrote: I have a data list on 10 columns. I'd like to fitler and remove the the duplicates based on col A only. Advance filter will filter single col. How can I keep unique codes on col A and remove all the duplicates codes in colA along with the rest of the records on the other 9 colunms? Thanks. -- when u change the way u look @ things, the things u look at change. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Filter for unique values in multiple columns
Try this sequence on a copy of your data sheet ..
Insert a new col B Put in B2: =IF(COUNTIF(A$2:A2,A2)1,"x","") Copy B2 down to the last row of data in col A. This flags duplicates as "x". Kill the formulas in col B with an "in-place" copy n paste special as values. Put a label into B1, apply autofilter on col B, choose: "x" from the dropdown in B1. This gives you all the duplicate rows to be deleted. Select all the filtered rows (select all the blue rowheaders), right-click Delete Row. Done. -- Max Singapore http://savefile.com/projects/236895 Downloads:19,500 Files:362 Subscribers:62 xdemechanik --- "sahafi" wrote: I have a data list on 10 columns. I'd like to fitler and remove the the duplicates based on col A only. Advance filter will filter single col. How can I keep unique codes on col A and remove all the duplicates codes in colA along with the rest of the records on the other 9 colunms? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Filter for unique values in multiple columns
Thanks Max. Accomplished exactly what I needed.
Thanks. -- when u change the way u look @ things, the things u look at change. "Max" wrote: Try this sequence on a copy of your data sheet .. Insert a new col B Put in B2: =IF(COUNTIF(A$2:A2,A2)1,"x","") Copy B2 down to the last row of data in col A. This flags duplicates as "x". Kill the formulas in col B with an "in-place" copy n paste special as values. Put a label into B1, apply autofilter on col B, choose: "x" from the dropdown in B1. This gives you all the duplicate rows to be deleted. Select all the filtered rows (select all the blue rowheaders), right-click Delete Row. Done. -- Max Singapore http://savefile.com/projects/236895 Downloads:19,500 Files:362 Subscribers:62 xdemechanik --- "sahafi" wrote: I have a data list on 10 columns. I'd like to fitler and remove the the duplicates based on col A only. Advance filter will filter single col. How can I keep unique codes on col A and remove all the duplicates codes in colA along with the rest of the records on the other 9 colunms? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Filter for unique values in multiple columns
Sorry, I meant to say it 'DOES WORK on a range'
"Sheeloo" wrote: Advance Filter does not work on a range Select your range with headers Choose Data|Filter|Advanced Filter Select Unique Records Click on Col A after clicking on Criteria Range... "sahafi" wrote: I have a data list on 10 columns. I'd like to fitler and remove the the duplicates based on col A only. Advance filter will filter single col. How can I keep unique codes on col A and remove all the duplicates codes in colA along with the rest of the records on the other 9 colunms? Thanks. -- when u change the way u look @ things, the things u look at change. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Filter for unique values in multiple columns
Sheeloo,
I have tried your method, but it didn't work for me. I tried different criteria... selected the entire col A as a criterion, then tried cell A2, A1, but nothing worked. So I used Max's way. Thanks. -- when u change the way u look @ things, the things u look at change. "Sheeloo" wrote: Sorry, I meant to say it 'DOES WORK on a range' "Sheeloo" wrote: Advance Filter does not work on a range Select your range with headers Choose Data|Filter|Advanced Filter Select Unique Records Click on Col A after clicking on Criteria Range... "sahafi" wrote: I have a data list on 10 columns. I'd like to fitler and remove the the duplicates based on col A only. Advance filter will filter single col. How can I keep unique codes on col A and remove all the duplicates codes in colA along with the rest of the records on the other 9 colunms? Thanks. -- when u change the way u look @ things, the things u look at change. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Filter for unique values in multiple columns
Thanks Max. Accomplished exactly what I needed.
Welcome. Glad it did. -- Max Singapore http://savefile.com/projects/236895 Downloads:19,500 Files:362 Subscribers:62 xdemechanik --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
List Unique Values from multiple columns | Excel Discussion (Misc queries) | |||
List unique Values from different columns: How to... | Excel Discussion (Misc queries) | |||
Filter for Unique Values not working | Excel Discussion (Misc queries) | |||
count unique values & filter | Excel Worksheet Functions | |||
How do I get unique values from 2 columns? | Excel Discussion (Misc queries) |