Filtering from multiples and writing to new page
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 |
Filtering from multiples and writing to new page
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 |
Filtering from multiples and writing to new page
Just use Data: Filter: Advanced Filter Then choose your destination and unique records.
|
Filtering from multiples and writing to new page
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 |
Filtering from multiples and writing to new page
"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 |
Filtering from multiples and writing to new page
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 |
All times are GMT +1. The time now is 04:23 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com