Home |
Search |
Today's Posts |
#1
|
|||
|
|||
find then sort formula
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 |
#2
|
|||
|
|||
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 -- remove $$$ from email addresss to send email Robert wrote in message ... 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 |
#3
|
|||
|
|||
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). Whe 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:"&CO UNTA(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 "Robert" wrote in message ... 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 |
#4
|
|||
|
|||
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 "Biff" wrote: 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). Whe 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:"&CO UNTA(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 "Robert" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help! Formula to find the address of particular value in sheet | Excel Worksheet Functions | |||
Find and delete part of formula for multiple formulas? | Excel Worksheet Functions | |||
Need formula to find and sum values in a different spreadsheet | Excel Worksheet Functions | |||
Formula to find information between 2 dates | Excel Worksheet Functions | |||
I there an easy way to find out if any formula reference a cell? | New Users to Excel |