Advanced FIlter behaviour?

[Jay]

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]

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]

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

 

[Pete_UK]

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]

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