unique records formula

[MP]

=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,

 

[Domenic]

=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!

 

[MP]

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$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

 

[Domenic]

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

ROWS($C$2:C2)

with

1

Hope this helps!

 

[MP]

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




I appreciate your help!

 

[Domenic]

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?

 

[MP]

Sure, No formula in C1 just the filtered title "Products".

C2:C2042 contain various product names
D22042 contain various product values
both are on the same worksheet. Sheet 1 "ProdData"

 

[Domenic]

Since you haven't provided me with the cell references and the name of
the worksheet containing the formulas returning the results, try the
following....

On a separate sheet...

B2, confirmed with CONTROL+SHIFT+ENTER:

=SUM(IF(FREQUENCY(IF(SUBTOTAL(2,OFFSET(Sheet1!D22042,ROW(Sheet1!D2204
2)-ROW(Sheet1!D2),0,1)),Sheet1!D22042),IF(SUBTOTAL(2,OFFSET(Sheet1!D2
2042,ROW(Sheet1!D22042)-ROW(Sheet1!D2),0,1)),Sheet1!D22042)),1))

or

=SUM(IF(FREQUENCY(IF(SUBTOTAL(2,OFFSET(Sheet1!D22042,ROW(Sheet1!D2204
2)-ROW(Sheet1!D2),0,1)),Sheet1!D22042),Sheet1!D22042),1))

C2, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=IF(ROWS(C$2:C2)<=$B$2,LARGE(IF(ISNA(MATCH(Sheet1!$D$2:$D$2042,$C$1:C1,0)
),IF(SUBTOTAL(2,OFFSET(Sheet1!$D$2:$D$2042,ROW(Sheet1!$D$2:$D$2042)-ROW(S
heet1!$D$2),,1)),Sheet1!$D$2:$D$2042)),1),"")

Hope this helps!

 

[MP]

Hopefully this will give you alittle more info (table with desired results)

Sheet 1 Name: "Data1"

COLUMN A COLUMN B
Item Name Item Amount Top Values
Construction 90 143
Products 143 Desired Result 142
Products 62 list top 5 Unique Col B values 140
Products 143 (No Duplicates) 131
Products 62 111
Products 71
Products 62
Products 117
Products 140
Products 94
GL 131
GL 103
MFG 142
GL 107
Products 121
Construction 62

Top five value can be listed on Column D in sheet 1("Data1") or if need be

 

[Domenic]

Try...

Data2!D2, confirmed with CONTROL+SHIFT+ENTER, copied down to D6:

=LARGE(IF(ISNA(MATCH(Data1!$B$2:$B$2042,Data2!$D$11,0)),IF(SUBTOTAL(2,O
FFSET(Data1!$B$2:$B$2042,ROW(Data1!$B$2:$B$2042)-ROW(Data1!$B$2),,1)),Dat
a1!$B$2:$B$2042)),1)

....which will return the following results...

143
142
140
131
121

I'm assuming that in your results you meant 121 as the fifth unique
value, not 111. Also, note that if you have the results on the same
worksheet as the source data, the formulas should be entered either
before or after the data, not beside it. Otherwise some of the results
will be hidden when the data is filtered.

Hope this helps!