Hi.
I have a block of cells in one part that containts text.
On rows below there are in column A text that I want the other columns
in that row to search for in the above block, and then enter into that
particular cell the text that is in column A in the row that it finds
it's text in (what a gibberish... Se attachment:

I want the cell C28 to search for the content in A28 ("PS") in the
cells C11:C28 and if it finds the content, as in cell C27, write in
cell C28 the content of cell A27 "Chöl".
If it found more hits like if it looked for "OM" it would write in the
cell "Tarm, Tarm, Chöl" or "Tarm Tarm Chöl"
If it finds nothing it would write nothing.
And then I'd copy the formula to all the other cells C28,30,32,34,
D28,30,32 etc...

Is there someway to do this.
COUNTIF and MATCH only works if the content matches, not if it is
among other text..


+-------------------------------------------------------------------+
|Filename: excel.jpg |
|Download: http://www.excelforum.com/attachment.php?postid=4862 |
+-------------------------------------------------------------------+

--
Ruatha
------------------------------------------------------------------------
Ruatha's Profile: http://www.excelforum.com/member.php...o&userid=31083
View this thread: http://www.excelforum.com/showthread...hreadid=549423

I can't download your attachment, but the SEARCH and FIND functions
allow you to look for sub-strings - FIND is case sensitive.

Hope this helps.

Pete

Ruatha wrote:
Hi.
I have a block of cells in one part that containts text.
On rows below there are in column A text that I want the other columns
in that row to search for in the above block, and then enter into that
particular cell the text that is in column A in the row that it finds
it's text in (what a gibberish... Se attachment:

I want the cell C28 to search for the content in A28 ("PS") in the
cells C11:C28 and if it finds the content, as in cell C27, write in
cell C28 the content of cell A27 "Chöl".
If it found more hits like if it looked for "OM" it would write in the
cell "Tarm, Tarm, Chöl" or "Tarm Tarm Chöl"
If it finds nothing it would write nothing.
And then I'd copy the formula to all the other cells C28,30,32,34,
D28,30,32 etc...

Is there someway to do this.
COUNTIF and MATCH only works if the content matches, not if it is
among other text..


+-------------------------------------------------------------------+
|Filename: excel.jpg |
|Download: http://www.excelforum.com/attachment.php?postid=4862 |
+-------------------------------------------------------------------+

--
Ruatha
------------------------------------------------------------------------
Ruatha's Profile: http://www.excelforum.com/member.php...o&userid=31083
View this thread: http://www.excelforum.com/showthread...hreadid=549423

It cannot be done with plain formulas. It needs some VBA macro code.
Please zip your workbook and post the zip as attachment.

HTH
--
AP

"Ruatha" a écrit dans
le message de news: ...

Hi.
I have a block of cells in one part that containts text.
On rows below there are in column A text that I want the other columns
in that row to search for in the above block, and then enter into that
particular cell the text that is in column A in the row that it finds
it's text in (what a gibberish... Se attachment:

I want the cell C28 to search for the content in A28 ("PS") in the
cells C11:C28 and if it finds the content, as in cell C27, write in
cell C28 the content of cell A27 "Chöl".
If it found more hits like if it looked for "OM" it would write in the
cell "Tarm, Tarm, Chöl" or "Tarm Tarm Chöl"
If it finds nothing it would write nothing.
And then I'd copy the formula to all the other cells C28,30,32,34,
D28,30,32 etc...

Is there someway to do this.
COUNTIF and MATCH only works if the content matches, not if it is
among other text..


+-------------------------------------------------------------------+
|Filename: excel.jpg |
|Download: http://www.excelforum.com/attachment.php?postid=4862 |
+-------------------------------------------------------------------+

--
Ruatha
------------------------------------------------------------------------
Ruatha's Profile:
http://www.excelforum.com/member.php...o&userid=31083
View this thread: http://www.excelforum.com/showthread...hreadid=549423

Well it can be done, but can it be done easier??

Right now I'd have to do like this:

Code:
--------------------

=IF(ISERROR(SEARCH($A28;$C$27));"";$A27)&IF(ISERRO R(SEARCH($A28;$C$26));"";","&$A$26)&IF(ISERROR(SEA RCH($A28;$C$25));"";","&$A$25)

--------------------

And repeat that 17 times instead of 3, or 17x2 if I want the D column
aswell..

Is there some easier way to do it as a function??


--
Ruatha
------------------------------------------------------------------------
Ruatha's Profile: http://www.excelforum.com/member.php...o&userid=31083
View this thread: http://www.excelforum.com/showthread...hreadid=549423