unique filter help

orsm6

Active Member
Joined
Oct 3, 2012
Messages
442
Office Version
  1. 365
Platform
  1. Windows
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
Joined
May 28, 2005
Messages
58,349
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Oct 3, 2012
Messages
442
Office Version
  1. 365
Platform
  1. Windows
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
LineIDISSUEACTIONMinutesEscalatedID
HARTBLOCK2411221MeetingNil45YES451
000000
000000
000000
000000
BOSCH000000
000000
000000
000000
000000
SCHOLLE000000
000000
000000
000000
000000
MICRO000000
000000
000000
000000
000000
NOVA000000
000000
000000
000000
000000


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
1451BLOCK2411221MeetingNil45
2451BLOCK2411221MeetingNil45
3451BLOCK2411221MeetingNil45
4451BLOCK2411221MeetingNil45
5451BLOCK2411221MeetingNil45


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

orsm6

Active Member
Joined
Oct 3, 2012
Messages
442
Office Version
  1. 365
Platform
  1. Windows
my version was old by the looks.

data source
LineIDISSUEACTIONMinutesEscalatedID
HARTBLOCK2411221MeetingNil45YES451
000000
000000
000000
000000
BOSCH000000
000000
000000
000000
000000
SCHOLLE000000
000000
000000
000000
000000
MICRO000000
000000
000000
000000
000000
NOVA000000
000000
000000
000000
000000


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
1451BLOCK2411221MeetingNil45
2451BLOCK2411221MeetingNil45
3451BLOCK2411221MeetingNil45
4451BLOCK2411221MeetingNil45
5451BLOCK2411221MeetingNil45
it
my version was old by the looks.

data source
LineIDISSUEACTIONMinutesEscalatedID
HARTBLOCK2411221MeetingNil45YES451
000000
000000
000000
000000
BOSCH000000
000000
000000
000000
000000
SCHOLLE000000
000000
000000
000000
000000
MICRO000000
000000
000000
000000
000000
NOVA000000
000000
000000
000000
000000


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
1451BLOCK2411221MeetingNil45
2451BLOCK2411221MeetingNil45
3451BLOCK2411221MeetingNil45
4451BLOCK2411221MeetingNil45
5451BLOCK2411221MeetingNil45


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
Joined
May 28, 2005
Messages
58,349
Office Version
  1. 365
Platform
  1. Windows
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),"")
 
Solution

orsm6

Active Member
Joined
Oct 3, 2012
Messages
442
Office Version
  1. 365
Platform
  1. Windows
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
Joined
May 28, 2005
Messages
58,349
Office Version
  1. 365
Platform
  1. Windows
Cheers. Thanks for the follow-up.
 

Forum statistics

Threads
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?

Disable AdBlock

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
Top