Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
automatically filter for unique records... or other suggestions
I need to know if there is a way to automatically filter for unique records.
I am creating a workbook where one sheet has a list of projects worked (column one) on and a task associated with the project (column two) and the number of hours worked (column 3) on that project/task combination. The next row may or may not have the same project name with a different task On the second sheet, I used the sumif (if the text in the column on sheet 1 matches the text in the cell on sheet 2) function to add all hours associated with the project. It works great except I have to manually place the project name on each row of the second sheet. (right now I use a data validation list to enter the project name so I don't have to type or copy and paste it each time) I would like to find a way to have the project name automatically entered on the second sheet. I first tried just using the = sign and then pointing at each cell, but it does not avoid duplicates. Then I found the filter for unique records which works great except I would have to manually apply the filter each time... which I don't want to do. Any suggestions? -- Lila |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
automatically filter for unique records... or other suggestions
Data / Filter / Advanced Filter
Action: Copy to another location AND Check the Unique Records only checkbox at the bottom left |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
automatically filter for unique records... or other suggestions
I did that, but it only does it that one time. If I change the original
list I have to run the filter again. Is there a way to do this automatically or maybe another combination of formulas that would do this? Lila "Buffgirl71" wrote in message oups.com... Data / Filter / Advanced Filter Action: Copy to another location AND Check the Unique Records only checkbox at the bottom left |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
automatically filter for unique records... or other suggestions
If you sort your data, you could use data|subtotals to get the subtotals.
Or maybe it's time to look at pivottables. If you want to read more about pivottables... Here are a few links: Debra Dalgleish's pictures at Jon Peltier's site: http://peltiertech.com/Excel/Pivots/pivottables.htm And Debra's own site: http://www.contextures.com/xlPivot01.html John Walkenbach also has some at: http://j-walk.com/ss/excel/files/general.htm (look for Tony Gwynn's Hit Database) Chip Pearson keeps Harald Staff's notes at: http://www.cpearson.com/excel/pivots.htm MS has some at (xl2000 and xl2002): http://office.microsoft.com/downloads/2000/XCrtPiv.aspx http://office.microsoft.com/assistan...lconPT101.aspx Lila wrote: I need to know if there is a way to automatically filter for unique records. I am creating a workbook where one sheet has a list of projects worked (column one) on and a task associated with the project (column two) and the number of hours worked (column 3) on that project/task combination. The next row may or may not have the same project name with a different task On the second sheet, I used the sumif (if the text in the column on sheet 1 matches the text in the cell on sheet 2) function to add all hours associated with the project. It works great except I have to manually place the project name on each row of the second sheet. (right now I use a data validation list to enter the project name so I don't have to type or copy and paste it each time) I would like to find a way to have the project name automatically entered on the second sheet. I first tried just using the = sign and then pointing at each cell, but it does not avoid duplicates. Then I found the filter for unique records which works great except I would have to manually apply the filter each time... which I don't want to do. Any suggestions? -- Lila -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Extracting information from records to another sheet automatically | Excel Worksheet Functions | |||
Can I automatically hide or filter a row? | Excel Worksheet Functions | |||
automatically filter out blanks in multiple columns | Excel Worksheet Functions | |||
automatically delete records w/duplicate address in excel | Excel Discussion (Misc queries) | |||
Automatically insert row between groups of records | Excel Discussion (Misc queries) |