Welcome to the Excel Forum

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed.

Excel Help Forum » Microsoft Office Application Help - Excel Help forum » Excel General » need help ASAP

Please Register to Remove these Ads

Please Register to Remove these Ads



Reply
  #1  
Old 06-07-2005, 10:25 AM
pike188 pike188 is offline
Registered User
  
Join Date: 07 Jun 2005
Posts: 2
pike188 is becoming part of the community
need help ASAP

Please Register to Remove these Ads

hey all, I'm having a problem and dont know exactly how to do this...

I have one column of data (2000 lines worth) all are numerical and all are different, this is column A

in column B I have another list of numerical data, most of this data will match the data in column A, but not all.

what I'm trying to do is to pull out of column A all the numbers that have a match in column B,

How do I do this?

thanks

Ryan
Reply With Quote
  #2  
Old 06-07-2005, 10:33 AM
swatsp0p swatsp0p is offline
Forum Guru
  
Join Date: 07 Oct 2004
Location: Minnesota, USA
Posts: 1,537
swatsp0p is becoming part of the community
Send a message via MSN to swatsp0p
What do you mean "...pull out of column A all the numbers that have a match in column B"? Delete them? Highlight them? Put them in another column? Line them up with the match in 'B'? Something else?

More details please...
__________________
Bruce
The older I get, the better I used to be.
Minneapolis, MN USA
Reply With Quote
  #3  
Old 06-07-2005, 10:44 AM
pike188 pike188 is offline
Registered User
  
Join Date: 07 Jun 2005
Posts: 2
pike188 is becoming part of the community
any of the above except delete them, I need to be able to determine whitch ones had a match and whitch ones didnt
Reply With Quote
  #4  
Old 06-07-2005, 10:57 AM
BenjieLop BenjieLop is offline
Forum Guru
  
Join Date: 23 Jun 2004
Location: Houston, TX
Posts: 567
BenjieLop is becoming part of the community
To identify duplicate entries in Column B (as compared to entries in Column A), you can use this formula:

=if(countif($A$1:$A$2000,B1)>0,"Duplicate","")

Enter the above formula in Cell C1 and copy down.

Now that you have identified the duplicate entries, you can then decide what to do with them.

Hope this helps you.

Regards.
__________________
BenjieLop
Houston, TX
Last edited by BenjieLop; 06-07-2005 at 11:00 AM.
Reply With Quote
  #5  
Old 06-07-2005, 11:04 AM
Morrigan Morrigan is offline
Valued Forum Contributor
  
Join Date: 12 Mar 2004
Posts: 323
Morrigan is becoming part of the community
Assume A1:A2000 are numbers that you are trying to match with B1:B2000, you can try this:

C1 = IF(ISERROR(MATCH(B1,$A$1:$A$2000,0)),"No Match","Match")
C2 = IF(ISERROR(MATCH(B2,$A$1:$A$2000,0)),"No Match","Match")
etc.

Now if you want to view all the numbers in column A that also exist in column B, simply turn on autofilter and filter out all the "No Match".


Hope this helps.




Quote:
Originally Posted by pike188
hey all, I'm having a problem and dont know exactly how to do this...

I have one column of data (2000 lines worth) all are numerical and all are different, this is column A

in column B I have another list of numerical data, most of this data will match the data in column A, but not all.

what I'm trying to do is to pull out of column A all the numbers that have a match in column B,

How do I do this?

thanks

Ryan
Reply With Quote


Reply

Bookmarks

« Previous Thread | Next Thread »

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes
Linear Mode Linear Mode

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are Off
Refbacks are Off
Forum Jump

Contact Us - ExcelTip.com - Archive - Top