| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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?? |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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?? |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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?? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Single list ~ not duplicates | Excel Discussion (Misc queries) | |||
| Array Formulas - Unique List from List with Duplicates | Excel Discussion (Misc queries) | |||
| removing duplicates from a list | Excel Discussion (Misc queries) | |||
| Filtering a list with duplicates | Excel Worksheet Functions | |||
| How to remove duplicates from a list and copy new list to new colu | Excel Worksheet Functions | |||
Linear Mode
