| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Filter words
Hi
I have columns with words in different cells, f.ex. one word in A1, one in B2 and one in C12. Is there some sort of a "sumfunction", so that I can filter all the words down into row 14 ? Thanks Therese |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Filter words
There may be a "sumfunction" as you call it, but I don't know what it is.
Are the rest of the cells in each column occupied, or blank? If blank, then =A1,A2,A3... etc will do for you. If not blank, is there some way to determine which cell you need to copy down (ie does it fit some criteria)? If it isn't possible with a formula, then it's probably possible with code, but still needs some way to determine exactly which cell to copy. Ian "Therese" wrote in message ... Hi I have columns with words in different cells, f.ex. one word in A1, one in B2 and one in C12. Is there some sort of a "sumfunction", so that I can filter all the words down into row 14 ? Thanks Therese |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Filter words
One interp ..
Assuming you want to pull into row 14, the contents of the first non blank* cell in each col within rows 1 - 13 *contents could be either text or number Put in A14 and array-enter the formula by pressing CTRL+SHIFT+ENTER: =IF(COUNTA(A1:A13)=0,"",INDEX(A1:A13,MATCH(TRUE,A1 :A13<"",0))) Copy A14 across as far as required If you want to pull only the first non blank cell containing text (ignore numbers) Put in A14 and array-enter the formula by pressing CTRL+SHIFT+ENTER: =IF(COUNTA(A1:A13)=0,"",INDEX(A1:A13,MATCH(TRUE,IS TEXT(A1:A13),0))) Copy A14 across as far as required If you want to pull only the first non blank cell containing numbers (ignore text) Put in A14 and array-enter the formula by pressing CTRL+SHIFT+ENTER: =IF(COUNT(A1:A13)=0,"",INDEX(A1:A13,MATCH(TRUE,ISN UMBER(A1:A13),0))) Copy A14 across as far as required -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Therese" wrote: Hi I have columns with words in different cells, f.ex. one word in A1, one in B2 and one in C12. Is there some sort of a "sumfunction", so that I can filter all the words down into row 14 ? Thanks Therese |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Filter words
Hi again
Thanks. Uh...I forgot to say...There are several words in each column! Next to each column I have another column with an x. I wanted something like..IF A1:A13=x, then the word in column B right next to the x, would come out in A:14. Oh no...hope I make it understandable!! Therese "Ian" wrote: There may be a "sumfunction" as you call it, but I don't know what it is. Are the rest of the cells in each column occupied, or blank? If blank, then =A1,A2,A3... etc will do for you. If not blank, is there some way to determine which cell you need to copy down (ie does it fit some criteria)? If it isn't possible with a formula, then it's probably possible with code, but still needs some way to determine exactly which cell to copy. Ian "Therese" wrote in message ... Hi I have columns with words in different cells, f.ex. one word in A1, one in B2 and one in C12. Is there some sort of a "sumfunction", so that I can filter all the words down into row 14 ? Thanks Therese |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Filter words
Hi again
Thanks. Uh...I forgot to say...There are several words in each column! Next to each column I have another column with an x. I wanted something like..IF A1:A13=x, then the word in column B right next to the x, would come out in A:14. Oh no...hope I make it understandable!! Therese "Max" wrote: One interp .. Assuming you want to pull into row 14, the contents of the first non blank* cell in each col within rows 1 - 13 *contents could be either text or number Put in A14 and array-enter the formula by pressing CTRL+SHIFT+ENTER: =IF(COUNTA(A1:A13)=0,"",INDEX(A1:A13,MATCH(TRUE,A1 :A13<"",0))) Copy A14 across as far as required If you want to pull only the first non blank cell containing text (ignore numbers) Put in A14 and array-enter the formula by pressing CTRL+SHIFT+ENTER: =IF(COUNTA(A1:A13)=0,"",INDEX(A1:A13,MATCH(TRUE,IS TEXT(A1:A13),0))) Copy A14 across as far as required If you want to pull only the first non blank cell containing numbers (ignore text) Put in A14 and array-enter the formula by pressing CTRL+SHIFT+ENTER: =IF(COUNT(A1:A13)=0,"",INDEX(A1:A13,MATCH(TRUE,ISN UMBER(A1:A13),0))) Copy A14 across as far as required -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Therese" wrote: Hi I have columns with words in different cells, f.ex. one word in A1, one in B2 and one in C12. Is there some sort of a "sumfunction", so that I can filter all the words down into row 14 ? Thanks Therese |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Filter words
Ah, that's much simpler
Place in A14, normal ENTER will do: =INDEX(B1:B13,MATCH("x",A1:A13,0)) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Therese" wrote: Hi again Thanks. Uh...I forgot to say...There are several words in each column! Next to each column I have another column with an x. I wanted something like..IF A1:A13=x, then the word in column B right next to the x, would come out in A:14. Oh no...hope I make it understandable!! Therese |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Filter words
Hi Max
Took some time...sorry. Have been trying a while with your tip, but I can't find the "MATCH"-function on a danish computer...unless...is it in the newest excel-version ? Thanks a lot!! Merry x-mas THerese "Max" wrote: Ah, that's much simpler Place in A14, normal ENTER will do: =INDEX(B1:B13,MATCH("x",A1:A13,0)) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Therese" wrote: Hi again Thanks. Uh...I forgot to say...There are several words in each column! Next to each column I have another column with an x. I wanted something like..IF A1:A13=x, then the word in column B right next to the x, would come out in A:14. Oh no...hope I make it understandable!! Therese |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Filter words
Hi Ian
Thanks! "Ian" wrote: There may be a "sumfunction" as you call it, but I don't know what it is. Are the rest of the cells in each column occupied, or blank? If blank, then =A1,A2,A3... etc will do for you. If not blank, is there some way to determine which cell you need to copy down (ie does it fit some criteria)? If it isn't possible with a formula, then it's probably possible with code, but still needs some way to determine exactly which cell to copy. Ian "Therese" wrote in message ... Hi I have columns with words in different cells, f.ex. one word in A1, one in B2 and one in C12. Is there some sort of a "sumfunction", so that I can filter all the words down into row 14 ? Thanks Therese |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Filter words
.. can't find the "MATCH"-function on a danish computer
Not sure, sorry. One guess from some google trawl, perhaps SAMMENLIGN ? MATCH is not a new function - it has been around since xl97 (my 1st ver) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Therese" wrote in message ... Hi Max Took some time...sorry. Have been trying a while with your tip, but I can't find the "MATCH"-function on a danish computer...unless...is it in the newest excel-version ? Thanks a lot!! Merry x-mas THerese |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Filter words
Hi
Thanks anyway...see it took some time to find out the reply. Sorted everything with a VLOOKUP. Thanks Hi "Max" wrote: .. can't find the "MATCH"-function on a danish computer Not sure, sorry. One guess from some google trawl, perhaps SAMMENLIGN ? MATCH is not a new function - it has been around since xl97 (my 1st ver) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Therese" wrote in message ... Hi Max Took some time...sorry. Have been trying a while with your tip, but I can't find the "MATCH"-function on a danish computer...unless...is it in the newest excel-version ? Thanks a lot!! Merry x-mas THerese |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Filter words
....oh...right!!!Oh no thanks for getting the answer. But you are right.
Prolem sorted. haha Hi "Max" wrote: .. can't find the "MATCH"-function on a danish computer Not sure, sorry. One guess from some google trawl, perhaps SAMMENLIGN ? MATCH is not a new function - it has been around since xl97 (my 1st ver) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Therese" wrote in message ... Hi Max Took some time...sorry. Have been trying a while with your tip, but I can't find the "MATCH"-function on a danish computer...unless...is it in the newest excel-version ? Thanks a lot!! Merry x-mas THerese |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Can i "filter" in excel by words in Bold? | Excel Worksheet Functions | |||
| Excel auto filter doesn't recoginize case - won't filter AA from A | Excel Discussion (Misc queries) | |||
| Long list of words to find with Filter | Excel Discussion (Misc queries) | |||
| how do i insert words into a column without erasing the words | Excel Discussion (Misc queries) | |||
| "Criteria Range" in the "Data/Filter/Advanced Filter" to select Du | Excel Worksheet Functions | |||
Linear Mode
