+ Reply to Thread
Results 1 to 15 of 15

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

  1. #1
    Forum Contributor
    Join Date
    01-16-2014
    Location
    Poland
    MS-Off Ver
    Excel 2016-365
    Posts
    2,877

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

    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
    Attached Files Attached Files

  2. #2
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

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

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

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

    Formula: copy to clipboard
    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
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Last edited by AlKey; 08-29-2018 at 02:31 PM.
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  3. #3
    Forum Contributor
    Join Date
    01-16-2014
    Location
    Poland
    MS-Off Ver
    Excel 2016-365
    Posts
    2,877

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

    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

  4. #4
    Forum Contributor
    Join Date
    01-16-2014
    Location
    Poland
    MS-Off Ver
    Excel 2016-365
    Posts
    2,877

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

    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
    Last edited by jaryszek; 08-30-2018 at 03:02 AM. Reason: adding image

  5. #5
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    33,988

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

    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.
    Glenn



  6. #6
    Forum Contributor
    Join Date
    01-16-2014
    Location
    Poland
    MS-Off Ver
    Excel 2016-365
    Posts
    2,877

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

    Thank you Glenn,

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

    Like A1 & A2?

    Best,
    Jacek

  7. #7
    Forum Contributor
    Join Date
    08-14-2018
    Location
    Hanoi, Vietnam
    MS-Off Ver
    2016
    Posts
    118

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

    Please try
    Please Login or Register  to view this content.
    Copied down and copied right.
    Last edited by congnt92; 08-30-2018 at 04:00 AM. Reason: Typo

  8. #8
    Forum Contributor
    Join Date
    01-16-2014
    Location
    Poland
    MS-Off Ver
    Excel 2016-365
    Posts
    2,877

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

    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
    Last edited by jaryszek; 08-30-2018 at 04:17 AM.

  9. #9
    Forum Contributor
    Join Date
    08-14-2018
    Location
    Hanoi, Vietnam
    MS-Off Ver
    2016
    Posts
    118

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

    Quote Originally Posted by jaryszek View Post
    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.
    Last edited by congnt92; 08-30-2018 at 04:54 AM. Reason: typo

  10. #10
    Forum Contributor
    Join Date
    01-16-2014
    Location
    Poland
    MS-Off Ver
    Excel 2016-365
    Posts
    2,877

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

    congnt92,

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

    And your english is not bad ;-)

    Best,
    Jacek

  11. #11
    Forum Contributor
    Join Date
    01-16-2014
    Location
    Poland
    MS-Off Ver
    Excel 2016-365
    Posts
    2,877

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

    Hi,

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

    Best,
    Jacek
    Last edited by jaryszek; 08-30-2018 at 06:47 AM.

  12. #12
    Forum Contributor
    Join Date
    08-14-2018
    Location
    Hanoi, Vietnam
    MS-Off Ver
    2016
    Posts
    118

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

    Plz change it to
    Please Login or Register  to view this content.
    Capture.PNG
    HTH.
    Last edited by congnt92; 08-30-2018 at 07:27 AM. Reason: Upload IMG

  13. #13
    Forum Contributor
    Join Date
    01-16-2014
    Location
    Poland
    MS-Off Ver
    Excel 2016-365
    Posts
    2,877

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

    wow thank you!!!

    Best,
    Jacek

  14. #14
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    33,988

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

    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.

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files

  15. #15
    Forum Contributor
    Join Date
    01-16-2014
    Location
    Poland
    MS-Off Ver
    Excel 2016-365
    Posts
    2,877

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

    Glenn,

    thank you very much, awesome formula.

    Best,
    Jacek

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 40
    Last Post: 08-29-2018, 01:36 PM
  2. Extract rows from a big table based on criteria in the Same column
    By Madzilla in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 08-16-2017, 11:39 AM
  3. extracting subarray from array - subarray to be used with index/match
    By Dbauddls90 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-04-2014, 12:33 AM
  4. [SOLVED] Extract Unique Text Values based on Multiple Criteria
    By Kattenhove in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 09-26-2014, 07:59 AM
  5. Replies: 1
    Last Post: 02-14-2013, 02:32 PM
  6. Replies: 3
    Last Post: 02-14-2013, 10:25 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1