|
Posted to microsoft.public.excel.worksheet.functions
|
|
|
Filter unique on key fields only
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
|