multiple searches on one entry query

[Orangeanorak]

Can you please tell me the formula or how I go about solving the following

l have column A with names in it and Column B with news. I want to search both columns for ocurrences of particular words and numbers in one search

For example show me the ocurrences in one search of Jones, Fredsaw, America, car, spaghetti, 07775462441 New and DCF1238

Thank you for your assistance - i hope I have explained this properly

l

Name News
Fred Smith Mary Jones sold a car FT99TGY to Michael Fraser for £5000. He lives at New Street Telephone 07775462441. He sold me DCF1238
Derek Fredsaw Mary Henning bought Dave Smooher a new car for his 21st birthday 0n 21/10/2010
Michael Marrat My best friends are Jo Rob Henry and others who live in America. Rob likes mercedes cars and helicopters. phone 0775462441
Mary Henning Sam Dunwert has invited Fred Smith Derek Fredsaw and Jon Derwent to visit him in America
Jo Roberts Derek loves music. His favourite artists are Madonna and Beyonce. He does not get on well with Dave. He like spaghetti and meat balls but not fish
Sam Dunwert I sold Sam three cars WE56RRT TT55THB DCF1238.
Dave Snooher Dave has arranged to meet me on Thursday in New Street. He wants to buy a mercedes from me
Rob Jennings Mary Henning and Fred Smith have invited me to America for spaghetti. I think its his birthday
Henry Jones Michael Fraser crashed his helicopter in Derwent

 

[hiker95]

Orangeanorak,

It is difficult to determine what goes in column A and B.


What version of Excel are you using?

You will generally get much more help (and faster) in this forum if you can post your small samples (what you have and what you expect to achieve) directly in the forum.

To attach screenshots, see below in my Signature block: Post a screen shot with one of these:

 

[Orangeanorak]

I have excel 2003 I'm afraid

can you use this link for an example of the spreadsheet described above

http://dl.dropbox.com/u/4674400/mrexcel120611.xls

I want to enter multiple search words and numbers in one search and have all the spread sheet checked for them

Thank you for your time

 

[hiker95]

Orangeanorak,

Thanks for the workbook.

Excel Workbook
	A	B
1	Name	News
2	Fred Smith	Mary Jones sold a car FT99TGY to Michael Fraser for 5000. He lives at New Street Telephone 07775462441. He sold me DCF1238
3	Derek Fredsaw	Mary Henning bought Dave Smooher a new car for his 21st birthday 0n 21/10/2010
4	Michael Marrat	My best friends are Jo Rob Henry and others who live in America. Rob likes mercedes cars and helicopters
5	Mary Henning	Sam Dunwert has invited Fred Smith Derek Fredsaw and Jon Derwent to visit him in America
6	Jo Roberts	Derek loves music. His favourite artists are Madonna and Beyonce. He does not get on well with Dave. He like spaghetti and meat balls but not fish
7	Sam Dunwert	I sold Sam three cars WE56RRT TT55THB DCF1238.
8	Dave Snooher	Dave has arranged to meet me on Thursday in New Street. He wants to buy a mercedes from me
9	Rob Jennings	Mary Henning and Fred Smith have invited me to America for spaghetti. I think its his birthday
10	Henry Jones	Michael Fraser crashed his helicopter in Derwent
11
Sheet1

Where is the list of search words? In a column on another worksheet?

And, how do we indicate that the search word/words were found on Sheet1?

Can we have another workbook, with the search words in Sheet2, beginning in cell A1 down?

 

[Orangeanorak]

hi hiker95 thanks for your time on this

Link to search sheet2 here http://dl.dropbox.com/u/4674400/searchfor.xls

Would it be possible to display the full contents of the cell from sheet1 that matches the search words from sheet2 onto a sheet3 or in someother way if you have a better idea

can it be made not case sensitive

Sorry this is getting more confusing

Perhaps this is not possible ---- if not can you show on sheet3 the sheeet1 cell reference that the match occurs in

 

[Aladin Akyurek]

hi hiker95 thanks for your time on this

Link to search sheet2 here http://dl.dropbox.com/u/4674400/searchfor.xls

Would it be possible to display the full contents of the cell from sheet1 that matches the search words from sheet2 onto a sheet3 or in someother way if you have a better idea

can it be made not case sensitive

Sorry this is getting more confusing

