software

I'm trying to use Excel to create a basic rhyming dictionary. I have
one worksheet for the query and results and another worksheet holding
the word data. The data worksheet contains numerous rows of up to 30
words each that all rhyme with each other, e.g. row 1 = brick, chick,
flick; row 2 = dock, mock, clock; row 3 = career, veneer, appear (etc
etc). My idea is that when the query term is found anywhere in the data
sheet, all the words from the row it is found in are returned on the
query/results sheet. For instance if the search term is quot;dockquot;, the
results in this example would be mock and clock. However I can't seem
to get LOOKUP to return the values of all cells in the relevant row, or
is that the wrong function for this?

Many thanks
TCOne play ..

A sample construct is available at:
www.savefile.com/files/9500795
Rhyming dictionary.xls

Assume the word data is in sheet: X,
cols A to AD, data from row1 down to row100 (say)

In sheet: X,

Insert a new col A (the word data will now be in cols B to AE)

Then put in A1: =MATCH(Query!$A$1,B1:AE1,0)
Copy A1 down to A100

In sheet: Query,

A1 will house the input for the query word

Select A2:AD2, put in the formula bar,
and array-enter the formula (Press CTRL SHIFT ENTER):

=IF(TRIM(A1)=quot;quot;,quot;quot;,IF(OFFSET(X!$B$1:$AE$1,MATCH(TR UE,ISNUMBER(X!$A$1:$A$100)
,0)-1,)=0,quot;quot;,OFFSET(X!$B$1:$AE$1,MATCH(TRUE,ISNUMBER(X !$A$1:$A$100),0)-1,)))

A2:AD2 will return the required results,
i.e. the row which contains the word in sheet: X

Adapt to suit ..
--
Max
Singapore
savefile.com/projects/236895
xdemechanik
---
quot;TCquot; gt; wrote in message ups.com...
gt; I'm trying to use Excel to create a basic rhyming dictionary. I have
gt; one worksheet for the query and results and another worksheet holding
gt; the word data. The data worksheet contains numerous rows of up to 30
gt; words each that all rhyme with each other, e.g. row 1 = brick, chick,
gt; flick; row 2 = dock, mock, clock; row 3 = career, veneer, appear (etc
gt; etc). My idea is that when the query term is found anywhere in the data
gt; sheet, all the words from the row it is found in are returned on the
gt; query/results sheet. For instance if the search term is quot;dockquot;, the
gt; results in this example would be mock and clock. However I can't seem
gt; to get LOOKUP to return the values of all cells in the relevant row, or
gt; is that the wrong function for this?
gt;
gt; Many thanks
gt; TC
gt;
I think this can be solved only with a UDF! Voil獺:

Function RhymeFind(rhymeto)
hitrow = 0
On Error Resume Next
hitrow = Worksheets(quot;Sheet2quot;).Cells.Find(What:=rhymeto, After:=[A1],
LookIn:=xlValues, LookAt:= _
xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Row
If hitrow gt; 0 Then
returnstr = quot;quot;
r = 1
Do While Not IsEmpty(Worksheets(quot;Sheet2quot;).Cells(hitrow, r))
returnstr = returnstr amp; Worksheets(quot;Sheet2quot;).Cells(hitrow, r) amp;
quot;,quot;
r = r 1
Loop
RhymeFind = Left(returnstr, Len(returnstr) - 1)
Else
RhymeFind = quot;No hit!quot;
End If
End Function

Usage:
Enter in cell A1 the word you search rhymes to, then in B2 enter
=Rhymefind(A1)Note, that in sheet2 the words are in separate cells!Regards,
Stefi

?C??ezt ?rta:

gt; I'm trying to use Excel to create a basic rhyming dictionary. I have
gt; one worksheet for the query and results and another worksheet holding
gt; the word data. The data worksheet contains numerous rows of up to 30
gt; words each that all rhyme with each other, e.g. row 1 = brick, chick,
gt; flick; row 2 = dock, mock, clock; row 3 = career, veneer, appear (etc
gt; etc). My idea is that when the query term is found anywhere in the data
gt; sheet, all the words from the row it is found in are returned on the
gt; query/results sheet. For instance if the search term is quot;dockquot;, the
gt; results in this example would be mock and clock. However I can't seem
gt; to get LOOKUP to return the values of all cells in the relevant row, or
gt; is that the wrong function for this?
gt;
gt; Many thanks
gt; TC
gt;
gt;

Max - thank you so much! This is great and works a treat.

Regards
TC
London, UK

Max wrote:

gt; One play ..
gt;
gt; A sample construct is available at:
gt; www.savefile.com/files/9500795
gt; Rhyming dictionary.xlsYou're welcome, TC !
Thanks for the feedback ..
--
Max
Singapore
savefile.com/projects/236895
xdemechanik
---
quot;TCquot; gt; wrote in message oups.com...
gt; Max - thank you so much! This is great and works a treat.
gt;
gt; Regards
gt; TC
gt; London, UK