I have been searching for a while now and have not been able to find a
solution to my problem. I have an excel document that contains many
customer records as well as the agent that assisted that customer. I
need a search utility that will search the excel spreadsheet for an
agent's name, and then copy the entire row (not just the matching cell)
of data, including the information about the customer to a new worksheet
or workbook (it doesn't matter). Basically, I am providing this service
to my agents so that when they need a summary of the accounts that they
have serviced, I can do a search and all of the data regarding every
record in which they were the agent can be provided.

I did find a utility called PowerGREP, however it seems that the
utility is geared more towards searching through more than one excel
document for matching terms and is a little more complex than I was
hoping for. If anyone can point me in the right direction, or if
further explanation is required please let me know. Thanks for
everyone's time.

James


--
ckjaime
------------------------------------------------------------------------
ckjaime's Profile: http://www.excelforum.com/member.php...o&userid=25249
View this thread: http://www.excelforum.com/showthread...hreadid=387402

This macro might get you what you want ...
Assuming that your data is in a workbook named DataBook.xls in a
table named "AllData",
and that you have a cell named "AgentID" where you can enter the
relevant AgentID,
and that column 1 in the data table holds the AgentID's ...

Sub FilterAndCopy()
Application.Goto Reference:="AllData"
Selection.AutoFilter Field:=1, Criteria1:=Range("AgentID")
Selection.Copy
Workbooks.Add Template:="Workbook"
Range("A1").Select
ActiveSheet.Paste
Windows("DataBook.xls").Activate
Application.CutCopyMode = False
Selection.AutoFilter
Application.Goto Reference:="AgentID"
End Sub

It may need a few tweaks but that should get the ball rolling.
Rgds,
ScottO

"ckjaime"
wrote in message
...
|
| I have been searching for a while now and have not been able to
find a
| solution to my problem. I have an excel document that contains
many
| customer records as well as the agent that assisted that customer.
I
| need a search utility that will search the excel spreadsheet for an
| agent's name, and then copy the entire row (not just the matching
cell)
| of data, including the information about the customer to a new
worksheet
| or workbook (it doesn't matter). Basically, I am providing this
service
| to my agents so that when they need a summary of the accounts that
they
| have serviced, I can do a search and all of the data regarding
every
| record in which they were the agent can be provided.
|
| I did find a utility called PowerGREP, however it seems that the
| utility is geared more towards searching through more than one
excel
| document for matching terms and is a little more complex than I was
| hoping for. If anyone can point me in the right direction, or if
| further explanation is required please let me know. Thanks for
| everyone's time.
|
| James
|
|
| --
| ckjaime
| -------------------------------------------------------------------
-----
| ckjaime's Profile:
http://www.excelforum.com/member.php...o&userid=25249
| View this thread:
http://www.excelforum.com/showthread...hreadid=387402
|

Not sure, just a non-array formulas play which might work ..

Assume source data is in Sheet1, cols A to O, data from row2 down
And the key column is say, col B where the Agent names are listed

Field1 Field2 Field3 ... etc
Text1 Agent1 Text1
Text2 Agent2 Text2
Text3 Agent2 Text3
Text4 Agent1 Text4
Text5 Agent3 Text5
etc

Using an empty col to the right, say col Q

Q1 will be reserved for input of the agent's name, e.g.: Agent2

Put in Q2: =IF(B2="","",IF(B2=TRIM($Q$1),ROW(),""))

Copy Q2 down to say Q100 to cover the max extent of data expected in the
source

Col Q is the criteria col which will simply assign arbitrary row numbers to
lines which satisfy the criteria. Col Q's returns will be read by the
formulas in Sheet2.

In a new Sheet2
-------
Copy paste the same headers from Sheet1 into A1:O1

Put in A2:
=IF(ISERROR(SMALL(Sheet1!$Q:$Q,ROWS($A$1:A1))),"", INDEX(Sheet1!A:A,MATCH(SMA
LL(Sheet1!$Q:$Q,ROWS($A$1:A1)),Sheet1!$Q:$Q,0)))

Copy A2 across to O2, fill down to O100
(cover the same range as done in col Q in Sheet1)

Sheet2 will return only the rows from Sheet1 which pertain to the agent's
name input in Sheet1's Q1, with all rows bunched neatly at the top, viz.:

Field1 Field2 Field3 .. etc
Text2 Agent2 Text2
Text3 Agent2 Text3
(blank rows below)

Adapt to suit ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"ckjaime" wrote in
message ...

I have been searching for a while now and have not been able to find a
solution to my problem. I have an excel document that contains many
customer records as well as the agent that assisted that customer. I
need a search utility that will search the excel spreadsheet for an
agent's name, and then copy the entire row (not just the matching cell)
of data, including the information about the customer to a new worksheet
or workbook (it doesn't matter). Basically, I am providing this service
to my agents so that when they need a summary of the accounts that they
have serviced, I can do a search and all of the data regarding every
record in which they were the agent can be provided.

I did find a utility called PowerGREP, however it seems that the
utility is geared more towards searching through more than one excel
document for matching terms and is a little more complex than I was
hoping for. If anyone can point me in the right direction, or if
further explanation is required please let me know. Thanks for
everyone's time.

James


--
ckjaime
------------------------------------------------------------------------
ckjaime's Profile:
http://www.excelforum.com/member.php...o&userid=25249
View this thread: http://www.excelforum.com/showthread...hreadid=387402