=IF(ROWS($A$3:A3)<=$A$1,LARGE(IF(SUBTOTAL(2,
OFFSET($C$11:$C$16,ROW($C$11:$C$16)-ROW($C$11),,1)),
$C$11:$C$16),ROWS($A$3:A3)),"")

I need to alter the above formula to remove the duplicates and list the top
(five) highest unique records only.

The table is already in auto filter mode.

Thanks,

In article ,
MP wrote:

=IF(ROWS($A$3:A3)<=$A$1,LARGE(IF(SUBTOTAL(2,
OFFSET($C$11:$C$16,ROW($C$11:$C$16)-ROW($C$11),,1)),
$C$11:$C$16),ROWS($A$3:A3)),"")

I need to alter the above formula to remove the duplicates and list the top
(five) highest unique records only.

The table is already in auto filter mode.

Thanks,

Try...

=IF(ROWS($A$3:A3)<=$A$1,LARGE(IF(ISNA(MATCH($C$11: $C$16,$A$2:A2,0)),IF(SU
BTOTAL(2,OFFSET($C$11:$C$16,ROW($C$11:$C$16)-ROW($C$11),,1)),$C$11:$C$16)
),ROWS($A$3:A3)),"")

....confirmed with CONTROL+SHIFT+ENTER. Then, I think, the formula for
A1 needs to change as well.

Hope this helps!

Thanks for the reply however i could not get the formula to work. Maybe i
missed something trying to adjust the formula per the data in my table.

=IF(ROWS($C$2:C2)<=$C$1,LARGE(IF(ISNA(MATCH($D$2:$ D$2042,$C$1:C1,0)),IF(SUBTOTAL(2,OFFSET($D$2:$D$20 42,ROW($D$2:$D$2042)-ROW($D$2),,1)),$D$2:$D$2042)),ROWS($C$2:C2)),"")

Column C: Product Name
Column D: Value

Thanks for your assistance

"Domenic" wrote:

In article ,
MP wrote:

=IF(ROWS($A$3:A3)<=$A$1,LARGE(IF(SUBTOTAL(2,
OFFSET($C$11:$C$16,ROW($C$11:$C$16)-ROW($C$11),,1)),
$C$11:$C$16),ROWS($A$3:A3)),"")

I need to alter the above formula to remove the duplicates and list the top
(five) highest unique records only.

The table is already in auto filter mode.

Thanks,

Try...

=IF(ROWS($A$3:A3)<=$A$1,LARGE(IF(ISNA(MATCH($C$11: $C$16,$A$2:A2,0)),IF(SUBTOTAL(2,OFFSET($C$11:$C$16 ,ROW($C$11:$C$16)-ROW($C$11),,1)),$C$11:$C$16)),ROWS($A$3:A3)),"")

....confirmed with CONTROL+SHIFT+ENTER. Then, I think, the formula for
A1 needs to change as well.

Hope this helps!

Sorry, my mistake! Try replacing the last instance of...

ROWS($C$2:C2)

with

1

Hope this helps!

In article ,
MP wrote:

Thanks for the reply however i could not get the formula to work. Maybe i
missed something trying to adjust the formula per the data in my table.

=IF(ROWS($C$2:C2)<=$C$1,LARGE(IF(ISNA(MATCH($D$2:$ D$2042,$C$1:C1,0)),IF(SUBTOT
AL(2,OFFSET($D$2:$D$2042,ROW($D$2:$D$2042)-ROW($D$2),,1)),$D$2:$D$2042)),ROWS(
$C$2:C2)),"")

Column C: Product Name
Column D: Value

Thanks for your assistance

I changed the last instance and could not get the fomula to work properly.
Still list the duplicate values.




I appreciate your help!

"Domenic" wrote:

Sorry, my mistake! Try replacing the last instance of...

ROWS($C$2:C2)

with

1

Hope this helps!

In article ,
MP wrote:

Thanks for the reply however i could not get the formula to work. Maybe i
missed something trying to adjust the formula per the data in my table.

=IF(ROWS($C$2:C2)<=$C$1,LARGE(IF(ISNA(MATCH($D$2:$ D$2042,$C$1:C1,0)),IF(SUBTOT
AL(2,OFFSET($D$2:$D$2042,ROW($D$2:$D$2042)-ROW($D$2),,1)),$D$2:$D$2042)),ROWS(
$C$2:C2)),"")

Column C: Product Name
Column D: Value

Thanks for your assistance

Can you post the formula contained in C1? Also, can you confirm which
cells and worksheet contain the data, and which cells and worksheet
contain the formulas?

In article ,
MP wrote:

I changed the last instance and could not get the fomula to work properly.
Still list the duplicate values.




I appreciate your help!

"Domenic" wrote:

Sorry, my mistake! Try replacing the last instance of...

ROWS($C$2:C2)

with

1

Hope this helps!

In article ,
MP wrote:

Thanks for the reply however i could not get the formula to work. Maybe i
missed something trying to adjust the formula per the data in my table.

=IF(ROWS($C$2:C2)<=$C$1,LARGE(IF(ISNA(MATCH($D$2:$ D$2042,$C$1:C1,0)),IF(SU
BTOT
AL(2,OFFSET($D$2:$D$2042,ROW($D$2:$D$2042)-ROW($D$2),,1)),$D$2:$D$2042)),R
OWS(
$C$2:C2)),"")

Column C: Product Name
Column D: Value

Thanks for your assistance