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

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

On Jul 28, 10:06 pm, Motown Mick
wrote:
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

Dear Pete:

Thank you, that appears to have worked splendidly. The list I wanted to
create was a coding list. I did a VLOOKUP operation on it, and the numeric
codes corresponded to the kind of written identification I expected it would.
Thanks again!

Mick

"Pete_UK" wrote:

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

On Jul 28, 10:06 pm, Motown Mick
wrote:
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

You're welcome, Mick - thanks for feeding back.

Pete

On Jul 30, 10:14 pm, Motown Mick
wrote:
Dear Pete:

Thank you, that appears to have worked splendidly. The list I wanted to
create was a coding list. I did a VLOOKUP operation on it, and the numeric
codes corresponded to the kind of written identification I expected it would.
Thanks again!

Mick



"Pete_UK" wrote:
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

On Jul 28, 10:06 pm, Motown Mick
wrote:
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- Hide quoted text -

- Show quoted text -