 |
 | 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 / New Users / June 2007Help comparing data in 2 columns and finding same data in both
Hi, I have a 2 column spreadsheet with numerical data in each column. I need to compare columns and note which numbers are in both columns (preferably in a third column) example: I have this... col A col B 1001855 1001855 1001855 1001866 1001866 1001877 1001866 1001888 1001877 1001899 I need this.... col A col B col C 1001855 1001855 X 1001855 1001866 X 1001866 1001877 X 1001866 1001888 1001877 1001899 A has over 14,000 lines and B has over 10,000 either may have duplicates. Suggestions? Thanks Max Max One way: =IF(COUNTIF(A:A,$B1)>0,"X","") and drag down the column Regards Trevor > Hi, > [quoted text clipped - 28 lines] > Thanks > Max Enter this formula in C1 and copy down to the end of data in column B: =IF(ISNUMBER(MATCH(B1,A:A,0)),"x","") Hint: for fast copying of the formula double click the fill handle (that little "square" on the bottom right of the selected cell). Double clicking the fill handle will copy the formula down the column until it reaches an empty cell in column B. Biff > Hi, > [quoted text clipped - 28 lines] > Thanks > Max Very interesting. When I used Biff's formula not all of the common data was found. When I used Trevor's formula all of the common data appears to have been found. Thank you both for your assistance. Regards Max Hmmm... At the most basic level, both formulas are essentially the same. The MATCH formula is faster to calculate. Biff > Very interesting. > When I used Biff's formula not all of the common data was found. [quoted text clipped - 4 lines] > Regards > Max Maybe there were "numbers" that were really text??? =match() wouldn't find a match, but =countif() would. > Hmmm... > [quoted text clipped - 11 lines] > > Regards > > Max  SignatureDave Peterson Yes, I was just getting ready to explain that. Biff > Maybe there were "numbers" that were really text??? > [quoted text clipped - 17 lines] >> > Regards >> > Max Oh, sure. I get one worksheet function question right and you want to take it away from me! <vbg> > Yes, I was just getting ready to explain that. > [quoted text clipped - 25 lines] > > > > Dave Peterson SignatureDave Peterson I logged in to CDO and voted for your reply: http://img503.imageshack.us/img503/7329/goodanswerdavevs2.jpg Don't know why it didn't give you the green checkmark! Biff > Oh, sure. > [quoted text clipped - 33 lines] >> > >> > Dave Peterson 1 out of 1 found it useful! That's close to 100%. (You have wayyyyyyyy too much time on your hands!) > I logged in to CDO and voted for your reply: > [quoted text clipped - 45 lines] > > > > Dave Peterson SignatureDave Peterson > (You have wayyyyyyyy too much time on your hands!) Yes. Yes I do, but, it looks like you have twice as much as me! http://img525.imageshack.us/img525/6752/freetimetn3.jpg I need a vacation! Biff >1 out of 1 found it useful! That's close to 100%. > [quoted text clipped - 52 lines] >> > >> > Dave Peterson You win! > > (You have wayyyyyyyy too much time on your hands!) > [quoted text clipped - 66 lines] > > > > Dave Peterson SignatureDave Peterson Your lack of vacation is affecting your math skills! Dave has 2.71 times as much free time as you. <g> > You win! > [quoted text clipped - 56 lines] >>>>>>>>>Regards >>>>>>>>>Max SignatureDebra Dalgleish Contextures http://www.contextures.com/tiptech.html And your vacation to the Bahamas, er, back porch is going well, I hope???? > Your lack of vacation is affecting your math skills! > Dave has 2.71 times as much free time as you. <g> [quoted text clipped - 64 lines] > Contextures > http://www.contextures.com/tiptech.html SignatureDave Peterson Well, my math skills are improving! Just don't ask about the book. > And your vacation to the Bahamas, er, back porch is going well, I hope???? > [quoted text clipped - 66 lines] >>Contextures >>http://www.contextures.com/tiptech.html SignatureDebra Dalgleish Contextures http://www.contextures.com/tiptech.html >Your lack of vacation is affecting your math skills! That's not all it's affecting! Biff > Your lack of vacation is affecting your math skills! > Dave has 2.71 times as much free time as you. <g> [quoted text clipped - 59 lines] >>>>>>>>>>Regards >>>>>>>>>>Max Hi Biff, did you compile that table yourself? Pete > > (You have wayyyyyyyy too much time on your hands!) > [quoted text clipped - 68 lines] > > - Show quoted text - No. Debra Dalgleish keeps stats: http://contextures.com/xlngstats.html Biff > Hi Biff, > [quoted text clipped - 75 lines] >> >> - Show quoted text - It looks like it comes from Debra Dalgleish's site: http://contextures.com/xlngstats.html > Hi Biff, > [quoted text clipped - 74 lines] > > > > - Show quoted text - SignatureDave Peterson > Don't know why it didn't give you the green checkmark! Think only* original posters can "give" the greens *perhaps also MVPs if they choose to mark the responses in CDO SignatureMax Singapore http://savefile.com/projects/236895 xdemechanik --- Ah, that makes sense. Thanks, Max. Biff >> Don't know why it didn't give you the green checkmark! > Think only* original posters can "give" the greens > *perhaps also MVPs if they choose to mark the responses in CDO welcome, Biff. SignatureMax Singapore http://savefile.com/projects/236895 xdemechanik --- > Ah, that makes sense. Thanks, Max. > > Biff
|
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.