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.

"Henk" wrote:

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

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.

"AdamV" wrote:

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.

"Henk" wrote:

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

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,RO W())))
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
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Henk" wrote:
.. 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

Max,

Perfect solution!

tHenks


"Max" wrote:

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,RO W())))
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
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Henk" wrote:
.. 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

Welcome, Henk. Glad you liked it, and thanks for feeding back.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Henk" wrote in message
...
Max,

Perfect solution!

tHenks

Make sure your list range is only column A.

Henk wrote:

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

--

Dave Peterson

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.

"Dave Peterson" wrote:

Make sure your list range is only column A.

Henk wrote:

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

--

Dave Peterson

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.

Henk wrote:

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.

"Dave Peterson" wrote:

Make sure your list range is only column A.

Henk wrote:

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

--

Dave Peterson


--

Dave Peterson