software

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.--
umniy
------------------------------------------------------------------------
umniy's Profile: www.excelforum.com/member.php...oamp;userid=32077
View this thread: www.excelforum.com/showthread...hreadid=518306On Thu, 2 Mar 2006 10:34:24 -0600, umniy
gt; wrote:

gt;
gt;I have a range of data in multiple (10) columns with headers. Headers
gt;may read: Name Description Holder Size Length, etc. Some of the data in
gt;columns are duplicates. I want to filter only unique record pairs in
gt;columns Name and Description, extracting it to a different place in the
gt;sheet. Example of data:
gt;
gt;Name Description Holder Size Length
gt;.5 DRILL chuck 1/2 8
gt;.5 DRILL collet 1/2 8
gt;.5 REAMER collet 1/2 7
gt;.5 DRILL chuck 1/2 7
gt;etc.
gt;As you will see, entries in rows 1, 2 and 5 have the same Name and
gt;Description and may have different Holder, Size or Length. Row 4 has
gt;the same Name, but different Description, the rest may be anything.
gt;I want to filter the data, so that I will only get two rows: row 2 (or
gt;3, or 5) and 4, which have unique (.5 DRILL) and (.5 REAMER)
gt;combinations. The trick is to pull the rest of the records in rows C,
gt;D, ... along (and I do not care which row out of multiple selection I
gt;get; for instance, I would be happy to get either of three possible
gt;data combination for a result of a unique .5 DRILL pair)
gt;.5 DRILL chuck 1/2 8,
gt;.5 DRILL collet 1/2 8,
gt;.5 DRILL chuck 1/2 7
gt;
gt;If I set List Range to include all data, it treats the entries in all
gt;rows (including C, D, ...) as unique criteria. If I set List Range to
gt;just columns A and B, it does the trick, but I would not get the rest
gt;of the entries in columns C, D, ... copied.
gt;Is this posiible at all to do?
gt;Thanks for the help.

I think the best solution would be an additional helper column.
Assuming Name amp; Description are in cols. A amp; B then in the helper
column concatenate these with =A1amp;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 that
column, it works fine. But I need to copy all the rest of data in other
columns with it. No matter what I do, it does not filter, trying to
recognize unique records in all columns, not just in that helper
column.
I am starting to beleive that my task is impossible to do.--
umniy
------------------------------------------------------------------------
umniy's Profile: www.excelforum.com/member.php...oamp;userid=32077
View this thread: www.excelforum.com/showthread...hreadid=518306Once 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,quot;Duplicatequot;,quot;Uniquequot;)

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 lt;copygt; 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--
davidthegolfer
------------------------------------------------------------------------
davidthegolfer's Profile: www.excelforum.com/member.php...oamp;userid=31806
View this thread: www.excelforum.com/showthread...hreadid=518306