Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Stutter in AdvancedFilter Unique Results?
After pasting a data range into my template workbook (Paste Special - Values
Only), I filter two ranges for Unique Values using ' Filter unique values ' Subsystems Range("SUBSYSTEM").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _ "AA1"), Unique:=True ' Chargeability Range("CHARGEABILITY").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _ "AD1"), Unique:=True It's not unusual to get a "stutter" - one value showing up twice. The full range has 862 values; the filter yields 12 Unique Values, which is the same as what AutoFilter shows. But one value is repeated. The AutoFilter doesn't show it twice. Neither result from the filter shows an extra space, although I haven't checked the 239 out of the 862 that match the repeated value. Any suggestions? Ed |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Stutter in AdvancedFilter Unique Results?
This is generally caused because you don't have a header row. the
Autofilter assumes your data range is like Header1 Header2 Data Data Data Date If you have Data Data Data Data Data Date it treats the first row as a header and copies it - thus if that value is found later in the table, you get a single duplicate entry. May not be your problem, but it is the most likely that occurs to me. -- Regards, Tom Ogilvy "Ed" wrote in message ... After pasting a data range into my template workbook (Paste Special - Values Only), I filter two ranges for Unique Values using ' Filter unique values ' Subsystems Range("SUBSYSTEM").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _ "AA1"), Unique:=True ' Chargeability Range("CHARGEABILITY").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _ "AD1"), Unique:=True It's not unusual to get a "stutter" - one value showing up twice. The full range has 862 values; the filter yields 12 Unique Values, which is the same as what AutoFilter shows. But one value is repeated. The AutoFilter doesn't show it twice. Neither result from the filter shows an extra space, although I haven't checked the 239 out of the 862 that match the repeated value. Any suggestions? Ed |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Stutter in AdvancedFilter Unique Results?
The data ranges are set in the template, and the data is pasted into the
ranges. There are headers on the template for the data ranges in Row 1, but the range definitions start at Row 2. When I had the ranges include Row 1, I got the headers as part of my Unique Values list, so I changed it. Would it be better to redefine the ranges back to Row 1 and Find/Delete my header value if it shows up? Or am I missing a parameter in my filter? Ed "Tom Ogilvy" wrote in message ... This is generally caused because you don't have a header row. the Autofilter assumes your data range is like Header1 Header2 Data Data Data Date If you have Data Data Data Data Data Date it treats the first row as a header and copies it - thus if that value is found later in the table, you get a single duplicate entry. May not be your problem, but it is the most likely that occurs to me. -- Regards, Tom Ogilvy "Ed" wrote in message ... After pasting a data range into my template workbook (Paste Special - Values Only), I filter two ranges for Unique Values using ' Filter unique values ' Subsystems Range("SUBSYSTEM").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _ "AA1"), Unique:=True ' Chargeability Range("CHARGEABILITY").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _ "AD1"), Unique:=True It's not unusual to get a "stutter" - one value showing up twice. The full range has 862 values; the filter yields 12 Unique Values, which is the same as what AutoFilter shows. But one value is repeated. The AutoFilter doesn't show it twice. Neither result from the filter shows an extra space, although I haven't checked the 239 out of the 862 that match the repeated value. Any suggestions? Ed |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Stutter in AdvancedFilter Unique Results?
Your choice Ed, delete the headers or start in row 2 and delete the
duplicate - but if that is the only place that particular value occurs, you will loose it. So I would start in row1 and delete the headers if you don't want them. -- Regards, Tom Ogilvy "Ed" wrote in message ... The data ranges are set in the template, and the data is pasted into the ranges. There are headers on the template for the data ranges in Row 1, but the range definitions start at Row 2. When I had the ranges include Row 1, I got the headers as part of my Unique Values list, so I changed it. Would it be better to redefine the ranges back to Row 1 and Find/Delete my header value if it shows up? Or am I missing a parameter in my filter? Ed "Tom Ogilvy" wrote in message ... This is generally caused because you don't have a header row. the Autofilter assumes your data range is like Header1 Header2 Data Data Data Date If you have Data Data Data Data Data Date it treats the first row as a header and copies it - thus if that value is found later in the table, you get a single duplicate entry. May not be your problem, but it is the most likely that occurs to me. -- Regards, Tom Ogilvy "Ed" wrote in message ... After pasting a data range into my template workbook (Paste Special - Values Only), I filter two ranges for Unique Values using ' Filter unique values ' Subsystems Range("SUBSYSTEM").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _ "AA1"), Unique:=True ' Chargeability Range("CHARGEABILITY").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _ "AD1"), Unique:=True It's not unusual to get a "stutter" - one value showing up twice. The full range has 862 values; the filter yields 12 Unique Values, which is the same as what AutoFilter shows. But one value is repeated. The AutoFilter doesn't show it twice. Neither result from the filter shows an extra space, although I haven't checked the 239 out of the 862 that match the repeated value. Any suggestions? Ed |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Stutter in AdvancedFilter Unique Results?
Thanks for the help, Tom.
Ed "Tom Ogilvy" wrote in message ... Your choice Ed, delete the headers or start in row 2 and delete the duplicate - but if that is the only place that particular value occurs, you will loose it. So I would start in row1 and delete the headers if you don't want them. -- Regards, Tom Ogilvy "Ed" wrote in message ... The data ranges are set in the template, and the data is pasted into the ranges. There are headers on the template for the data ranges in Row 1, but the range definitions start at Row 2. When I had the ranges include Row 1, I got the headers as part of my Unique Values list, so I changed it. Would it be better to redefine the ranges back to Row 1 and Find/Delete my header value if it shows up? Or am I missing a parameter in my filter? Ed "Tom Ogilvy" wrote in message ... This is generally caused because you don't have a header row. the Autofilter assumes your data range is like Header1 Header2 Data Data Data Date If you have Data Data Data Data Data Date it treats the first row as a header and copies it - thus if that value is found later in the table, you get a single duplicate entry. May not be your problem, but it is the most likely that occurs to me. -- Regards, Tom Ogilvy "Ed" wrote in message ... After pasting a data range into my template workbook (Paste Special - Values Only), I filter two ranges for Unique Values using ' Filter unique values ' Subsystems Range("SUBSYSTEM").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _ "AA1"), Unique:=True ' Chargeability Range("CHARGEABILITY").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _ "AD1"), Unique:=True It's not unusual to get a "stutter" - one value showing up twice. The full range has 862 values; the filter yields 12 Unique Values, which is the same as what AutoFilter shows. But one value is repeated. The AutoFilter doesn't show it twice. Neither result from the filter shows an extra space, although I haven't checked the 239 out of the 862 that match the repeated value. Any suggestions? Ed |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Multiple results of Unique Values | Excel Worksheet Functions | |||
Populate formula results in cells to next unique result. VBA or Fu | Excel Worksheet Functions | |||
Concatenate Unique advanced filter results | Excel Discussion (Misc queries) | |||
unique filter results in some non-unique records. | Excel Discussion (Misc queries) | |||
Inconsistent Results: Advanced Filter Unique Records Only | Excel Discussion (Misc queries) |