Hi,

I'm looking to filter a column which has multiples of the same string and
creating a definitive list on another worksheet. i.e.

Column A "data" sheet
bed
cog
dog
bed
dog
cog

So what I am looking to do is filter column A from row 2 to get a result of

Column A "summary" sheet
bed
cog
dog

The list isn't a set length long on "data" sheet so it needs to keep going
till it reaches a blank cell.

Regards

Ric

You will need to use advanced filter. This will allow you two set you
criteria for a determined array.

some info her
http://www.contextures.com/xladvfilter01.html

just remember that the headings need to be the same i.e your dat
called names then the advanced filter array also needs to have th
title names

--
Message posted from http://www.ExcelForum.com

Just use Data: Filter: Advanced Filter Then choose your destination and unique records.

You can do this with an Advanced Filter, but you must start from the
sheet on which you want the results (the destination sheet). There are
instructions he

http://www.contextures.com/xladvfilter01.html

ric wrote:
Hi,

I'm looking to filter a column which has multiples of the same string and
creating a definitive list on another worksheet. i.e.

Column A "data" sheet
bed
cog
dog
bed
dog
cog

So what I am looking to do is filter column A from row 2 to get a result of

Column A "summary" sheet
bed
cog
dog

The list isn't a set length long on "data" sheet so it needs to keep going
till it reaches a blank cell.

Regards

Ric




--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

"chris" wrote in message
...
Just use Data: Filter: Advanced Filter Then choose your destination and
unique records.

Chris,

This just gives me a reply of "you can only copy data to the active sheet".

What i have to do is take the list from "data" and add it (filtered) to
"look_up". I will then be looking at "data2", "data3" and "data4" and doing
the same with them but adding them below the values from "data" so I get a
continuous list from the 4 worksheets in a column. I will then filter that
list and write the definative list to "summary".

I'll actually be doing this with contract numbers at work (and not dog, cog
or bed) and then importing the total spend, estimate, budget and forecast
hours next to each contract to show any discrepencies that need to be
addressed. As some contracts are in the future i'll get contract numbers on
the "estimate" sheet that are not on the "spend" sheet. As new data is being
added on a monthly basis I don't want to miss anything so thought creating a
summary sheet via VB or a Macro pull out the information faster (and be a
lot less prone to mistakes).

Regards

Ric

What i have to do is take the list from "data" and add it (filtered) to
"look_up". I will then be looking at "data2", "data3" and "data4" and
doing
the same with them but adding them below the values from "data" so I get a
continuous list from the 4 worksheets in a column. I will then filter that
list and write the definative list to "summary".

I'll actually be doing this with contract numbers at work (and not dog,
cog
or bed) and then importing the total spend, estimate, budget and forecast
hours next to each contract to show any discrepencies that need to be
addressed. As some contracts are in the future i'll get contract numbers
on
the "estimate" sheet that are not on the "spend" sheet. As new data is
being
added on a monthly basis I don't want to miss anything so thought creating
a
summary sheet via VB or a Macro pull out the information faster (and be a
lot less prone to mistakes).

Regards

Ric

Got the advanced filter working, thanks. Here is the VB for the 4 pages

' This looks at the Spend worksheet and retrieves the contract numbers
Range("A1:A2").Select
Sheets("spend").Range("B2:B500").AdvancedFilter
Action:=xlFilterCopy, _
CopyToRange:=Range("A2"), Unique:=True


' This looks at the Estimate worksheet and retrieves the contract
numbers
Range("B1:B2").Select
Sheets("estimate").Range("B2:B500").AdvancedFilter
Action:=xlFilterCopy,
_
CopyToRange:=Range("B2"), Unique:=True


' This looks at the Directive worksheet and retrieves the contract
numbers
Range("C1:C2").Select
Sheets("dir").Range("B2:B500").AdvancedFilter Action:=xlFilterCopy,
_
CopyToRange:=Range("C2"), Unique:=True


' This looks at the RPS worksheet and retrieves the contract numbers
Range("D1:D2").Select
Sheets("rps").Range("B2:B500").AdvancedFilter Action:=xlFilterCopy,
_
CopyToRange:=Range("D2"), Unique:=True


Currently i've just told it to write the results in sperate columns but
I would like it to find the end of the last data and add it to the end so
it gives a continuos list. Also i've specified a range ("B2:B500") but I
would like it to look for a blank cell and stop instead of specifying the
range incase I get more records than 500.

Regards

Ric