Excel XP

Column A has numbers which are repeated many times. Each number corresponds
to a family.

For example:

1234 Smith A
1235 Jones A
1234 Smith A
1236 Jones B
1234 Smith A
1235 Jones A
1234 Smith A
1236 Jones B

How can I extract each number listed only once (omitting duplicates) so I
have:

1234 Smith A
1235 Jones A
1236 Jones B

TIA

Guy

Hi Guy,

Select the data including the column headers (must have column headers) then
Data-Filter-Advanced and make a copy of Unique data. If you can't work out
how to drive Advanced filter then get back to me and let me know your version
of xl and I'll provide some instructions.

Note that the copy must be on the same worksheet.
--
Regards,

OssieMac


"Guy Lydig" wrote:

Excel XP

Column A has numbers which are repeated many times. Each number corresponds
to a family.

For example:

1234 Smith A
1235 Jones A
1234 Smith A
1236 Jones B
1234 Smith A
1235 Jones A
1234 Smith A
1236 Jones B

How can I extract each number listed only once (omitting duplicates) so I
have:

1234 Smith A
1235 Jones A
1236 Jones B

TIA

Guy

Hi,

Thanks for the quick response. I looked this up on the board and I saw a
neat solution using CountIf. Your solution looks easier but I can't make it
work unless I filter in place. What if I want to filter by column A but need
the data from columns B and C?

"OssieMac" wrote:

Hi Guy,

Select the data including the column headers (must have column headers) then
Data-Filter-Advanced and make a copy of Unique data. If you can't work out
how to drive Advanced filter then get back to me and let me know your version
of xl and I'll provide some instructions.

Note that the copy must be on the same worksheet.
--
Regards,

OssieMac


"Guy Lydig" wrote:

Excel XP

Column A has numbers which are repeated many times. Each number corresponds
to a family.

For example:

1234 Smith A
1235 Jones A
1234 Smith A
1236 Jones B
1234 Smith A
1235 Jones A
1234 Smith A
1236 Jones B

How can I extract each number listed only once (omitting duplicates) so I
have:

1234 Smith A
1235 Jones A
1236 Jones B

TIA

Guy

Note that the copy must be on the same worksheet.

It can be on any sheet. If the data is one one sheet and you want to extract
the uniques to a different sheet then you *must* start the process from the
other sheet.

--
Biff
Microsoft Excel MVP


"OssieMac" wrote in message
...
Hi Guy,

Select the data including the column headers (must have column headers)
then
Data-Filter-Advanced and make a copy of Unique data. If you can't work
out
how to drive Advanced filter then get back to me and let me know your
version
of xl and I'll provide some instructions.

Note that the copy must be on the same worksheet.
--
Regards,

OssieMac


"Guy Lydig" wrote:

Excel XP

Column A has numbers which are repeated many times. Each number
corresponds
to a family.

For example:

1234 Smith A
1235 Jones A
1234 Smith A
1236 Jones B
1234 Smith A
1235 Jones A
1234 Smith A
1236 Jones B

How can I extract each number listed only once (omitting duplicates) so I
have:

1234 Smith A
1235 Jones A
1236 Jones B

TIA

Guy

Hi again Guy,

Must have something wrong if cant filter to new location. Following is
instructions to filter one column only.

Select menu item Data-Filter-Advanced filter. (For pre xl 2007 versions)
(for xl 2007 it is Data tab-Filter-Advanced filter)

Select Copy to another location.

List range:
Click on the icon at the right of the field box.
Select the range to be filtered including the column header for the one
column only with the numbers.
Click on the icon at the right of the field box.

Copy to:
Click on the icon at the right of the field box.
Select one cell only somewhere on a blank section of the worksheet. (Select
E1 and it will make the section below easier to understand)
Click on the icon at the right of the field box.

Check Unique records only.

Click OK.

You should now have a list of only the first column with the column header
in cell E1.

Enter the following formula in cell F2:
=VLOOKUP(E2,$A$2:$B$9,2,FALSE)

Note: the range to look in with the above formula is in absolute mode with
the $ signs. This is so that as you copy the formula down, it does not change
like E2 does (which is required to change)

Copy the formula to the bottom of the data in column E.

Note that it only finds the first instance of a name against the numeric
identification.

If you filter on both of the columns and then filter on the numeric id
column then you will be able to easily see if you have duplicate numeric ids
with different names against them.

--
Regards,

OssieMac


"Guy Lydig" wrote:

Hi,

Thanks for the quick response. I looked this up on the board and I saw a
neat solution using CountIf. Your solution looks easier but I can't make it
work unless I filter in place. What if I want to filter by column A but need
the data from columns B and C?

"OssieMac" wrote:

Hi Guy,

Select the data including the column headers (must have column headers) then
Data-Filter-Advanced and make a copy of Unique data. If you can't work out
how to drive Advanced filter then get back to me and let me know your version
of xl and I'll provide some instructions.

Note that the copy must be on the same worksheet.
--
Regards,

OssieMac


"Guy Lydig" wrote:

Excel XP

Column A has numbers which are repeated many times. Each number corresponds
to a family.

For example:

