unique filter help

[orsm6]

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.

 

[Peter_SSs]

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]

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]

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]

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
	N	O	P	Q	R	S
68	Minutes	Escalated	ID
69	45	YES	451			451
70	0	0	0
71	0	0	0
72	0	YES	0
73	0	0	0
74	0	0	0
75	0	0	0
76	0	0	0
77	0	0	0
78	0	0	0
79	0	0	0
80	0	0	0
81	0	0	0
82	0	0	0
83	0	0	0
84	0	0	0
85	0	0	0
86	0	0	0
87	0	0	0
88	0	0	0
89	0	0	0
90	0	0	0
91	0	0	0
92	0	0	0
93	0	0	0
Sheet1

Cell Formulas
Range	Formula
S69	S69	=IFERROR(SORT(UNIQUE(FILTER(P69:P93,P69:P93>=1)),,-1),"")

 

[orsm6]

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
	N	O	P	Q	R	S
68	Minutes	Escalated	ID
69	45	YES	451			451
70	0	0	0
71	0	0	0
72	0	YES	0
73	0	0	0
74	0	0	0
75	0	0	0
76	0	0	0
77	0	0	0
78	0	0	0
79	0	0	0
80	0	0	0
81	0	0	0
82	0	0	0
83	0	0	0
84	0	0	0
85	0	0	0
86	0	0	0
87	0	0	0
88	0	0	0
89	0	0	0
90	0	0	0
91	0	0	0
92	0	0	0
93	0	0	0
Sheet1

Cell Formulas
Range	Formula
S69	S69	=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]

Cheers. Thanks for the follow-up.