Results 1 to 10 of 10

Thread: Help with creating an array formula to produce 2 data lists with a single text

  1. #1
    Seeker Chi's Avatar
    Join Date
    Jun 2015
    Posts
    17
    Articles
    0
    Excel Version
    excel 2013

    Post Help with creating an array formula to produce 2 data lists with a single text



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I've been trying to find a formula that would produce 2 data lists from entering a text in a cell i.e a name. For example, in my extract sample shown below, there's a list of staff names in the 1st column, across the top are dates the staff members have worked and the 3 letter codes represents the jobs that they worked on on those dates. Is there a formula, perhaps an array formula, that would produce 2 data lists with the 1st list showing the dates the staff member worked and the 2nd list showing the jobs that they worked on on those dates, with only having to enter a staff name in a cell to produce these lists? Is this possible to create? and if so, how can this be done?


    01-Jan 02-Jan 03-Jan
    Tom LIB ONC ROT
    Mark SHO WES QUE
    Sam SEV ROT CAR
    Emma NEA SHO MEA


    Any assistance would be most appreciated


    Chi x

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,518
    Articles
    0
    Excel Version
    Excel 2016
    assuming your table is in A15 and you enter the name of interest into say, G1,

    then in G2 enter formula:

    =IFERROR(INDEX($B$1:$D$1,SMALL(IF(INDEX($B$2:$D$5,MATCH($G$1,$A$2:$A$5,0),0)<>"",COLUMN($B$1:$D$1)-COLUMN($B$1)+1),ROWS($G$1:$G1))),"")

    confirmed with CTRL+SHIFT+ENTER not just ENTER
    , then copied down same number of rows as there are date columns in your table. You may have to format the result as Date to match the table headers.

    in H2 enter:

    =IF(G2="","",INDEX($B$2:$D$5,MATCH($G$1,$A$2:$A$5,0),MATCH(G2,$B$1:$D$1,0)))

    copied down same number of rows.
    Attached Files Attached Files
    Last edited by NBVC; 2015-06-23 at 05:02 PM.


  3. #3
    Seeker Chi's Avatar
    Join Date
    Jun 2015
    Posts
    17
    Articles
    0
    Excel Version
    excel 2013
    Thank you so much NBVC for your help. The array formulas worked a dream!!!!

    Thanks again

    Chi x

  4. #4
    Seeker Chi's Avatar
    Join Date
    Jun 2015
    Posts
    17
    Articles
    0
    Excel Version
    excel 2013
    Quote Originally Posted by NBVC View Post
    assuming your table is in A15 and you enter the name of interest into say, G1,

    then in G2 enter formula:

    =IFERROR(INDEX($B$1:$D$1,SMALL(IF(INDEX($B$2:$D$5,MATCH($G$1,$A$2:$A$5,0),0)<>"",COLUMN($B$1:$D$1)-COLUMN($B$1)+1),ROWS($G$1:$G1))),"")

    confirmed with CTRL+SHIFT+ENTER not just ENTER
    , then copied down same number of rows as there are date columns in your table. You may have to format the result as Date to match the table headers.

    in H2 enter:

    =IF(G2="","",INDEX($B$2:$D$5,MATCH($G$1,$A$2:$A$5,0),MATCH(G2,$B$1:$D$1,0)))

    copied down same number of rows.


    Thank you so much NBVC for your help. The array formulas worked a dream!!!!

    Thanks again

    Chi x

  5. #5
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,518
    Articles
    0
    Excel Version
    Excel 2016
    You are very welcome, Chi

    Thanks for the positive feedback.


  6. #6
    Seeker Chi's Avatar
    Join Date
    Jun 2015
    Posts
    17
    Articles
    0
    Excel Version
    excel 2013
    Quote Originally Posted by NBVC View Post
    You are very welcome, Chi

    Thanks for the positive feedback.


    Hi NBVC

    I was just wandering if the above array formulas, you created, could be modified slightly to show only dates and jobs that staff members worked on and exclude, from the 2 data lists, the dates that they were "OFF". So for example, if we looked at my previous extract sample, it showed Tom as having worked on 1st Jan on job "LIB" but if I changed it to show "OFF" on that day then it shouldn't appear on the data list. Is this possible to do?

    I hope that all makes sense.


    Chi x

  7. #7
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,518
    Articles
    0
    Excel Version
    Excel 2016
    So instead of there being blanks in the table, there will be the "OFF" string?

    if so, then just replace the null quotes in the first formula with the string "OFF"

    e.g.

    =IFERROR(INDEX($B$1:$D$1,SMALL(IF(INDEX($B$2:$D$5,MATCH($G$1,$A$2:$A$5,0),0)<>"OFF",COLUMN($B$1:$D$1)-COLUMN($B$1)+1),ROWS($G$1:$G1))),"")

    confirmed with CTRL+SHIFT+ENTERED and copied down.

    The second formula is the same.


  8. #8
    Seeker Chi's Avatar
    Join Date
    Jun 2015
    Posts
    17
    Articles
    0
    Excel Version
    excel 2013
    Once again, thank you so much NBVC for your help. You're truly an Excel Guru!


    Chi x
    Last edited by NBVC; 2015-06-26 at 07:35 PM. Reason: Removed quoted text

  9. #9
    Seeker Chi's Avatar
    Join Date
    Jun 2015
    Posts
    17
    Articles
    0
    Excel Version
    excel 2013
    [QUOTE=NBVC;19043]So instead of there being blanks in the table, there will be the "OFF" string?

    if so, then just replace the null quotes in the first formula with the string "OFF"

    e.g.

    =IFERROR(INDEX($B$1:$D$1,SMALL(IF(INDEX($B$2:$D$5,MATCH($G$1,$A$2:$A$5,0),0)<>"OFF"[COLOR=#0000ff],COLUMN($B$1:$D$1)


    Hi NBVC

    I forgot to ask before, how do you include a 2nd <>"" into the above formula. I tried using the AND function but that didn't work. Any thoughts?


    Chi x

  10. #10
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,518
    Articles
    0
    Excel Version
    Excel 2016
    =iferror(index($b$1:$d$1,small(if((index($b$2:$d$5,match($g$1,$a$2:$a$5,0),0)<>"off")*(index($b$2:$d$5,match($g$1,$a$2:$a$5,0),0)<>""),column($b$1:$d$1)-column($b$1)+1),rows($g$1:$g1))),"")

Posting Permissions

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