1234 Smith A
1235 Jones A
1234 Smith A
1236 Jones B
1234 Smith A
1235 Jones A
1234 Smith A
1236 Jones B

How can I extract each number listed only once (omitting duplicates) so I
have:

1234 Smith A
1235 Jones A
1236 Jones B

TIA

Guy

you *must* start the process from the other sheet.

Well, you can always extract to the same sheet then cut/paste to the other
sheet but that's an extra step!


--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Note that the copy must be on the same worksheet.

It can be on any sheet. If the data is one one sheet and you want to
extract the uniques to a different sheet then you *must* start the process
from the other sheet.

--
Biff
Microsoft Excel MVP


"OssieMac" wrote in message
...
Hi Guy,

Select the data including the column headers (must have column headers)
then
Data-Filter-Advanced and make a copy of Unique data. If you can't work
out
how to drive Advanced filter then get back to me and let me know your
version
of xl and I'll provide some instructions.

Note that the copy must be on the same worksheet.
--
Regards,

OssieMac


"Guy Lydig" wrote:

Excel XP

Column A has numbers which are repeated many times. Each number
corresponds
to a family.

For example:

1234 Smith A
1235 Jones A
1234 Smith A
1236 Jones B
1234 Smith A
1235 Jones A
1234 Smith A
1236 Jones B

How can I extract each number listed only once (omitting duplicates) so
I
have:

1234 Smith A
1235 Jones A
1236 Jones B

TIA

Guy

Thanks. Now I understand. However, since I have 8 additional columns of data
I would need 8 VLOOKUPS to transfer all the columns to my unique values.
Thanks for your help. This or CountIf would serve the purpose.

Thanks for your help. I appreciate it.

"OssieMac" wrote:

Hi again Guy,

Must have something wrong if cant filter to new location. Following is
instructions to filter one column only.

Select menu item Data-Filter-Advanced filter. (For pre xl 2007 versions)
(for xl 2007 it is Data tab-Filter-Advanced filter)

Select Copy to another location.

List range:
Click on the icon at the right of the field box.
Select the range to be filtered including the column header for the one
column only with the numbers.
Click on the icon at the right of the field box.

Copy to:
Click on the icon at the right of the field box.
Select one cell only somewhere on a blank section of the worksheet. (Select
E1 and it will make the section below easier to understand)
Click on the icon at the right of the field box.

Check Unique records only.

Click OK.

You should now have a list of only the first column with the column header
in cell E1.

Enter the following formula in cell F2:
=VLOOKUP(E2,$A$2:$B$9,2,FALSE)

Note: the range to look in with the above formula is in absolute mode with
the $ signs. This is so that as you copy the formula down, it does not change
like E2 does (which is required to change)

Copy the formula to the bottom of the data in column E.

Note that it only finds the first instance of a name against the numeric
identification.

If you filter on both of the columns and then filter on the numeric id
column then you will be able to easily see if you have duplicate numeric ids
with different names against them.

--
Regards,

OssieMac


"Guy Lydig" wrote:

Hi,

Thanks for the quick response. I looked this up on the board and I saw a
neat solution using CountIf. Your solution looks easier but I can't make it
work unless I filter in place. What if I want to filter by column A but need
the data from columns B and C?

"OssieMac" wrote:

Hi Guy,

Select the data including the column headers (must have column headers) then
Data-Filter-Advanced and make a copy of Unique data. If you can't work out
how to drive Advanced filter then get back to me and let me know your version
of xl and I'll provide some instructions.

Note that the copy must be on the same worksheet.
--
Regards,

OssieMac


"Guy Lydig" wrote:

Excel XP

Column A has numbers which are repeated many times. Each number corresponds
to a family.

For example:

1234 Smith A
1235 Jones A
1234 Smith A
1236 Jones B
1234 Smith A
1235 Jones A
1234 Smith A
1236 Jones B

How can I extract each number listed only once (omitting duplicates) so I
have:

1234 Smith A
1235 Jones A
1236 Jones B

TIA

Guy

Thanks for that info Biff. I just love it when I learn something new.
--
Regards,

OssieMac


"T. Valko" wrote:

you *must* start the process from the other sheet.

Well, you can always extract to the same sheet then cut/paste to the other
sheet but that's an extra step!


--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Note that the copy must be on the same worksheet.

It can be on any sheet. If the data is one one sheet and you want to
extract the uniques to a different sheet then you *must* start the process
from the other sheet.

--
Biff
Microsoft Excel MVP


"OssieMac" wrote in message
...
Hi Guy,

Select the data including the column headers (must have column headers)
then
Data-Filter-Advanced and make a copy of Unique data. If you can't work
out
how to drive Advanced filter then get back to me and let me know your
version
of xl and I'll provide some instructions.

Note that the copy must be on the same worksheet.
--
Regards,

OssieMac


"Guy Lydig" wrote:

Excel XP

Column A has numbers which are repeated many times. Each number
corresponds
to a family.

For example:

1234 Smith A
1235 Jones A
1234 Smith A
1236 Jones B
1234 Smith A
1235 Jones A
1234 Smith A
1236 Jones B

How can I extract each number listed only once (omitting duplicates) so
I
have:

1234 Smith A
1235 Jones A
1236 Jones B

TIA

Guy