Filter unique on key fields only

  • Thread starter Thread starter Henk
  • Start date Start date
H

Henk

I know how Advanced Filter Unique works, but I would like to have something
different. Suppose I have a little table like this :

Henk 1 4 6 7
Koos 2 5 7 1
Toos 4 3 2 4
Henk 1 5 7 3

If I use Advanced Filter Unique records, the result will be the same as the
tabel itself, because the table contains unique records only. But I want to
see Henk only once with data 1 4 6 7, or with 1 5 7 3. E.g.

Henk 1 4 6 7
Koos 2 5 7 1
Toos 4 3 2 4

How do I do this?

Thanks in advance filter,

Henk
 
Old but kuldgy way - only filter the single column of names (copied to a new
location) then use VLOOKUP (or other techniques such as OFFSET/MATCH) to pull
the other values in for each name

if you have Excel 2007 you can use the "Remove duplicates" function on the
data ribbon which allows you to define which fields to check for dupes, in
your case you tick just the name column and the second instance of Henk would
be removed.
 
Make sure your list range is only column A.
I know how Advanced Filter Unique works, but I would like to have something
different. Suppose I have a little table like this :

Henk 1 4 6 7
Koos 2 5 7 1
Toos 4 3 2 4
Henk 1 5 7 3

If I use Advanced Filter Unique records, the result will be the same as the
tabel itself, because the table contains unique records only. But I want to
see Henk only once with data 1 4 6 7, or with 1 5 7 3. E.g.

Henk 1 4 6 7
Koos 2 5 7 1
Toos 4 3 2 4

How do I do this?

Thanks in advance filter,

Henk
Click to expand...
 
Adam,

That is exactly what I did (VLOOKUP). I do have Excel 2007 and I know about
the beautiful "Remove duplicates" button. But I am training some people that
use Excel 2003.

Anyhow,

tHenks for your reply.
 
Dave,

I that works, but that is exactly what I do not want. I want the data next
to the unique Henk (one ore the other), not only Henk.

Anyhow, tHenks for your reply.
 
If you're filtering to a new location, then try...

Use the advanced filter, but filter in place (list range still just column A)
Then copy the visible rows to the new location.
 
Try this little number, which extracts the uniques* dynamically into adjacent
cols to the right ..
*1st occurences of the key col (names)

Source data assumed in cols A to E, from row1 down, with key col = col A
(names)

In G1:
=IF(A1="","",IF(COUNTIF(A$1:A1,A1)>1,"",ROW()))

In H1:
=IF(ROW()>COUNT($G:$G),"",INDEX(A:A,SMALL($G:$G,ROW())))
Copy H1 to K1. Select G1:K1, fill down to cover the max expected extent of
source data, say down to K200. Minimize/hide away col G. Cols H to K will
auto-return the expected results, all neatly bunched at the top.
 
Max,

Perfect solution!

tHenks


Max said:
Try this little number, which extracts the uniques* dynamically into adjacent
cols to the right ..
*1st occurences of the key col (names)

Source data assumed in cols A to E, from row1 down, with key col = col A
(names)

In G1:
=IF(A1="","",IF(COUNTIF(A$1:A1,A1)>1,"",ROW()))

In H1:
=IF(ROW()>COUNT($G:$G),"",INDEX(A:A,SMALL($G:$G,ROW())))
Copy H1 to K1. Select G1:K1, fill down to cover the max expected extent of
source data, say down to K200. Minimize/hide away col G. Cols H to K will
auto-return the expected results, all neatly bunched at the top.
Click to expand...
 
Back
Top