+ Reply to Thread
Results 1 to 11 of 11

Extracting a multiple-row subset from a table

  1. #1
    Registered User
    Join Date
    06-20-2022
    Location
    London
    MS-Off Ver
    365
    Posts
    3

    Extracting a multiple-row subset from a table

    Hi

    This seems such a basic thing but I can't find a solution!

    I need to extract a subset of rows from a table.

    Example:
    Each visitor is given a unique vistor ID which is used every time the visit (in the example this is Vis01 to Vis06).

    I only want to extract selected columns but for all of the entries for Vis01, Vis02 and Vis05 (these are listed in another table).

    Thanks!
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: Extracting a multiple-row subset from a table

    How about
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  3. #3
    Forum Contributor
    Join Date
    03-05-2018
    Location
    Tbilisi, Georgia
    MS-Off Ver
    2013, 2016
    Posts
    271

    Re: Extracting a multiple-row subset from a table

    Formulas. (suitable for excel 2013 and above)
    PHP Code: 
    =IFERROR(INDEX(Table1[VisitorID],MATCH(1,INDEX((COUNTIF(G$15:G15,Table1[VisitorID])=COUNTIF(Table1[VisitorID],Table1[VisitorID]))+ISNA(MATCH(Table1[VisitorID],{"Vis03","Vis04","Vis06"},)),),)),""
    PHP Code: 
    =IFERROR(INDEX(Table1[[VisitDate]:[Score]],AGGREGATE(15,6,ROW(Table1[#All])/(Table1[[VisitorID]:[VisitorID]]=$G16),COUNTIF($G$16:$G16,$G16)),MATCH(H$15,Table1[[#Headers],[VisitDate]:[Score]],0)),"") 
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    06-20-2022
    Location
    London
    MS-Off Ver
    365
    Posts
    3

    Re: Extracting a multiple-row subset from a table

    Thanks Strogg - that seems to work but it seems to do it by excluding unwanted rows.

    My source table has about 7000 rows and I need to extract about 1000 unique 'visitors' store in another table.

  5. #5
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: Extracting a multiple-row subset from a table

    Did you try the formula I suggested?

  6. #6
    Forum Contributor
    Join Date
    03-05-2018
    Location
    Tbilisi, Georgia
    MS-Off Ver
    2013, 2016
    Posts
    271

    Re: Extracting a multiple-row subset from a table

    but it seems to do it by excluding unwanted rows.
    Values from the range, not rows. Assuming you don't need some values I put them into exclusion list. You can easily replace the constants in curly brackets with the cell references and write there as much exclusions as you need. But, I'm not sure how fast this will work with more than 7000 rows, so try Fluff's solution as well.
    Last edited by T.I.; 06-21-2022 at 04:55 PM.

  7. #7
    Forum Expert
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    2,807

    Re: Extracting a multiple-row subset from a table

    @Fluff's solution should work. To sort like you show in your file, just wrap the SORT formula around it:

    =SORT(LET(f,FILTER(Table1,(Table1[VisitorID]="Vis01")+(Table1[VisitorID]="Vis02")+(Table1[VisitorID]="Vis05")),INDEX(f,SEQUENCE(ROWS(f)),{1,2,5,6})),1,1)

    Other options to give you the same result:

    =SORT(FILTER(FILTER(Table1,(Table1[VisitorID]="Vis01")+(Table1[VisitorID]="Vis02")+(Table1[VisitorID]="Vis05")),{1,1,0,0,1,1}),1,1)

    or:
    =SORT(FILTER(CHOOSE({1,2,3,4},Table1[VisitorID],Table1[VisitDate],Table1[Project],Table1[Score]),(Table1[VisitorID]="Vis01")+(Table1[VisitorID]="Vis02")+(Table1[VisitorID]="Vis05")),1,1)

  8. #8
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Extracting a multiple-row subset from a table

    Another approach

    Make a list in L2:M4 with the criteria

    After that :

    G2 =IFERROR(VLOOKUP(A2,$L$2:$M$4,2,0),"") and drag down.

    After that:

    Filter on column G on "yes"

    See the attached file.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  9. #9
    Registered User
    Join Date
    06-20-2022
    Location
    London
    MS-Off Ver
    365
    Posts
    3

    Re: Extracting a multiple-row subset from a table

    [SOLVED] Oeldere's suggestion worked for me.

    Thanks for all your prompt replies and suggestions

  10. #10
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: Extracting a multiple-row subset from a table

    Glad to help & thanks for the feedback.

  11. #11
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Extracting a multiple-row subset from a table

    Thanks for the rep.

    Glad I could help.

    If you are satisfied with the solution, please mark the question solved.

+ 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. Extracting multiple strings from a table based on multiple criteria
    By McKneezy in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-10-2020, 09:23 PM
  2. Extracting multiple strings from a table based on multiple criteria
    By McKneezy in forum Excel Formulas & Functions
    Replies: 20
    Last Post: 02-21-2020, 12:21 PM
  3. Extracting multiple strings from a table based on multiple criteria
    By McKneezy in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-14-2019, 04:41 AM
  4. [SOLVED] Extracting multiple strings from a table based on multiple criteria
    By McKneezy in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-10-2019, 10:54 PM
  5. [SOLVED] Searching for a value within a subset of a table
    By yukionna in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-27-2013, 09:20 AM
  6. Replies: 0
    Last Post: 09-10-2012, 09:19 AM
  7. [SOLVED] How to enter symbols for subset or element of a subset in Excel?
    By rwcita in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-23-2006, 05:30 PM

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