Microsoft Excel - How can you select and display contents of cells in cells in an array?

Asked By Simon on 25-Jul-14 11:52 AM
If I have a set of data on soccer players such as the following:

Country Player Goals Fouls
Brazil Player 1 2 0
Brazil Player 2 3 3
Brazil Player 3 4 2
Brazil Player 4 5 1
Brazil Player 5 6 1
Brazil Player 6 7 0
Brazil Player 7 8 3
Brazil Player 8 3 0
Brazil Player 9 5 0
Brazil Player 10 8 3
Germany Player 1 9 1
Germany Player 2 4 6
Germany Player 3 7 5
Germany Player 4 8 6
Germany Player 5 6 1
Germany Player 6 2 1

And I want to select and display all of the Germany player data in another range of cells elsewhere on the spreadsheet.  How would I do that?

Simon

 
Harry Boughen replied to Simon on 26-Jul-14 04:02 AM
Hello Simon,
One way would be to set up a Pivot Table.  This is probably the easiest.
If you wanted to use formulae, assuming that your table starts in cell A1, the following in M2/N2,O2 and copied down would work.
=OFFSET($A$1,MATCH("Germany",$A:$A,0)-1+ROW()-ROW(M$2),1)
=OFFSET($A$1,MATCH("Germany",$A:$A,0)-1+ROW()-ROW(M$2),2)
=OFFSET($A$1,MATCH("Germany",$A:$A,0)-1+ROW()-ROW(M$2),3)
You could make it more general by substituting a cell reference for the string and entering the country into that cell or by setting up a drop-down list of the country names.
Hope this helps but get back if you need more.
Harry
Simon replied to Harry Boughen on 28-Jul-14 10:24 AM
Thank you, i'll give it a try.

Simon