Today, whilst sorting a worksheet Excel behaved in a way I didn't expect.

Column F, rows 1 to 200, contained concatenations of the previous five
columns i.e

=A1&B1&C1&D1&E1
=A2&B2&C2&D2&E2

etc....down to =A200&B200&C200&D200&E200

What I wanted was a list of the unique values in F, so selected the
range F1:F200 and Data->Advanced Filter. I selected 'Unique Records
only' and to copy the filtered data under my range.

Wat Excel did, however, was copy the unique records from the A to E
cells being concatenated. So instead of a list pf unique records in one
column I had a 5 column list. Now it just so happens that this was
*very* useful to me.

It just really surprised me as I never expected it to filter the cells
being concatenated rather than the actual result of the concatenation.
WHy did it do this? How would I have filtered just the F column data?

Jay

If you selected F1:F200, that's the range that should have been
filtered. Perhaps the range changed when you opened the Advanced Filter
dialog box, and you didn't notice.

Also, if you're using the Advanced Filter, you should have a heading in
the first row. Otherwise, the first value will be treated as the
heading, and may be duplicated in the list.

Jay wrote:
> Today, whilst sorting a worksheet Excel behaved in a way I didn't expect.
>
> Column F, rows 1 to 200, contained concatenations of the previous five
> columns i.e
>
> =A1&B1&C1&D1&E1
> =A2&B2&C2&D2&E2
>
> etc....down to =A200&B200&C200&D200&E200
>
> What I wanted was a list of the unique values in F, so selected the
> range F1:F200 and Data->Advanced Filter. I selected 'Unique Records
> only' and to copy the filtered data under my range.
>
> Wat Excel did, however, was copy the unique records from the A to E
> cells being concatenated. So instead of a list pf unique records in one
> column I had a 5 column list. Now it just so happens that this was
> *very* useful to me.
>
> It just really surprised me as I never expected it to filter the cells
> being concatenated rather than the actual result of the concatenation.
> WHy did it do this? How would I have filtered just the F column data?
>
> Jay


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

No, I did the same thing in several worksheets and the range didn't
change in the dialog box. I even entered it manually as a double-check

And I know about the header row, I just didn't mention it in my post.

Column F was definitely the range to be filtered but it definitely
returned the 5 columns (from the concatenation in the F column cells)?

Jay


Debra Dalgleish wrote:
> If you selected F1:F200, that's the range that should have been
> filtered. Perhaps the range changed when you opened the Advanced Filter
> dialog box, and you didn't notice.
>
> Also, if you're using the Advanced Filter, you should have a heading in
> the first row. Otherwise, the first value will be treated as the
> heading, and may be duplicated in the list.
>
> Jay wrote:
>> Today, whilst sorting a worksheet Excel behaved in a way I didn't expect.
>>
>> Column F, rows 1 to 200, contained concatenations of the previous five
>> columns i.e
>>
>> =A1&B1&C1&D1&E1
>> =A2&B2&C2&D2&E2
>>
>> etc....down to =A200&B200&C200&D200&E200
>>
>> What I wanted was a list of the unique values in F, so selected the
>> range F1:F200 and Data->Advanced Filter. I selected 'Unique Records
>> only' and to copy the filtered data under my range.
>>
>> Wat Excel did, however, was copy the unique records from the A to E
>> cells being concatenated. So instead of a list pf unique records in
>> one column I had a 5 column list. Now it just so happens that this
>> was *very* useful to me.
>>
>> It just really surprised me as I never expected it to filter the cells
>> being concatenated rather than the actual result of the concatenation.
>> WHy did it do this? How would I have filtered just the F column data?
>>
>> Jay
>
>

I think Excel saw it as a contiguous block of records and that's why
you also got columns A to E. If you insert a blank column between E and
F (so that F becomes G) and try it with just G then you will not get
the other columns.

Hope this helps.

Pete

