Extract subarray from table column based on criteria matched - text values

[jaryszek]

Hi,

i want to extract list of text values based on criteria from table (using structure table references).
Can you help?

In attachment please find sample workbook.

Best,
Jacek

[AlKey]

Try this:
Enter array formula in H2 and drag formula across and down

**Must be entered with Ctrl+Shift+Enter key combination.

Please Login or Register  to view this content.

*If your region uses semicolon as separator instead of comma, replace all instances of comma with semicolon.

With use of structure table references

Please Login or Register  to view this content.

[jaryszek]

Hi,

this is working like a charm.
Thank you.

To eliminate "ROW(H$4)" i used

Please Login or Register  to view this content.

Best,
Jacek

[jaryszek]

Ok, one more issue here.

It is possible to restrict list for two requirements?
Like Server1 & Server2?

So filter based on more than one criteria?

Screenshot_3.png

Please help,
Jacek

[Glenn Kennedy]

Try:

=IFERROR(INDEX(t_MaxMemory[Offered],AGGREGATE(15,6,(ROW(t_MaxMemory[Offered])-ROW(t_MaxMemory[[#Headers],[Offered]]))/(t_MaxMemory[Server]={"Server1","Server2"}),ROWS($1:1))),"")

copied down. ordinary formula.

[jaryszek]

Thank you Glenn,

can i instead using : {"Server1","Server2"} use reference to cells?

Like A1 & A2?

Best,
Jacek

[congnt92]

Please try

Please Login or Register  to view this content.

Copied down and copied right.

[jaryszek]

Thank you congnt92.

Hmm i do not understand exactly why are you doing by multiplying ROW * 0...

And furthermore when you have not sorted list, your function will not work.

Server1 Text1
Server1 Text2
Server2 Text3
Server2 Text4
Server2 Text5
Server3 Text6
Server2 Text7

edit: Glenn, your formula is not working at all. I have only 1 value as "Text1"

Please Login or Register  to view this content.

Best,
Jacek

[congnt92]

Thank you congnt92.

Hmm i do not understand exactly why are you doing by multiplying ROW * 0...

And furthermore when you have not sorted list, your function will not work.

Server1 Text1
Server1 Text2
Server2 Text3
Server2 Text4
Server2 Text5
Server3 Text6
Server2 Text7

Best,
Jacek

Hi Jacek,
It works for me even if data was not sorted.
Capture.PNG
And "0" is a part of

Please Login or Register  to view this content.

SEARCH($C$5:$C$11,H$1) will search for all values in $C$5:$C$11 within text in H1 and it returns the position or NA(), the position could be 1,2,3 ... but we just want to return all of it to 1 so we use "^0" to do that.
Aggregate with option 6 will remove all error values from

Please Login or Register  to view this content.

so we have only valid values.
My English is very bad so I can't explain more detail.
HTH.

[jaryszek]

congnt92,

thank you, my mistake this is working like a charm!

And your english is not bad ;-)

Best,
Jacek

[jaryszek]

Hi,

How can i eliminate duplicates here? (within formula of course)

Best,
Jacek

[congnt92]

Plz change it to

Please Login or Register  to view this content.

Capture.PNG
HTH.

[jaryszek]

wow thank you!!!

Best,
Jacek

[Glenn Kennedy]

Slight delay in replying!! My formula worked fine when the OR critieria were hard coded in to the formula with {}. They also work fine if the server names are in a ROW. But not, without some modification, if they are in a column. see the attached sheet. Ther version across at column X is probably best for you. It could allow you to select the server names from DD lists that you could set up in column W (no need to concatentate names)... it's happy with blank cells. The COUNTIF phrase allows the return of unique values only.

Please Login or Register  to view this content.

[jaryszek]

Glenn,

thank you very much, awesome formula.

Best,
Jacek