| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula help...find values and grouping together...
I would like a formula that finds all the occurances in the cells in column F
that have the word NHL in them and return the text that is in the corresponding cell in column T. Thank you. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula help...find values and grouping together...
One way
Assume data in cols F and T running in row2 down In U2: =IF(F2="NHL",ROW(),"") Leave U1 blank In V2: =IF(ROWS($1:1)COUNT(U:U),"",INDEX(T:T,SMALL(U:U,R OWS($1:1)))) Select U2:V2, copy down to cover the max expected extent of data in col F. Hide away col U. Col V returns the required results all neatly bunched at the top. If the text "NHL" could be part of a text string in col F, eg: NHL champion use this instead in U2, copy down: =IF(ISNUMBER(SEARCH("NHL",F2)),ROW(),"") -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Jambruins" wrote: I would like a formula that finds all the occurances in the cells in column F that have the word NHL in them and return the text that is in the corresponding cell in column T. Thank you. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula help...find values and grouping together...
Thanks, that works great.
"Max" wrote: One way Assume data in cols F and T running in row2 down In U2: =IF(F2="NHL",ROW(),"") Leave U1 blank In V2: =IF(ROWS($1:1)COUNT(U:U),"",INDEX(T:T,SMALL(U:U,R OWS($1:1)))) Select U2:V2, copy down to cover the max expected extent of data in col F. Hide away col U. Col V returns the required results all neatly bunched at the top. If the text "NHL" could be part of a text string in col F, eg: NHL champion use this instead in U2, copy down: =IF(ISNUMBER(SEARCH("NHL",F2)),ROW(),"") -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Jambruins" wrote: I would like a formula that finds all the occurances in the cells in column F that have the word NHL in them and return the text that is in the corresponding cell in column T. Thank you. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula help...find values and grouping together...
welcome, glad to hear that.
-- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Jambruins" wrote in message ... Thanks, that works great. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Grouping Corresponding Values | Excel Discussion (Misc queries) | |||
| Formula to find equal values with blank cells | Excel Discussion (Misc queries) | |||
| Formula to find greater of two values??? | Excel Discussion (Misc queries) | |||
| Can you Find and Replace values with in a formula? | Excel Discussion (Misc queries) | |||
| Need formula to find and sum values in a different spreadsheet | Excel Worksheet Functions | |||
Linear Mode
