# unique filter help

#### orsm6

##### Active Member
Hi all - for some reason i cannot get this list to update dynamically.

range N69:P93 contains data source:
- col N has minutes (could contain duplicate numbers)
- col O contains YES or 0
- col P has a formula - basically to check for a duplicate in N - if so the number is assigned a digiti e.g. if there are 2 x 45 minutes col P returns a 451 in first instance, then 452 in the next.

otherwise if conditions not met in P... result is just 0

i am then trying to sort column P dynamically by formula highest to lowest using this formula: IFERROR(SORT(UNIQUE(FILTER(P69:P93,P69:P93>=1)),,-1),"")

it seems to convert itself to an array.
it will work sometimes, but we have been noticing that if there is only 1 entry in the minute column... the highest to lowest top 5 all result with the same info.
it should only show the one result and rest be blank.

i cant get my plugin working sorry, and i can't share the workbook. so hoping you may be able to help.

### Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

#### Peter_SSs

##### MrExcel MVP, Moderator
i cant get my plugin working
At what point during the XL2BB Instructions do you run into a problem and what are the symptoms of that problem?

i can't share the workbook.
Surely a small dummy workbook with ..
range N69:P93 contains data source:
- col N has minutes (could contain duplicate numbers)
- col O contains YES or 0
- col P has a formula - basically to check for a duplicate in N - if so the number is assigned a digiti e.g. if there are 2 x 45 minutes col P returns a 451 in first instance, then 452 in the next.
.. would not contain any sensitive information.

#### orsm6

##### Active Member
At what point during the XL2BB Instructions do you run into a problem and what are the symptoms of that problem?

Surely a small dummy workbook with ..

.. would not contain any sensitive information.
my version was old by the looks.

data source
 LineID ISSUE ACTION Minutes Escalated ID HART BLOCK2411221 Meeting Nil 45 YES 451 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 BOSCH 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 SCHOLLE 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 MICRO 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 NOVA 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0

here is where i am trying to return the highest - lowest from data source. you can see in data source there was only one instance of a yes, but it fills all top 5 lines, should only return one line, the rest should show as 0
 1 451 BLOCK2411221 Meeting Nil 45 2 451 BLOCK2411221 Meeting Nil 45 3 451 BLOCK2411221 Meeting Nil 45 4 451 BLOCK2411221 Meeting Nil 45 5 451 BLOCK2411221 Meeting Nil 45

it also doesn't seem to update dynamically i think. not sure.

#### orsm6

##### Active Member
my version was old by the looks.

data source
 LineID ISSUE ACTION Minutes Escalated ID HART BLOCK2411221 Meeting Nil 45 YES 451 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 BOSCH 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 SCHOLLE 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 MICRO 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 NOVA 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0

here is where i am trying to return the highest - lowest from data source. you can see in data source there was only one instance of a yes, but it fills all top 5 lines, should only return one line, the rest should show as 0
 1 451 BLOCK2411221 Meeting Nil 45 2 451 BLOCK2411221 Meeting Nil 45 3 451 BLOCK2411221 Meeting Nil 45 4 451 BLOCK2411221 Meeting Nil 45 5 451 BLOCK2411221 Meeting Nil 45
it
my version was old by the looks.

data source
 LineID ISSUE ACTION Minutes Escalated ID HART BLOCK2411221 Meeting Nil 45 YES 451 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 BOSCH 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 SCHOLLE 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 MICRO 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 NOVA 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0

here is where i am trying to return the highest - lowest from data source. you can see in data source there was only one instance of a yes, but it fills all top 5 lines, should only return one line, the rest should show as 0
 1 451 BLOCK2411221 Meeting Nil 45 2 451 BLOCK2411221 Meeting Nil 45 3 451 BLOCK2411221 Meeting Nil 45 4 451 BLOCK2411221 Meeting Nil 45 5 451 BLOCK2411221 Meeting Nil 45

it also doesn't seem to update dynamically i think. not sure.
if it makes any difference - this workbook is on a network drive and is shared.

the formula i am having issues with is =IFERROR(SORT(UNIQUE(FILTER(P69:P93,P69:P93>=1)),,-1),"")

#### Peter_SSs

##### MrExcel MVP, Moderator
Glad you got XL2BB working. However, for the future, it would be easier for helpers if you used 'Mini Sheet' rather than 'Table Only" as we could then see the column and row labels as well as any formulas used.

Based on that sample, no formulas shown, your formula seems to work for me, returning just a single instance of 451, not 5 of them.

orsm6.xlsm
NOPQRS
68MinutesEscalatedID
6945YES451451
70000
71000
720YES0
73000
74000
75000
76000
77000
78000
79000
80000
81000
82000
83000
84000
85000
86000
87000
88000
89000
90000
91000
92000
93000
Sheet1
Cell Formulas
RangeFormula
S69S69=IFERROR(SORT(UNIQUE(FILTER(P69:P93,P69:P93>=1)),,-1),"")

#### orsm6

##### Active Member
Glad you got XL2BB working. However, for the future, it would be easier for helpers if you used 'Mini Sheet' rather than 'Table Only" as we could then see the column and row labels as well as any formulas used.

Based on that sample, no formulas shown, your formula seems to work for me, returning just a single instance of 451, not 5 of them.

orsm6.xlsm
NOPQRS
68MinutesEscalatedID
6945YES451451
70000
71000
720YES0
73000
74000
75000
76000
77000
78000
79000
80000
81000
82000
83000
84000
85000
86000
87000
88000
89000
90000
91000
92000
93000
Sheet1
Cell Formulas
RangeFormula
S69S69=IFERROR(SORT(UNIQUE(FILTER(P69:P93,P69:P93>=1)),,-1),"")
Thanks Peter.

i tried and tried to uninstall the old BB addin then the new one, but it would never load up when i started excel. then when i got it to work while i had excel open it crashed my system when i tried to create a mini sheet.

you are right in that the formula works, it works for me too.....in the small table i shared (second column) i always only ever entered the formula in the first row because it is supposed to "detect" the changes in the source data and accordingly it would populate more results. i saw that the formula was copied to each cell of that table and for some reason it was also as an array.

I will monitor it and see how it goes,

thanks again

#### Peter_SSs

##### MrExcel MVP, Moderator
Cheers. Thanks for the follow-up.

Replies
2
Views
108
Replies
26
Views
404
Replies
3
Views
142
Replies
5
Views
180
Replies
2
Views
244

1,186,004
Messages
5,955,264
Members
438,188
Latest member
DLJ

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back