 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / May 2008Making 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 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. > 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 [quoted text clipped - 40 lines] > Any help would be greatly appreciated. > HDL 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$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(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  SignatureBiff Microsoft Excel MVP > In Excel 2000 I'm at a bit of a loss on how to search a list in column A > and [quoted text clipped - 44 lines] > Any help would be greatly appreciated. > 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 > Another one... > [quoted text clipped - 77 lines] > > Any help would be greatly appreciated. > > HDL You're welcome. Thanks for the feedback! SignatureBiff Microsoft Excel MVP > Thanks Biff, I was able to get the results I was after with your solution. > I [quoted text clipped - 86 lines] >> > Any help would be greatly appreciated. >> > HDL 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?? 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 > How about names or text instead of numbers? > [quoted text clipped - 38 lines] > > is it still possible?? Data>Sort ? SignatureHTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) > How about names or text instead of numbers? > [quoted text clipped - 38 lines] > > is it still possible?? 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)),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 > 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 [quoted text clipped - 40 lines] > Any help would be greatly appreciated. > HDL |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.