filtering unique in multiple columns

  • Thread starter Thread starter umniy
  • Start date Start date
U

umniy

I have a range of data in multiple (10) columns with headers. Header
may read: Name Description Holder Size Length, etc. Some of the data i
columns are duplicates. I want to filter only unique record pairs i
columns Name and Description, extracting it to a different place in th
sheet. Example of data:

Name Description Holder Size Length
.5 DRILL chuck 1/2 8
.5 DRILL collet 1/2 8
.5 REAMER collet 1/2 7
.5 DRILL chuck 1/2 7
etc.
As you will see, entries in rows 1, 2 and 5 have the same Name an
Description and may have different Holder, Size or Length. Row 4 ha
the same Name, but different Description, the rest may be anything.
I want to filter the data, so that I will only get two rows: row 2 (o
3, or 5) and 4, which have unique (.5 DRILL) and (.5 REAMER
combinations. The trick is to pull the rest of the records in rows C
D, ... along (and I do not care which row out of multiple selection
get; for instance, I would be happy to get either of three possibl
data combination for a result of a unique .5 DRILL pair)
.5 DRILL chuck 1/2 8,
.5 DRILL collet 1/2 8,
.5 DRILL chuck 1/2 7

If I set List Range to include all data, it treats the entries in al
rows (including C, D, ...) as unique criteria. If I set List Range t
just columns A and B, it does the trick, but I would not get the res
of the entries in columns C, D, ... copied.
Is this posiible at all to do?
Thanks for the help
 
I have a range of data in multiple (10) columns with headers. Headers
may read: Name Description Holder Size Length, etc. Some of the data in
columns are duplicates. I want to filter only unique record pairs in
columns Name and Description, extracting it to a different place in the
sheet. Example of data:

Name Description Holder Size Length
.5 DRILL chuck 1/2 8
.5 DRILL collet 1/2 8
.5 REAMER collet 1/2 7
.5 DRILL chuck 1/2 7
etc.
As you will see, entries in rows 1, 2 and 5 have the same Name and
Description and may have different Holder, Size or Length. Row 4 has
the same Name, but different Description, the rest may be anything.
I want to filter the data, so that I will only get two rows: row 2 (or
3, or 5) and 4, which have unique (.5 DRILL) and (.5 REAMER)
combinations. The trick is to pull the rest of the records in rows C,
D, ... along (and I do not care which row out of multiple selection I
get; for instance, I would be happy to get either of three possible
data combination for a result of a unique .5 DRILL pair)
.5 DRILL chuck 1/2 8,
.5 DRILL collet 1/2 8,
.5 DRILL chuck 1/2 7

If I set List Range to include all data, it treats the entries in all
rows (including C, D, ...) as unique criteria. If I set List Range to
just columns A and B, it does the trick, but I would not get the rest
of the entries in columns C, D, ... copied.
Is this posiible at all to do?
Thanks for the help.
Click to expand...

I think the best solution would be an additional helper column.
Assuming Name & Description are in cols. A & B then in the helper
column concatenate these with =A1&B1 then copy this down your list.

Now you can do a unique filter on this helper column.

HTH
Richard Buttrey
__
 
Thanks for the help,
I concatenated data in columns A and B. When I filter data only in tha
column, it works fine. But I need to copy all the rest of data in othe
columns with it. No matter what I do, it does not filter, trying t
recognize unique records in all columns, not just in that helpe
column.
I am starting to beleive that my task is impossible to do
 
Once you have added the helper column to join A1 and B1, you can then
sort all the data using this field - I assume this is field 11, or
column K. You can then add another helper column L and enter this
formula in L2:

=IF(K2=K1,"Duplicate","Unique")

Copy this formula down and then apply autofilter to this column and
select Unique. You can then highlight all the visible rows for the
first 10 columns, click <copy> then move somewhere else (another
sheet?) and paste your data - only the visible data will be pasted, and
of course you will have data in all your columns as you requested.

Hope this helps.

Pete
 
I too had been struggling with Filter/Advanced/Unique and could not
understand why it wouldn't work. I eventually gave up, searched here
and used your technique. Worked for me.
Thank you very much.
Kind regards
David
 
Back
Top