lookup and extract

[Donald B]

ok, I have multiple names I need to extract along with all the data on the
same row to another sheet. These names will appear multiple times.

There's about 2 hundred of these I do daily. Again I need to focus only on
the names which one person's name could apear 10-20 times in any order and I
need all the data associated with in the row to move to another sheet.


ex:
djones red bike
marce green shoes
phaul red gloves
marce blue hat
phaul green bike
djones blue ball

hope i was clear on this
tia
Don

 

[Tevuna]

Assuming your data starts at cell A2 on Sheet1

1. Select header of column A
2. Data > Advanced Filter > Unique records and another location = Sheet2!A2
3. After all filtered names are on sheet2, use vlookup in cell B2 to get the
colors
4. =vlookup(A2,Sheet1!$A$2:$C$100,column(),FALSE)
5. Copy acroos columns and down rows

 

[Tevuna]

Sorry I was mislead by your phrasing.. You can not filter to another sheet
Try a new column in sheet1. Or look here

http://www.microsoft.com/office/com...ublic.excel.worksheet.functions&lang=en&cr=US

 

[Max]

Here's one formulas set-up which delivers it the way you're after ..

Take this sample construct from my archives:
http://www.savefile.com/files/430142
AutoCopy Lines to Resp Sht Non Array.xls
(Full details inside, nicely rendered. Easy to adapt ..)

Data is continuously entered in a master ("parent") sheet, with lines neatly
auto-copied to each individual ("child") sheet based on the values within a
key col.

In the sample, the key col in the master sheet is the "State" col, which may
contain eg: NY, CA, NV, SD, AZ, etc. All lines with "NY" in the key col will
be auto-copied to the sheet named: NY, and appear neatly bunched at the top.
Ditto for lines with "CA", "NV", etc which will be copied into their
respective sheets.

Propagation of the "child" sheet is as simple as making a copy of the
initial one, then renaming it accordingly as the next key col value. Eg we
first formulate one child sheet for "NY", dress it up nicely, then just make
copies of the "NY" sheet, and rename these as: CA, NV, SD, etc.

In your case, the key col would be your "Names" col, eg: djones, marce etc

 

[T. Valko]

You can not filter to another sheet

If using the advanced filter you can filter to a different sheet *if* you
start the process from the different sheet.

Suppose you have this list of names on Sheet1 in the range A1:A5

Name
Biff
Biff
Max
Tevuna

You want to extract the uniques with a filter to Sheet2 starting in cell A1.

Navigate to Sheet2
Goto the menu Data>Filter>Advanced filter
Select: Copy to another location
List range: Sheet1!A1:A5
Copy to: A1
Select: Unique records only
OK