Making a list out of multiple duplicates
 

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

Making a list out of multiple duplicates
 

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.


"HDL" wrote:

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

Making a list out of multiple duplicates
 

Another one...

Assume your data is on Sheet1 A2:B20

Create this named range:

InsertNameDefine
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$2:D2)<=COUNT(1/FREQUENCY(rng,rng)),SMALL(rng,1),"")

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

=IF(ROWS(D$2:D3)<=COUNT(1/FREQUENCY(rng,rng)),MIN(IF(rngD2,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


--
Biff
Microsoft Excel MVP


"HDL" wrote in message
...
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

Making a list out of multiple duplicates
 

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

d2=IF(ROWS(D$2:D2)<=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)),COUNT IF($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" wrote:

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

Making a list out of multiple duplicates
 

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

Another one...

Assume your data is on Sheet1 A2:B20

Create this named range:

InsertNameDefine
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$2:D2)<=COUNT(1/FREQUENCY(rng,rng)),SMALL(rng,1),"")

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

=IF(ROWS(D$2:D3)<=COUNT(1/FREQUENCY(rng,rng)),MIN(IF(rngD2,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


--
Biff
Microsoft Excel MVP


"HDL" wrote in message
...
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

Making a list out of multiple duplicates
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"HDL" wrote in message
...
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" wrote:

Another one...

Assume your data is on Sheet1 A2:B20

Create this named range:

InsertNameDefine
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$2:D2)<=COUNT(1/FREQUENCY(rng,rng)),SMALL(rng,1),"")

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

=IF(ROWS(D$2:D3)<=COUNT(1/FREQUENCY(rng,rng)),MIN(IF(rngD2,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


--
Biff
Microsoft Excel MVP


"HDL" wrote in message
...
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

names instead of numbers..
 

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??

names instead of numbers..
 

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 "C1:D1" Check Unique records only Ok

Regards,
Per
"edml xodus" skrev i meddelelsen ...
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??

names instead of numbers..
 

DataSort ?

--
HTH

Bob

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

<edml xodus wrote in message ...
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??