Extract the unique combinations of values from 2 columns

[Matt Rule]

Hi,

I'm sure this will be easy for most of you, but I'm having trouble with
extracting unique combinations of data from 2 columns. These columns will
both have repeated data.
What I would like to achieve is a list of unique item numbers with their
associated supplier code.
Below is a sample of the data I'm working with. The actual data has 36000 +
rows

SUPPLIER_NO ITEM_NO
A002 01-4102-700
A002 01-4102-710
A002 01-4102-710
A002 01-4102-815
A002 01-4104-710
A004 01-3305-815
A004 01-4010-190

A006 01-4380-815
A006 01-4380-815
A006 01-4380-815
A006 01-4380-815
A006 01-4380-815
A009 01-4100-720
A009 01-4100-800
A009 01-4102-700
A009 01-4102-700
A009 01-4102-700
A009 01-4102-710
A009 01-4102-710
A009 01-4102-710
A009 01-4102-710
A009 01-4102-710
A009 01-4102-710
A009 01-4102-710


I'd like to see a result like this:
SUPPLIER_NO ITEM_NO

A002 01-4102-700

A002 01-4102-710

A002 01-4102-815
A002 01-4104-710

A004 01-3305-815
A004 01-4010-190

A006 01-4380-815

A009 01-4100-720
A009 01-4100-800
A009 01-4102-700

A009 01-4102-710


Any help would be greatly appreciated

Regards
Matt

 

[Govind]

Hi,

Use Filter-Advanced filter and pick unique records only.

Regards

Govind

 

[Max]

One way ..

Taking your sample data to be in A1:B25
(supplier# in col A, item# in col B,
headers in row1, data from row2 down)

SUPPLIER_NO ITEM_NO
A002 01-4102-700
A002 01-4102-710
etc

(Note: Blank rows assumed removed)

Assuming empty cols to the right:

a. Concatenate cols A and B into one col
-------------------------------------------------------
Put in C1: =TRIM(A1)&" "&TRIM(B1)
Copy down to C25

Kill the formulas in col C with an in-place:
Copy > Paste special > Check "Values" > OK

b. Drive out uniques in col C into col D
via Advanced Filter

Select C1:C25
Click Data > Filter > Advanced Filter
Check "Copy to another location" & "Unique records only"
Put in the "Copy to:" box : $D$1
Click OK

This'll extract the uniques into D112
(for the sample data set)

c. Split col D back into the "original" 2 parts
via Data > Text to columns

Select D112
Click Data > Text to columns
Ensure "Delimited" is checked in step 1
Check "space" in step 2
Click Finish

 

[Matt Rule]

Cheers Max,

Thats perfect

Regards
Matt

One way ..

Taking your sample data to be in A1:B25
(supplier# in col A, item# in col B,
headers in row1, data from row2 down)

SUPPLIER_NO ITEM_NO
A002 01-4102-700
A002 01-4102-710
etc

(Note: Blank rows assumed removed)

Assuming empty cols to the right:

a. Concatenate cols A and B into one col
-------------------------------------------------------
Put in C1: =TRIM(A1)&" "&TRIM(B1)
Copy down to C25

Kill the formulas in col C with an in-place:
Copy > Paste special > Check "Values" > OK

b. Drive out uniques in col C into col D
via Advanced Filter

Select C1:C25
Click Data > Filter > Advanced Filter
Check "Copy to another location" & "Unique records only"
Put in the "Copy to:" box : $D$1
Click OK

This'll extract the uniques into D112
(for the sample data set)

c. Split col D back into the "original" 2 parts
via Data > Text to columns

Select D112
Click Data > Text to columns
Ensure "Delimited" is checked in step 1
Check "space" in step 2
Click Finish
--
Rgds
Max
xl 97

 

[Max]

Glad to hear that, Matt !
You're welcome