Filtering from multiples and writing to new page

[ric]

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

 

[pauluk]

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

 

[Guest]

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

 

[Debra Dalgleish]

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 here:

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

 

[ric]

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

 

[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("D12").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