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

C

Chi

New member
Joined
Jun 23, 2015
Messages
29
Reaction score
0
Points
3
Excel Version(s)
excel 2013
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-Jan02-Jan03-Jan
TomLIBONCROT
MarkSHOWESQUE
SamSEVROTCAR
EmmaNEASHOMEA



Any assistance would be most appreciated :wave:


Chi x
 
assuming your table is in A1:D5 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.
 

Attachments

  • Sample.xlsx
    8.9 KB · Views: 15
Last edited:
Thank you so much NBVC for your help. The array formulas worked a dream!!!!

Thanks again :)

Chi x
 
NBVC said:
assuming your table is in A1:D5 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.
Click to expand...



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

Thanks again :)

Chi x
 
You are very welcome, Chi

Thanks for the positive feedback.
 
NBVC said:
You are very welcome, Chi

Thanks for the positive feedback.
Click to expand...



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


Chi x
 
Last edited by a moderator:
NBVC said:
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)


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
Click to expand...
 
=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))),"")
 
You must log in or register to reply here.
Back
Top