Find name and return records

[Guest]

I have a spreadsheet with company names and contact (client) information,
owner information, director information, etc. The same client name may
appear more than once within a record or within multiple records. I would
like to be able to enter the client's last name on a separate worksheet and
have that a list of all of the companies that name is associated with
populate the list. I have tried and tried to figure out how to do this, but
I just don't understand. Could anyone help me out with this? Thank you in
advance for any time spent helping me.

Thank you

 

[Tom Ogilvy]

You can do this with advanced filter.

See Debra Dalgleish's pages which have information on using the advanced
filter.
http://www.contextures.com/tiptech.html

If you want to go right to writing code you can look at Ron de Bruin's page:
http://www.rondebruin.nl/copy5.htm

 

[Guest]

I might just not be looking at those directions corrently, but to me it
appears that those instructions are for searching within just one column, or
for return results when looking for just one or maybe two columns. I would
like to be able to search across about 25 different columns in which the name
could be found. Could you help to clarify this for me?

Thank you

 

[Tom Ogilvy]

Sub CopyData
Dim sh as Worksheet, cnt as Long
Dim i as Long, j as Long
Dim companyname as String
Dim rng as Range
With Worksheets("Sheet1")
set rng = .range(.cells(5,1),.cells(rows.count,1).end(xlup))
if rng(rng.count).row > 5 then
rng.EntireRow.ClearContents
end if
End with

i = 1
j = 5
set sh = worksheets("sheet2")
Companyname = "Hartford"
companyname = "*" & companyname & "*"
do while sh.Cells(i,1) <> ""
cnt = Application.countif(sh.rows(i),companyname)
if cnt <> 0 then
sh.rows(i).copy Destination:=worksheets( _
"Sheet1").Cells(j,1)
j = j + 1
end if
i = i + 1
Loop
End Sub

 

[Guest]

Tom, I am sorry but I don't understand how to edit this code so it fits my
worksheet. Can I email you a sample of my worksheet and you could help me so
you can see title names and all that? Do you have an email address I can
email to? Mine is (e-mail address removed) if you would like to contact me that
way. Thank you so much.