+ Reply to Thread
Results 1 to 9 of 9

Formula to return a table subset

  1. #1
    Registered User
    Join Date
    03-23-2017
    Location
    Belgium
    MS-Off Ver
    2010
    Posts
    21

    Lightbulb Formula to return a table subset

    Hello everyone,

    I manage a planning where people report different activity types, let's say "Work" or "Special activity" (with different types, like 1 2 or 3).
    While keeping my main planning where everything is reported, I am looking for a way to filter out on all special activities (so: Special*), for all members.

    It means that the dates do not matter anymore, I just need the raw list of all special activities per person, all regrouped below their name.
    Example is in the below file.

    Is there a way to get this without affecting my source data?

    Many thanks!
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    12-03-2009
    Location
    Florence, Italy
    MS-Off Ver
    Excel 2019
    Posts
    1,796

    Re: Formula to return a table subset

    Hi,

    first answer

    In J2 to be copied across

    =IFERROR(INDEX(B$2:B$13,AGGREGATE(15,6,ROW($2:$13)-1/(LEFT(B$2:B$13,7)="Special"),ROWS($1:1))),"")

    I'm assuming Names in same order in B:D e J:L

    Regards
    -----------------------------------------------------

    At Excelforum, you can say "Thank you!" by clicking the star icon ("Add Reputation") below the post.

    Please, mark your thread [SOLVED] if you received your answer.

  3. #3
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    Office 365
    Posts
    3,935

    Re: Formula to return a table subset

    Try

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


    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
    If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)

  4. #4
    Forum Expert
    Join Date
    12-03-2009
    Location
    Florence, Italy
    MS-Off Ver
    Excel 2019
    Posts
    1,796

    Re: Formula to return a table subset

    Hi All,

    Names mixed up.

    Again, exploiting AGGREGATE.

    SEARCH function is maybe easier to adjust than LEFT: in J2 to be copied across

    Please Login or Register  to view this content.
    Regards

  5. #5
    Registered User
    Join Date
    03-23-2017
    Location
    Belgium
    MS-Off Ver
    2010
    Posts
    21

    Re: Formula to return a table subset

    Truly magic!
    I still have many things to learn in Excel ;-)

    If anyone would accept to explain the rationale behind the formula, that would be great.
    In the meantime, I will split the formula in chunk to understand which part does what...

    Thanks so much!

  6. #6
    Forum Expert
    Join Date
    12-03-2009
    Location
    Florence, Italy
    MS-Off Ver
    Excel 2019
    Posts
    1,796

    Re: Formula to return a table subset

    Hi,

    are you confident with any functions used in the formula? INDEX, SEARCH or SMALL(IF...AGGREGATE

    Do you know array formulas? In some of them you could happen to spot segment as

    ROW($2:$13)-ROW($A$2)+1

    This segment produces - for istance - a vector of numbers ( 1,2,3,4,5,6,7,8,9,10,11,12) to be processed by SMALL(IF or by AGGREGATE and finally read by INDEX.

    Regards
    Last edited by canapone; 03-23-2017 at 06:28 AM.

  7. #7
    Registered User
    Join Date
    03-23-2017
    Location
    Belgium
    MS-Off Ver
    2010
    Posts
    21

    Re: Formula to return a table subset

    Hi,

    I know the formulas indeed.
    Array formulas, I read a lot about but still have difficulties to get a full grip. Every time, I try but I end up using more standard formulas as a workaround ;(a shame)...

  8. #8
    Forum Expert
    Join Date
    12-03-2009
    Location
    Florence, Italy
    MS-Off Ver
    Excel 2019
    Posts
    1,796

    Re: Formula to return a table subset

    Hi,

    an example. This formula does not need array status (to be confirmed with control+shift+enter) thanks to AGGREGATE

    =IFERROR(INDEX(B$2:B$13,AGGREGATE(15,6,ROW($2:$13)-1/(LEFT(B$2:B$13,7)="Special"),ROWS($1:1))),"")

    The segment

    INDEX(B$2:B$13,

    needs a number, which row you need to return. B2 is 1, B3 is 2


    AGGREGATE(15,6,ROW($2:$13)-1/(LEFT(B$2:B$13,7)="Special"),ROWS($1:1))

    makes a selection among numbers 1-12 (ROW($2:$13)-1)

    AGGREGATE(15,6,ROW($2:$13)-1....,ROWS($1:1))

    means the SMALLEST number that meets the condition at denominator.

    (LEFT(B$2:B$13,7)="Special")


    If FALSE produces an error (number/0), the relevant number is excluded from the selection made by AGGREGATE and read by INDEX.


    AGGREGATE(15,6,ROW($2:$13)-1/(LEFT(B$2:B$13,7)="Special"),ROWS($1:1))

    The red segments means 1

    Try to copy in one cell

    =ROWS($1:1)

    and then drag down the formula.

    So the formula returns the first "SMALLEST", the second "SMALLEST"....until it returns an error because cells containing "special" are finished.

    Another example

    If you drag down the segment

    =AGGREGATE(15,6,ROW($2:$13)-1/(LEFT(B$2:B$13,7)="Special"),ROWS($1:1))

    you get the list of positions where condition

    LEFT(B$2:B$13,7)="Special"

    is met.

    SMALL(IF makes the same selection to INDEX.

    -----------------------------------------------------------------------------------

    If INDEX has to elaborate a bi-dimensional range

    =IFERROR(INDEX($B$2:$D$13,AGGREGATE(15,6,ROW($2:$13)-ROW($A$2)+1/(ISNUMBER(SEARCH("special",$B$2:$D$13))*($B$1:$D$1=J$1)),ROWS($1:1)),MATCH(J$1,$B$1:$D$1,0)),"")

    INDEX needs two numbers: row number and column number

    Row numbers from AGGREGATE (or from SMALL(IF...)

    Column number from

    MATCH(J$1,$B$1:$D$1,0)

    ....

    Just for sharing some poor piece of explanation in very poor English.
    Last edited by canapone; 03-23-2017 at 07:05 AM.

  9. #9
    Registered User
    Join Date
    03-23-2017
    Location
    Belgium
    MS-Off Ver
    2010
    Posts
    21

    Re: Formula to return a table subset

    Thanks a lot, extremely useful!

+ 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. [SOLVED] Subset of a table on a separate sheet
    By Aquamore in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-09-2014, 12:13 PM
  2. [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
  3. Analyze subset of data in table
    By holmes123 in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 03-08-2013, 02:35 AM
  4. Replies: 0
    Last Post: 09-10-2012, 09:19 AM
  5. Select Subset of Table in List Box
    By stubbsj in forum Excel Programming / VBA / Macros
    Replies: 20
    Last Post: 10-18-2011, 11:02 AM
  6. [SOLVED] Create subset table
    By Elmar in forum Excel General
    Replies: 4
    Last Post: 02-19-2009, 05:44 PM
  7. 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