Filter words

T

Therese

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
 
I

Ian

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
 
M

Max

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,ISTEXT(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,ISNUMBER(A1:A13),0)))
Copy A14 across as far as required
 
T

Therese

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
 
T

Therese

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
 
M

Max

Ah, that's much simpler

Place in A14, normal ENTER will do:
=INDEX(B1:B13,MATCH("x",A1:A13,0))
 
T

Therese

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
 
T

Therese

Hi Ian
Thanks!

Ian said:
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
Click to expand...
 
M

Max

.. 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)
 
T

Therese

Hi
Thanks anyway...see it took some time to find out the reply. Sorted
everything with a VLOOKUP.
Thanks
Hi
 
T

Therese

....oh...right!!!Oh no thanks for getting the answer. But you are right.
Prolem sorted.
haha
Hi
 
Top