Making a list out of multiple duplicates

[HDL]

In Excel 2000 I'm at a bit of a loss on how to search a list in column A and
return all the unique matches to items in column B. Column A is already
sorted in asending order. I want the resulting data to be in columns D and E
without all the duplicates in column A.

A B
120 143
120 93
120 143
120 143
160 118
160 143
160 118
160 143
160 293
170 118
170 143
170 168
170 218
170 118
170 168
170 218
170 118
170 168
170 218

The result should be;

D E
120 93
120 143
160 118
160 143
160 293
170 118
170 143
170 168
170 218

I think I've read just about every post I could find and tried a few of the
equations that I thought were close to what I want to have done, but I still
can't quite get it.
Any help would be greatly appreciated.
HDL

 

[JMB]

One way - lets say your data is in A2:A20. Enter this in C2

=MATCH(A2&" "&B2,A$2:A$20&" "&B$2:B$20,0)=ROW()-MIN(ROW(A$2:A$20))+1

and hit Ctrl+Shift+Enter (or you get #VALUE). Copy down. Then use the
autofilter and filter Column C for TRUE. Copy/paste the results of the
filter to D2. Turn off the filter, delete the formula in column C, and sort
the results in columns D and E however you wish.

 

[T. Valko]

Another one...

Assume your data is on Sheet1 A2:B20

Create this named range:

Insert>Name>Define
Name: rng
Refers to:

=INDEX(Sheet1!$A$2:$A$20+(Sheet1!$B$2:$B$20/1000),,1)

Enter this formula in D2:

=IF(ROWS(D$22)<=COUNT(1/FREQUENCY(rng,rng)),SMALL(rng,1),"")

Enter this array formula** in D3 and copy down until you get blanks:

=IF(ROWS(D$23)<=COUNT(1/FREQUENCY(rng,rng)),MIN(IF(rng>D2,rng)),"")

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

Enter this formula in E2:

=IF(D2="","",INT(D2))

Enter this formula in F2:

=IF(D2="","",MOD(D2,1)*1000)

Select E2 and F2 and copy down until you get blanks

 

[yshridhar]

Table range : a2:b20
c2=A2+B2/1000

d2=IF(ROWS(D$22)<=SUM(N($C$2:$C$20<>"")),INDEX($C$2:$C$20,MATCH(SMALL(COUNTIF($C$2:$C$20,"<="&$C$2:$C$20),SUM(N($C$2:$C$20=""))+ROWS($C$2:C2)),COUNTIF($C$2:$C$20,"<="&$C$2:$C$20),0)),"")

e2=IF(ISNA(MATCH(0,COUNTIF($E$1:E1,$D$2:$D$20),0)),"",INDEX($D$2:$D$20,MATCH(0,COUNTIF($E$1:E1,$D$2:$D$20),0)))

Copy the range upto required

D2, E2 are array formulae. (CTRl+SHIFT+Enter)
best wishes
Sreedhar

 

[HDL]

Thanks Biff, I was able to get the results I was after with your solution. I
tried the other suggestions but I either entered them in wrong or messed them
up when I adjusted them to fit my actual spreadsheet. Thanks to all who
replied. HDL

 

[T. Valko]

You're welcome. Thanks for the feedback!

 

[edml xodus]

How about names or text instead of numbers?

A B
Mary Jackson
Lucy Henderson
Cheryl Jackson
Joline Denilson
Joline Jackson
Mary Denilson
Cheryl Henderson
Mary Jackson
Lucy George
Joline George
Favian George
Favian Max
Cheryl Henderson
Krystal Henderson
Lucy Henderson
Krystal Max
Joline Henderson
Favian Max
Mary Denilson

The result should be;

D E
Cheryl Jackson
Cheryl Henderson
Favian George
Favian Max
Joline Denilson
Joline Jackson
Joline George
Joline Henderson
Krystal Henderson
Krystal Max
Lucy Henderson
Lucy George
Mary Jackson
Mary Denilson

is it still possible??

 

[Per Jessen]

Hi
It would be nice if you tried to describe what you want to acheive in
words...

Select Column A:B, then goto Data > Sort > Sort by "Column A", Then by
"Column B" > Ok

With the list selected, goto Data > Filter > Advanced filter > Copy to
another location > Copy to "C11" > Check Unique records only > Ok

Regards,
Per
"edml xodus" skrev i meddelelsen news:[email protected]...

 

[Bob Phillips]

Data>Sort ?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)