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

[Chi]

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

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

[Chi]

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

Thanks again

Chi x

[Chi]

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

[NBVC]

You are very welcome, Chi

Thanks for the positive feedback.

[Chi]

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

[NBVC]

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.

[Chi]

Once again, thank you so much NBVC for your help. You're truly an Excel Guru!


Chi x

[Chi]

[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

[NBVC]

=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))),"")