 |
 | 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 / September 2005Filtering Columns to Align Matching Data
If I have a list of data in column A, ie: phone numbers, and I also have a list of similar data in column B. Some, but not all numbers in column B match numbers in column A. Is there a way to filter the two columns so that matching data from columns A & B are aligned? from what I understand vlookup function will be useful -- remove $$$ from email addresss to send email ========= > If I have a list of data in column A, ie: phone numbers, and I also have a > list of similar data in column B. [quoted text clipped - 3 lines] > Is there a way to filter the two columns so that matching data from columns > A & B are aligned? R. Venkataraman Thanks for your response but I don't know how to use the vlookup function therefore I still haven't solved my problem. Could you possibly create an example? > from what I understand vlookup function will be useful > [quoted text clipped - 12 lines] > columns > > A & B are aligned? > .. Could you possibly create an example .. One play assuming I've read your underlying intent correctly .. Assume col A contains the "master" list of numbers, and col B contains another list to be checked against the master, data from row1 down, e.g.: 500 385 385 124 489 562 829 489 419 829 etc etc Put in C1: = IF(B1="","",IF(ISNUMBER(MATCH(B1,A:A,0)),ROW(),"")) Put in D1: = IF(B1="","",IF(ISNUMBER(MATCH(B1,A:A,0)),"",ROW())) Put in E1: =IF(ISERROR(SMALL(C:C,ROWS($A$1:A1))),"", INDEX($B:$B,MATCH(SMALL(C:C,ROWS($A$1:A1)),C:C,0))) Copy E1 across to F1 Then just select C1:F1 and copy down until the last row of data in col B Col E returns all the numbers in col B found in col A Col F returns all the numbers in col B not found in col A (Results will be nicely bunched at the top in cols E & F) -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- For the sample data in the example, you'd get in cols E & F: 385 124 489 562 829 -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
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.