 |
 | 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 / February 2008Matching Data within Cells
First I am a newbie to this community but I am by no means a newbie to Excel. I have a need where I work to match two different columns of data as follows. Anyone that can help will be my savior. Problem to solve: Cell A2 contains a 16 digit number stored as text (Account Number) This is one of several such cells in column A Column E contains a list of Account numbers from another report that has been copied to the work sheet Solution required: search Column E for a match to Cell A2 and indicate that there is a match in some manner If possible could more than one cell be compared to more than one column with a match having to be all or none. Thanks in advance for your help. Steve Boyle Assuming your column E entries are in E1:E100 then =COUNTIF(E1:E100,A2) will tell you how many times A2 matches the entries in E2:E100 If your values are in C1:E10 and the values you want to count are in A1:A5 then the array formula =SUM(COUNTIF(C1:E10,INDIRECT("A1:A5"))) (Press Ctrl+Shift+Enter) That formula will sum the number of times each entry in A1:A5 occurs in C1:E10. I'm not sure what you mean by "all or none" Tyro > First I am a newbie to this community but I am by no means a newbie to > Excel. I have a need where I work to match two different columns of data [quoted text clipped - 16 lines] > > Steve Boyle Tyro: All or None means if comparing 3 items All must match for a match but if only one or two match it is not a complete match. Does that explain it? Steve > Assuming your column E entries are in E1:E100 then =COUNTIF(E1:E100,A2) > will tell you how many times A2 matches the entries in E2:E100 [quoted text clipped - 30 lines] >> >> Steve Boyle This array formula returns TRUE if any of the values in A1:A5 does not appear in C1:E10 and FALSE if all appear =IF(OR(COUNTIF(C1:E10,INDIRECT("A1:A5"))=0),TRUE,FALSE) (Ctrl+Shift+Enter) Tyro > Tyro: > All or None means if comparing 3 items All must match for a match but [quoted text clipped - 37 lines] >>> >>> Steve Boyle As a follow-up, the array formulas treat blanks as equal to 0's, but in your case with the 16 digit account numbers that may not be an issue. Tyro > This array formula returns TRUE if any of the values in A1:A5 does not > appear in C1:E10 and FALSE if all appear [quoted text clipped - 44 lines] >>>> >>>> Steve Boyle Assuming you have a header row A(Account#) | B...| C...| D...| E(Lookups)| F(found) if The account numbers are in range A2:A5000 and what you are searching for is in E2:E5000 F could have this formula, Insert this in F2 and drag it down as needed =IF(ISNA(VLOOKUP(E2,$A$2:$A$5000,1,FALSE)),"n","y") >First I am a newbie to this community but I am by no means a newbie to >Excel. I have a need where I work to match two different columns of data as [quoted text clipped - 16 lines] > >Steve Boyle It is not clear to me. Are there ONLY account numbers in column A? Scott >First I am a newbie to this community but I am by no means a newbie to >Excel. I have a need where I work to match two different columns of data as [quoted text clipped - 16 lines] > >Steve Boyle |
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.