find then sort formula

[Guest]

I have a list of team members numbers in one column and their corrosponding
team numbers in another column. I would like display the teams seperately on
other sheets automatically. I would like a formula that will search the first
sheet, identify all team 1 members for example, and sort them numerically on
a seperate team sheet. Previously, someone gave me this formula but I can't
get it to work:
=INDEX(S7:S18,SMALL(IF(U7:U18=I2,ROW(S7:S18),ROW(1:1)),1)) (at this point,
this is just a tester, so doesn't actually point to a different sheet)
any tips would be appreciated,
Robert

 

[R.VENKATARAMAN]

hope I have understood the problem.

use autofilter and copy, operate from the sheet where you want to copy.
you can prepare a vba programme to find members of each of the teams.
just create macros and modify later into vba programme

another line of apprach will be to sort accoding to the column contiaining
team numbers

 

[Biff]

Hi!

Try one of these:

All are array formulas and need to be entered using the key combo of
CTRL,SHIFT,ENTER.

=INDEX(S$7:S$18,SMALL(IF(U$7:U$18=I$2,ROW($1:$12)),ROW(1:1)))

One of the problems with your formula was this portion:

ROW(S7:S18)

You'll notice that in my version I have something completely different:
ROW($1:$12)

ROW($1:$12) refers to the size of your range S7:S18. That range contains 12
elements, thus: ROW($1:$12). Where:

S7 = element 1
S8 = element 2
S9 = element 3
...
S18 = element 12

In the ROW() function the column designation is ignored so that ROW(S7:S18)
evaluates to ROW(7:18). In your formula you used ROW(S7:S18) so when this
was processed it started with element 7 and ignored the first 6.

Using this method of ROW - ROW($1:$12) is my preferred choice but there are
a few ways to do this. I'll explain below in succession.

The only problem with using the above formula is that when copied down and
all the data that meets the conditons is extracted, the formula will return
errors. You can build into the formula a means to suppress these errors.
This is generally referred to as error trapping. The above formula with an
error trap:

=IF(ROWS($1:1)<=COUNTIF(U$7:U$18,I$2),INDEX($S$7:$S$18,SMALL(IF($U$7:$U$18=$I$2,ROW($1:$12)),ROW(1:1))),"")

This formula will return a blank in place of the error value. This is just
one method of error trapping. There is another that is more widely used but
the method above is more efficient and results in a slightly shorter
formula.

Now, the formula with the error trap and a couple of different methods of
expressing the ROW($1:$12) portion:

=IF(ROWS($1:1)<=COUNTIF(U$7:U$18,I$2),INDEX($S$7:$S$18,SMALL(IF($U$7:$U$18=$I$2,(ROW(S$7:S$18)-ROW(S$7))+1),ROW(1:1))),"")

This method is probably less confussing than the method I prefer because it
uses the the range reference that "looks" familiar but evaluates to the
exact same thing. With each iteration of the array calculation:

(ROW(S$7:S$18)-ROW(S$7))+1)

Evaluates to:

ROW 7 minus 7 plus 1 = 1
ROW 8 minus 7 plus 1 = 2
ROW 9 minus 7 plus 1 =3
...
ROW 18 minus 7 plus 1 = 12

Another method of expressing ROW($1:$12):

=IF(ROWS($1:1)<=COUNTIF(U$7:U$18,I$2),INDEX($S$7:$S$18,SMALL(IF($U$7:$U$18=$I$2,ROW(INDIRECT("1:"&COUNTA(S$7:S$18)))),ROW(1:1))),"")

This uses the INDIRECT function along with COUNTA and litterally counts the
the number of entries in the range. This method is no good if you might have
empty cells within the range.

Ok, gotta go!

Biff

 

[Guest]

Biff, thanks this is working for me as I was hoping. Now I will just have to
update the master sheet and the team sheets follow suit automatically. I'm
stoked!
Robert