-1

On a single table of musicians and instruments, musicians will be playing multiple instruments and multiple musicians may play each instrument.

Ultimately, I want to collect two lists: Who is playing each instrument and What each person is playing?

How can I do that using the table below?

Image of Spreadsheet Attached

Rubén
  • 34,714
  • 9
  • 70
  • 166
mrcaplan
  • 3
  • 1

1 Answers1

1

You can do this with two filter formulas:

For the first chart marking who is on each instrument:

=TRANSPOSE(FILTER(A6:A10,B6:B10="x"))

if you want to make this one more dynamic by just pointing to the instrument name you modify it to this:

=transpose(filter(A6:A10,indirect(address(6,MATCH($A16,$A$5:$H$5,))&":"&address(10,MATCH($A16,$A$5:$H$5,)))="x"))

and the second one:

=FILTER($B$5:$H$5,ISTEXT(FILTER(B5:H10,A5:A10=A20)))

enter image description here

Aurielle Perlmann
  • 5,323
  • 1
  • 15
  • 26
  • I tried to use the dynamic formula on another spreadsheet, but I'm not sure I understand all the parts. My first sheet looks like rows 5-10. My second sheet is supposed to become rows 16-17 and continue. I want it to pull the headings at A16:A17 etc. from B5:H5 and then populate the names into B16:F17 from the ones with an x from B6:H10 (there is other text in those blank cells, too). – mrcaplan Jun 08 '16 at 18:53