Perhaps this is not possible ---- if not can you show on sheet3 the sheeet1 cell reference that the match occurs in

(Sheet1) This is your list of items you want to search...

<TABLE style="WIDTH: 65pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=87><COLGROUP><COL style="WIDTH: 65pt; mso-width-source: userset; mso-width-alt: 2790" width=87><TBODY><TR style="HEIGHT: 13.8pt" height=18><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 65pt; HEIGHT: 13.8pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=18 width=87>Jones</TD></TR><TR style="HEIGHT: 13.8pt" height=18><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 13.8pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=18>Fredshaw</TD></TR><TR style="HEIGHT: 13.8pt" height=18><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 13.8pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=18>America</TD></TR><TR style="HEIGHT: 13.8pt" height=18><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 13.8pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=18>car</TD></TR><TR style="HEIGHT: 13.8pt" height=18><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 13.8pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=18>spaghetti</TD></TR><TR style="HEIGHT: 13.8pt" height=18><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 13.8pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=18 align=right>7775472441</TD></TR><TR style="HEIGHT: 13.8pt" height=18><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 13.8pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=18>New</TD></TR><TR style="HEIGHT: 13.8pt" height=18><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 13.8pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=18>DCF1238</TD></TR></TBODY></TABLE>

in Sheet2:

<TABLE style="WIDTH: 454pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=605><COLGROUP><COL style="WIDTH: 81pt; mso-width-source: userset; mso-width-alt: 3456" width=108><COL style="WIDTH: 373pt; mso-width-source: userset; mso-width-alt: 15897" width=497><TBODY><TR style="HEIGHT: 13.8pt" height=18><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 81pt; HEIGHT: 13.8pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=18 width=108>Name</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 373pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=497>News</TD></TR><TR style="HEIGHT: 27.6pt" height=37><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 27.6pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=37>Fred Smith</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 373pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=497>Mary Jones sold a car FT99TGY to Michael Fraser for £5000. He lives at New Street Telephone 07775462441. He sold me DCF1238</TD></TR><TR style="HEIGHT: 27.6pt" height=37><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 27.6pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=37>Derek Fredsaw</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 373pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=497>Mary Henning bought Dave Smooher a new car for his 21st birthday 0n 21/10/2010</TD></TR><TR style="HEIGHT: 27.6pt" height=37><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 27.6pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=37>Michael Marrat</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 373pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=497>My best friends are Jo Rob Henry and others who live in America. Rob likes mercedes cars and helicopters</TD></TR><TR style="HEIGHT: 27.6pt" height=37><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 27.6pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=37>Mary Henning</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 373pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=497>Sam Dunwert has invited Fred Smith Derek Fredsaw and Jon Derwent to visit him in America</TD></TR><TR style="HEIGHT: 41.4pt" height=55><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 41.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=55>Jo Roberts</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 373pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=497>Derek loves music. His favourite artists are Madonna and Beyonce. He does not get on well with Dave. He like spaghetti and meat balls but not fish</TD></TR><TR style="HEIGHT: 13.8pt" height=18><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 13.8pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=18>Sam Dunwert</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 373pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=497>I sold Sam three cars WE56RRT TT55THB DCF1238. </TD></TR><TR style="HEIGHT: 27.6pt" height=37><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 27.6pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=37>Dave Snooher</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 373pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=497>Dave has arranged to meet me on Thursday in New Street. He wants to buy a mercedes from me</TD></TR><TR style="HEIGHT: 27.6pt" height=37><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 27.6pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=37>Rob Jennings</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 373pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=497>Mary Henning and Fred Smith have invited me to America for spaghetti. I think its his birthday</TD></TR><TR style="HEIGHT: 13.8pt" height=18><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 13.8pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=18>Henry Jones</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 373pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=497>Michael Fraser crashed his helicopter in Derwent </TD></TR></TBODY></TABLE>

Must the search occur in the Name range or in the News range?

And what are the expected results?

 

[Orangeanorak]

the one I need is the news column

thanks

is it possible to do both columns or if i add more columns can they be included in future

thanks

 

[Aladin Akyurek]

the one I need is the news column

thanks

is it possible to do both columns or if i add more columns can they be included in future

thanks

Where do we search? And what do we return?

 

[Orangeanorak]

we search sheet1 news column for the search criteria in colA sheet2

 

[careboy]

is it possible to do both columns or if i add more columns can they be included in future