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

1. ## Help with creating an array formula to produce 2 data lists with a single text

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

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

Thanks again

Chi x

4. Originally Posted by NBVC
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. You are very welcome, Chi

Thanks for the positive feedback.

6. Originally Posted by NBVC
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. 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. Once again, thank you so much NBVC for your help. You're truly an Excel Guru!

Chi x

9. [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. =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
•