I would insert a new worksheet and copy one of the columns (plus a
header - you must have a header, even if it just says "Number") into
A1 of the new sheet. If you have less than 16k records then you can
combine all the data into this one column. Press <end> once followed
by <down-arrow>, then position the cursor in the next blank cell. Then
click over to the first sheet, highlight the second set of data
(without the header), click <copy> then click over to the new sheet
and press <Enter>. Press <end> followed by <down-arrow> again and
position cursor in the next blank cell. Then copy the third range of
data and repeat once more for the fourth range - you now have all the
data from the four columns in column A of the new sheet, together with
a header in cell A1. (If you have more than 16k records then you will
have to do what follows separately for each column).
Highlight all the combined data from A1 downwards, and click Data |
Sort - click Header Row if not already selected, then click OK - your
combined data is now sorted, but leave it highlighted.
Then click on Data | Filter | Advanced Filter and in the pop-up you
should click on Unique Records only and click on Copy to another
location - in the Copy To panel you should enter C1, then click OK.
You will now have your reduced list of non-duplicated items sorted in
C2 downwards - just like you would have in an autofilter pull-down.
Hope this helps.
Pete
I have 4 non-adjacent columns of numeric data. I would like to create a
list of each distinct entry that appears in those 4 columns. I would like to
produce something similar to that which appears in the dropdown menu after
you have applied a filter to a column [after (All) (Top 10...) (Custom...), and
before (Blanks) (NonBlanks)], but as applied to all 4 columns, and have it
appear as actual data I can enter into a separate column. How would you
suggest I proceed?
Mick
