| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
unique records by advanced filter
Hi All,
I'd like to make a simple filter for unique records copied to another location. The layout is this: two columns, A:B, 14 rows including a header. Unique filter is to be made by column A, but I want to copy both columns to a new location. When in the advanced filter dialog List range: $A$1:$A$14 criteria range: $A$1:$B$14 then in-place filtering is correct, but I can't in any way achieve the filtered range copied to another location, say C1. Any ideas? Another question is that why List range has to be $A$1:$A$14 (the volumn by which uniqueness is checked)? For me calling this range criteria range should be more logical, and range $A$1:$B$14 should be called List range (cells I want to list in another place). Maybe I misunderstand something not being a native English speaker, please explain me the correct usage of filtering unique records! Thanks, Stefi |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
unique records by advanced filter
Should work if you do as you assumed you should.
List Range should be $A$1:$B$14. Criteria range should be $A$1:$A$14 if column A has your duplicate records. Otherwise, make it $B$1:$B$14 Assuming you are selecting Unique records and you are attempting the paste to another place in the same worksheet. "Stefi" wrote: Hi All, I'd like to make a simple filter for unique records copied to another location. The layout is this: two columns, A:B, 14 rows including a header. Unique filter is to be made by column A, but I want to copy both columns to a new location. When in the advanced filter dialog List range: $A$1:$A$14 criteria range: $A$1:$B$14 then in-place filtering is correct, but I can't in any way achieve the filtered range copied to another location, say C1. Any ideas? Another question is that why List range has to be $A$1:$A$14 (the volumn by which uniqueness is checked)? For me calling this range criteria range should be more logical, and range $A$1:$B$14 should be called List range (cells I want to list in another place). Maybe I misunderstand something not being a native English speaker, please explain me the correct usage of filtering unique records! Thanks, Stefi |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
unique records by advanced filter
Thanks Sean, but with your settings nothing happens. LIST range has to be
$A$1:$A$14, and CRITERIA range has to be $A$1:$B$14 to make a correct filtering in-place (I also thought it should be the opposite way like you wrote, that's why I posted 2nd part of my question). What is a bigger problem, copying to another location doesn't work even with the above settings. Why? Regards, Stefi Sean Timmons ezt *rta: Should work if you do as you assumed you should. List Range should be $A$1:$B$14. Criteria range should be $A$1:$A$14 if column A has your duplicate records. Otherwise, make it $B$1:$B$14 Assuming you are selecting Unique records and you are attempting the paste to another place in the same worksheet. "Stefi" wrote: Hi All, I'd like to make a simple filter for unique records copied to another location. The layout is this: two columns, A:B, 14 rows including a header. Unique filter is to be made by column A, but I want to copy both columns to a new location. When in the advanced filter dialog List range: $A$1:$A$14 criteria range: $A$1:$B$14 then in-place filtering is correct, but I can't in any way achieve the filtered range copied to another location, say C1. Any ideas? Another question is that why List range has to be $A$1:$A$14 (the volumn by which uniqueness is checked)? For me calling this range criteria range should be more logical, and range $A$1:$B$14 should be called List range (cells I want to list in another place). Maybe I misunderstand something not being a native English speaker, please explain me the correct usage of filtering unique records! Thanks, Stefi |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
unique records by advanced filter
Stefi
Leave the criteria range blank and just select the single column for List Range. Copy to set for C1 should be OK For more on this and a video see Debra Dalgleish's site. http://www.contextures.on.ca/xladvfi....html#FilterUR Gord Dibben MS Excel MVP On Wed, 30 Sep 2009 07:55:03 -0700, Stefi wrote: Hi All, I'd like to make a simple filter for unique records copied to another location. The layout is this: two columns, A:B, 14 rows including a header. Unique filter is to be made by column A, but I want to copy both columns to a new location. When in the advanced filter dialog List range: $A$1:$A$14 criteria range: $A$1:$B$14 then in-place filtering is correct, but I can't in any way achieve the filtered range copied to another location, say C1. Any ideas? Another question is that why List range has to be $A$1:$A$14 (the volumn by which uniqueness is checked)? For me calling this range criteria range should be more logical, and range $A$1:$B$14 should be called List range (cells I want to list in another place). Maybe I misunderstand something not being a native English speaker, please explain me the correct usage of filtering unique records! Thanks, Stefi |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
unique records by advanced filter
Thanks Gord, I followed your instruction and found that (provided $A$1:$A$14
is entered in List range) 1. leaving the criteria range blank or filling it with $A$1:$B$14 gives the same result when in-place filtering is made (in other words the content of criteria range was neutral), 2. Entering C1 in Copy to box resulted in copying filtered cells only from column A, I could in no way achieve to copy the filtered cells from column B. There must be something wrong with this feature or I still misunderstand something! Regards, Stefi Gord Dibben ezt *rta: Stefi Leave the criteria range blank and just select the single column for List Range. Copy to set for C1 should be OK For more on this and a video see Debra Dalgleish's site. http://www.contextures.on.ca/xladvfi....html#FilterUR Gord Dibben MS Excel MVP On Wed, 30 Sep 2009 07:55:03 -0700, Stefi wrote: Hi All, I'd like to make a simple filter for unique records copied to another location. The layout is this: two columns, A:B, 14 rows including a header. Unique filter is to be made by column A, but I want to copy both columns to a new location. When in the advanced filter dialog List range: $A$1:$A$14 criteria range: $A$1:$B$14 then in-place filtering is correct, but I can't in any way achieve the filtered range copied to another location, say C1. Any ideas? Another question is that why List range has to be $A$1:$A$14 (the volumn by which uniqueness is checked)? For me calling this range criteria range should be more logical, and range $A$1:$B$14 should be called List range (cells I want to list in another place). Maybe I misunderstand something not being a native English speaker, please explain me the correct usage of filtering unique records! Thanks, Stefi |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
unique records by advanced filter
Sorry, I misunderstood.
If you want to copy both column A and B to another location based on unique records in column B List Range..............A1:B14 Criteria Range.............B1:B14.......this is range with the uniques Copy to range...........C1 Gord On Thu, 1 Oct 2009 02:58:01 -0700, Stefi wrote: Thanks Gord, I followed your instruction and found that (provided $A$1:$A$14 is entered in List range) 1. leaving the criteria range blank or filling it with $A$1:$B$14 gives the same result when in-place filtering is made (in other words the content of criteria range was neutral), 2. Entering C1 in Copy to box resulted in copying filtered cells only from column A, I could in no way achieve to copy the filtered cells from column B. There must be something wrong with this feature or I still misunderstand something! Regards, Stefi Gord Dibben ezt rta: Stefi Leave the criteria range blank and just select the single column for List Range. Copy to set for C1 should be OK For more on this and a video see Debra Dalgleish's site. http://www.contextures.on.ca/xladvfi....html#FilterUR Gord Dibben MS Excel MVP On Wed, 30 Sep 2009 07:55:03 -0700, Stefi wrote: Hi All, I'd like to make a simple filter for unique records copied to another location. The layout is this: two columns, A:B, 14 rows including a header. Unique filter is to be made by column A, but I want to copy both columns to a new location. When in the advanced filter dialog List range: $A$1:$A$14 criteria range: $A$1:$B$14 then in-place filtering is correct, but I can't in any way achieve the filtered range copied to another location, say C1. Any ideas? Another question is that why List range has to be $A$1:$A$14 (the volumn by which uniqueness is checked)? For me calling this range criteria range should be more logical, and range $A$1:$B$14 should be called List range (cells I want to list in another place). Maybe I misunderstand something not being a native English speaker, please explain me the correct usage of filtering unique records! Thanks, Stefi |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
unique records by advanced filter
Hi Gord,
Thank you for dealing with my problem, but doing exactly what you wrote resulted in both columns having been copied but without filtering out the redundant rows (the entire range A1:B14 has been copied). Stefi Gord Dibben ezt *rta: Sorry, I misunderstood. If you want to copy both column A and B to another location based on unique records in column B List Range..............A1:B14 Criteria Range.............B1:B14.......this is range with the uniques Copy to range...........C1 Gord On Thu, 1 Oct 2009 02:58:01 -0700, Stefi wrote: Thanks Gord, I followed your instruction and found that (provided $A$1:$A$14 is entered in List range) 1. leaving the criteria range blank or filling it with $A$1:$B$14 gives the same result when in-place filtering is made (in other words the content of criteria range was neutral), 2. Entering C1 in Copy to box resulted in copying filtered cells only from column A, I could in no way achieve to copy the filtered cells from column B. There must be something wrong with this feature or I still misunderstand something! Regards, Stefi Gord Dibben ezt *rta: Stefi Leave the criteria range blank and just select the single column for List Range. Copy to set for C1 should be OK For more on this and a video see Debra Dalgleish's site. http://www.contextures.on.ca/xladvfi....html#FilterUR Gord Dibben MS Excel MVP On Wed, 30 Sep 2009 07:55:03 -0700, Stefi wrote: Hi All, I'd like to make a simple filter for unique records copied to another location. The layout is this: two columns, A:B, 14 rows including a header. Unique filter is to be made by column A, but I want to copy both columns to a new location. When in the advanced filter dialog List range: $A$1:$A$14 criteria range: $A$1:$B$14 then in-place filtering is correct, but I can't in any way achieve the filtered range copied to another location, say C1. Any ideas? Another question is that why List range has to be $A$1:$A$14 (the volumn by which uniqueness is checked)? For me calling this range criteria range should be more logical, and range $A$1:$B$14 should be called List range (cells I want to list in another place). Maybe I misunderstand something not being a native English speaker, please explain me the correct usage of filtering unique records! Thanks, Stefi |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
unique records by advanced filter
Don't use the criteria range (leave it empty).
Do the advanced filter against a single column A1:a14 Make sure unique records is checked. After the filtering is done, select both columns. (F5, ctrl-g, edit|goto) Special|visible cells only Copy those visible cells (now including both columns) paste into the new location Stefi wrote: Hi All, I'd like to make a simple filter for unique records copied to another location. The layout is this: two columns, A:B, 14 rows including a header. Unique filter is to be made by column A, but I want to copy both columns to a new location. When in the advanced filter dialog List range: $A$1:$A$14 criteria range: $A$1:$B$14 then in-place filtering is correct, but I can't in any way achieve the filtered range copied to another location, say C1. Any ideas? Another question is that why List range has to be $A$1:$A$14 (the volumn by which uniqueness is checked)? For me calling this range criteria range should be more logical, and range $A$1:$B$14 should be called List range (cells I want to list in another place). Maybe I misunderstand something not being a native English speaker, please explain me the correct usage of filtering unique records! Thanks, Stefi -- Dave Peterson |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
unique records by advanced filter
Thanks, Dave, I did that, it worked, but it shows that Copying unique records
to a new location feature doesn't work in Excel2003. I wonder has it been fixed in Excel2007. Stefi Dave Peterson ezt *rta: Don't use the criteria range (leave it empty). Do the advanced filter against a single column A1:a14 Make sure unique records is checked. After the filtering is done, select both columns. (F5, ctrl-g, edit|goto) Special|visible cells only Copy those visible cells (now including both columns) paste into the new location Stefi wrote: Hi All, I'd like to make a simple filter for unique records copied to another location. The layout is this: two columns, A:B, 14 rows including a header. Unique filter is to be made by column A, but I want to copy both columns to a new location. When in the advanced filter dialog List range: $A$1:$A$14 criteria range: $A$1:$B$14 then in-place filtering is correct, but I can't in any way achieve the filtered range copied to another location, say C1. Any ideas? Another question is that why List range has to be $A$1:$A$14 (the volumn by which uniqueness is checked)? For me calling this range criteria range should be more logical, and range $A$1:$B$14 should be called List range (cells I want to list in another place). Maybe I misunderstand something not being a native English speaker, please explain me the correct usage of filtering unique records! Thanks, Stefi -- Dave Peterson |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
unique records by advanced filter
Since you want to include more columns that you're filtering on (a single column
for uniqueness), you don't want to copy to another location using that dialog. You have to use a multiple step process. On the other hand, if you wanted to copy the entire range (single column or multiple columns to determine uniqueness), you can start the dialog on the new sheet. Then go back to the real data when you're entering addresses into that dialog. Seems kind of backward, but it works fine. I don't know if xl2007 changed this "feature". Stefi wrote: Thanks, Dave, I did that, it worked, but it shows that Copying unique records to a new location feature doesn't work in Excel2003. I wonder has it been fixed in Excel2007. Stefi Dave Peterson ezt *rta: Don't use the criteria range (leave it empty). Do the advanced filter against a single column A1:a14 Make sure unique records is checked. After the filtering is done, select both columns. (F5, ctrl-g, edit|goto) Special|visible cells only Copy those visible cells (now including both columns) paste into the new location Stefi wrote: Hi All, I'd like to make a simple filter for unique records copied to another location. The layout is this: two columns, A:B, 14 rows including a header. Unique filter is to be made by column A, but I want to copy both columns to a new location. When in the advanced filter dialog List range: $A$1:$A$14 criteria range: $A$1:$B$14 then in-place filtering is correct, but I can't in any way achieve the filtered range copied to another location, say C1. Any ideas? Another question is that why List range has to be $A$1:$A$14 (the volumn by which uniqueness is checked)? For me calling this range criteria range should be more logical, and range $A$1:$B$14 should be called List range (cells I want to list in another place). Maybe I misunderstand something not being a native English speaker, please explain me the correct usage of filtering unique records! Thanks, Stefi -- Dave Peterson -- Dave Peterson |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
unique records by advanced filter
Well, I can accept that this task can be done only via a multiple step
process, my problem really was that Excel Help didn't stated this clearly. In my opinion the Help and the logic of Advanced filter dialog box suggest (or at least allows to draw this conclusion) that this feature should work in the way I supposed. Anyway, the question is settled, I'll use the multiple step process, though I'll still consider it a workaround. Thanks, Stefi Dave Peterson ezt *rta: Since you want to include more columns that you're filtering on (a single column for uniqueness), you don't want to copy to another location using that dialog. You have to use a multiple step process. On the other hand, if you wanted to copy the entire range (single column or multiple columns to determine uniqueness), you can start the dialog on the new sheet. Then go back to the real data when you're entering addresses into that dialog. Seems kind of backward, but it works fine. I don't know if xl2007 changed this "feature". Stefi wrote: Thanks, Dave, I did that, it worked, but it shows that Copying unique records to a new location feature doesn't work in Excel2003. I wonder has it been fixed in Excel2007. Stefi âžDave Petersonâ ezt Ã*rta: Don't use the criteria range (leave it empty). Do the advanced filter against a single column A1:a14 Make sure unique records is checked. After the filtering is done, select both columns. (F5, ctrl-g, edit|goto) Special|visible cells only Copy those visible cells (now including both columns) paste into the new location Stefi wrote: Hi All, I'd like to make a simple filter for unique records copied to another location. The layout is this: two columns, A:B, 14 rows including a header. Unique filter is to be made by column A, but I want to copy both columns to a new location. When in the advanced filter dialog List range: $A$1:$A$14 criteria range: $A$1:$B$14 then in-place filtering is correct, but I can't in any way achieve the filtered range copied to another location, say C1. Any ideas? Another question is that why List range has to be $A$1:$A$14 (the volumn by which uniqueness is checked)? For me calling this range criteria range should be more logical, and range $A$1:$B$14 should be called List range (cells I want to list in another place). Maybe I misunderstand something not being a native English speaker, please explain me the correct usage of filtering unique records! Thanks, Stefi -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Advanced Filter - extracting unique records | Excel Worksheet Functions | |||
| "Live" Advanced Filter (for unique records) | Excel Discussion (Misc queries) | |||
| unique filter results in some non-unique records. | Excel Discussion (Misc queries) | |||
| Error using Advanced Filter Unique Records | Excel Discussion (Misc queries) | |||
| Inconsistent Results: Advanced Filter Unique Records Only | Excel Discussion (Misc queries) | |||
Linear Mode
