Sorting like names within column... 2

[Waxaholic]

I have 2 columns of data. Column 1 has names in it, column 2 data.

John Doe 111111
John Doe 111111
John Doe 135593
Jane Doe 289483
Jane Doe 289483
Jane Doe 345789

How can I extract column 1 and column 2 so as not to have repetitous data (column 2) in the result.

Like this:

John Doe 111111
John Doe 135593
Jane Doe 289483
Jane Doe 345789

Anyone know how this can be done. I have about 27,000 rows of info and hate to do it by hand.

Thanks,

Waxaholic

 

[DaleWatson123321]

Hi Waxaholic,

You simply need to use the following code:

Sub Unique_Extract()

Range("database&quot.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:="unique_crit", _
CopyToRange:=Range("output&quot, _
Unique:=True

End Sub


Steps to be sure to take first before running the extraction code.

1) You will of course need to assign the Range Names which match the VBA code ...i.e. assign "database" to your 2-column database range - down to include the 27,000 rows. The top row of this database needs to have field names.

2) Copy the two field names to a separate sheet, or to separate range on the same sheet. Assign the range name "output" to the two cells. CAUTION: Any data below these two cells will be eliminated during the extraction process.

3) Set up the Criteria: Copy either of the two field names to a separate cell (off to the side, out of the way, or on another sheet. Assign the range name "unique_crit" to the field name and the cell below. Note: Do not enter any data into the cell below the field name (leave it BLANK).

I hope you understand the above. If not, please let me know.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[JVFriederick]

DaleWatson is right on the money, but if you want to skip the macro code and use the Excel menus :
1) Copy the first row of data in column 1&2 to column 3&4 (A=#1, B=#2, etc)
2) Use Data Filter Advanced as follows :

 

[Waxaholic]

Awesome guys. Works like a charm. I appreciate the help both of you have given.

Thanks,

Waxaholic