Jay wrote:
> No, I did the same thing in several worksheets and the range didn't
> change in the dialog box. I even entered it manually as a double-check
>
> And I know about the header row, I just didn't mention it in my post.
>
> Column F was definitely the range to be filtered but it definitely
> returned the 5 columns (from the concatenation in the F column cells)?
>
> Jay
>
>
> Debra Dalgleish wrote:
> > If you selected F1:F200, that's the range that should have been
> > filtered. Perhaps the range changed when you opened the Advanced Filter
> > dialog box, and you didn't notice.
> >
> > Also, if you're using the Advanced Filter, you should have a heading in
> > the first row. Otherwise, the first value will be treated as the
> > heading, and may be duplicated in the list.
> >
> > Jay wrote:
> >> Today, whilst sorting a worksheet Excel behaved in a way I didn't expect.
> >>
> >> Column F, rows 1 to 200, contained concatenations of the previous five
> >> columns i.e
> >>
> >> =A1&B1&C1&D1&E1
> >> =A2&B2&C2&D2&E2
> >>
> >> etc....down to =A200&B200&C200&D200&E200
> >>
> >> What I wanted was a list of the unique values in F, so selected the
> >> range F1:F200 and Data->Advanced Filter. I selected 'Unique Records
> >> only' and to copy the filtered data under my range.
> >>
> >> Wat Excel did, however, was copy the unique records from the A to E
> >> cells being concatenated. So instead of a list pf unique records in
> >> one column I had a 5 column list. Now it just so happens that this
> >> was *very* useful to me.
> >>
> >> It just really surprised me as I never expected it to filter the cells
> >> being concatenated rather than the actual result of the concatenation.
> >> WHy did it do this? How would I have filtered just the F column data?
> >>
> >> Jay
> >
> >

I didn't *also* get columns A to E. I *only* got A to E, which were the
cells concatenated in the F column range I was filtering for unique values?

Jay


Pete_UK wrote:
> I think Excel saw it as a contiguous block of records and that's why
> you also got columns A to E. If you insert a blank column between E and
> F (so that F becomes G) and try it with just G then you will not get
> the other columns.
>
> Hope this helps.
>
> Pete
>
> Jay wrote:
>> No, I did the same thing in several worksheets and the range didn't
>> change in the dialog box. I even entered it manually as a double-check
>>
>> And I know about the header row, I just didn't mention it in my post.
>>
>> Column F was definitely the range to be filtered but it definitely
>> returned the 5 columns (from the concatenation in the F column cells)?
>>
>> Jay
>>
>>
>> Debra Dalgleish wrote:
>>> If you selected F1:F200, that's the range that should have been
>>> filtered. Perhaps the range changed when you opened the Advanced Filter
>>> dialog box, and you didn't notice.
>>>
>>> Also, if you're using the Advanced Filter, you should have a heading in
>>> the first row. Otherwise, the first value will be treated as the
>>> heading, and may be duplicated in the list.
>>>
>>> Jay wrote:
>>>> Today, whilst sorting a worksheet Excel behaved in a way I didn't expect.
>>>>
>>>> Column F, rows 1 to 200, contained concatenations of the previous five
>>>> columns i.e
>>>>
>>>> =A1&B1&C1&D1&E1
>>>> =A2&B2&C2&D2&E2
>>>>
>>>> etc....down to =A200&B200&C200&D200&E200
>>>>
>>>> What I wanted was a list of the unique values in F, so selected the
>>>> range F1:F200 and Data->Advanced Filter. I selected 'Unique Records
>>>> only' and to copy the filtered data under my range.
>>>>
>>>> Wat Excel did, however, was copy the unique records from the A to E
>>>> cells being concatenated. So instead of a list pf unique records in
>>>> one column I had a 5 column list. Now it just so happens that this
>>>> was *very* useful to me.
>>>>
>>>> It just really surprised me as I never expected it to filter the cells
>>>> being concatenated rather than the actual result of the concatenation.
>>>> WHy did it do this? How would I have filtered just the F column data?
>>>>
>>>> Jay
>>>
>