Matching 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 

0
2/21/2008 10:58:15 PM
excel 39879 articles. 2 followers. Follow

6 Replies
529 Views

Similar Articles

[PageSpeed] 23

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

"Steve Boyle" <waylandboyle@comcast.net> wrote in message 
news:DC99A9F2-B842-49A9-9084-D9A31135E413@microsoft.com...
> 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 


0
Tyro (331)
2/21/2008 11:32:22 PM
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")


On Thu, 21 Feb 2008 15:58:15 -0700, "Steve Boyle"
<waylandboyle@comcast.net> wrote:

>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 
0
s8234 (19)
2/22/2008 12:17:27 AM
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
"Tyro" <Tyro@hotmail.com> wrote in message 
news:aSnvj.13146$Ej5.5858@newssvr29.news.prodigy.net...
> 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
>
> "Steve Boyle" <waylandboyle@comcast.net> wrote in message 
> news:DC99A9F2-B842-49A9-9084-D9A31135E413@microsoft.com...
>> 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
>
> 

0
2/22/2008 12:54:50 AM
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

"Steve Boyle" <waylandboyle@comcast.net> wrote in message 
news:D18B1DE0-909F-45AC-AE66-E11A743EA9A2@microsoft.com...
> 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
> "Tyro" <Tyro@hotmail.com> wrote in message 
> news:aSnvj.13146$Ej5.5858@newssvr29.news.prodigy.net...
>> 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
>>
>> "Steve Boyle" <waylandboyle@comcast.net> wrote in message 
>> news:DC99A9F2-B842-49A9-9084-D9A31135E413@microsoft.com...
>>> 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
>>
>>
> 


0
Tyro (331)
2/22/2008 1:19:36 AM
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

"Tyro" <Tyro@hotmail.com> wrote in message 
news:Iqpvj.13161$Ej5.8161@newssvr29.news.prodigy.net...
> 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
>
> "Steve Boyle" <waylandboyle@comcast.net> wrote in message 
> news:D18B1DE0-909F-45AC-AE66-E11A743EA9A2@microsoft.com...
>> 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
>> "Tyro" <Tyro@hotmail.com> wrote in message 
>> news:aSnvj.13146$Ej5.5858@newssvr29.news.prodigy.net...
>>> 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
>>>
>>> "Steve Boyle" <waylandboyle@comcast.net> wrote in message 
>>> news:DC99A9F2-B842-49A9-9084-D9A31135E413@microsoft.com...
>>>> 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
>>>
>>>
>>
>
> 


0
Tyro (331)
2/22/2008 1:45:30 AM
It is not clear to me. Are there ONLY account numbers in column A?
Scott

On Thu, 21 Feb 2008 15:58:15 -0700, "Steve Boyle"
<waylandboyle@comcast.net> wrote:

>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 
0
s8234 (19)
2/22/2008 5:27:19 AM
Reply:

Similar Artilces:

How do I preserve text color when combining cell data
I'm using "&" to combine data from multiple cells. Each cell has text of a different color and I want to preserve those colors in the new combined cell. The new combined cell is formatting all the text to one color. Anyone have any suggestions on how to maintain the separate colors in the new cell? A formula can only return a value to a cell, so you cannot return formatting and such. =A1&A2 will return the value of A1 and A2 to the cell that has that formula entered in it. HTH Regards, Howard "CraigS" <CraigS@discussions.microsof...

Matching function or no match
I am using this formula to compare 2 list to see which numbers are not included in both column A and Col C. The formula I am using below pulls out the ones that i do have a match . Is there another function that I could use that would only pull out the ones that there is not a match? =IF(ISERROR(MATCH(A1,$C$1:$C$5,0)),"",A1) Thanks Uh =IF(ISERROR(MATCH(A1,$C$1:$C$5,0)),A1,"") -- HTH Bob "Donna" <donna@yahoo.com> wrote in message news:BE688A2B-3D42-45A0-AA3F-B65034C685E1@microsoft.com... > > I am using this for...

Extracting Data from Excel
Hi, Is it possible to use excel like a CRM software? Currently i am using excel to store my customer list, requests and enqueries. I would like to extract data from those information such as by company, location, and status. What would be the best way to implement this? Is there any guide to do this? Thanks in advance Regards, MY How about use of Autofilter & Advanced Filter? MVP Debra Dalgleish has some nice illustrations at: http://www.contextures.com/tiptech.html Check out the relevant links on her home page -- hth Max ----------------------------------------- Please reply in n...

not wordwrapping last two lines in cell
In cell 3C I have 18 lines that will wordwrap. the last two sentences will not wordwrap the last two sentences list about 10 words each and then run off the cell and cannot be seen. Any ideas how to fix this Thanks in Advance Does this link help: http://support.microsoft.com/default.aspx?scid=kb;en-us;211580 "badgercat" wrote: > In cell 3C I have 18 lines that will wordwrap. > the last two sentences will not wordwrap > the last two sentences list about 10 words each and then run off the cell > and cannot be seen. > > Any ideas how to fix this > > Tha...

Client E-Mail and Web E-Mail not matching
I've got a few complaints from about 4 users where e-mail they see in their Outlook Client is not showing up in their Outlook Web Client when they check their e-mail from a remote location. We're using Exchange 2003 with SP2 installed and all the users have Office Outlook 2003 with SP1 Installed also. This seems to only have been a problem since we upgraded to Exchange SP2 two weeks ago. (Although I can't confirm it, but I wasn't told of any problems before th upgrade.) We had to upgrade to SP2 because of the 16 Gig limit so rollback is not an option. Best as I can t...

deduplicate data in excel
How do I deduplicate data in Excel. I have a large database of names and addresses which I want to check to duplication You may want to read some of Chip Pearson's techniques for dealing with duplicates: http://www.cpearson.com/excel/duplicat.htm Julie Melbourne wrote: > > How do I deduplicate data in Excel. I have a large database of names and > addresses which I want to check to duplication -- Dave Peterson Thanks Dave I have looked at this site, but cannot get the formula to work? Must be doing something wrong. Have you tried it? Do you have a working spreadsheet wtih...

Matching cells #3
Here's my spreadsheet: A B C 1 1 1 2 3 3 3 4 6 4 5 10 5 8 11 6 9 7 10 8 9 10 11 12 13 How do I get the matching numbers on the same rows, like: A B C 1 1 1 2 3 3 3 4 4 5 5 6 6 7 8 8 9 9 10 10 10 11 11 12 13 13 I would just re-create the columns. 1. Copy column A into D. 2. In E1 put: =IF(COUNTIF(B:B,$A1),$A1,"") 3. Copy it over to F1 and then down as far as needed. 4. Select columns D thru F, copy them, and go to Edit > Paste Special > Value. Press OK. 5. Delete column A thru C. HTH Jason Atlanta, GA &g...

Searching within coloumns
I need some advice or example code on how to search within a coloumn. I have one coloumn of keywords seperated by commas. each row has corrisponding file. I want to write a macro which starts with a box t input search term and then goes away and searchs the coloumn for tha word, if possible a find next function aswell. When it finds the wor it should highlight the row. Thanks in advance Rober -- Message posted from http://www.ExcelForum.com Hi Robert, Sounds like something you could do with Conditional Formatting If it is in column A that you want to test you could put a value to be test...

Move data to right with variable rows
I have a large spreadsheet with corrupted data. I can identify several types of bad data and have correct the data in these rows but one particluar type is giving me problems. Because the spreadsheet is large I used filtering and visible cell commands to correct blocks of data. This seems to work fine but one type is still not working so I am trying a row by row logic search. In this case the bad data needs to move from cell column B to C with the range through L. So any data in the range B through L in the selected row is copied if the contents of L ="N". A cel...

Lookup Formula: Return 1st match, then 2nd match, then 3rd match
I have a spreadsheet that looks like the following. Tab 1. Col A=Name. Col B = Task. A B Bob Reconcile Cash Tim Do Sales Report Bob Create presentation Tim Prepare financial statements Bob Hire staff person Now on tab two, I want to create another list that pulls all the tasks together by person. For example, Tab 2 would look like this: Bob Reconcile Cash Bob Create presentation Bob Hire staff person Tim Do Sales Report Tim Prepare financial statement. I realize I can just sor...

Many to Many Data Integrity (Alway confuse myself)
OK, thanks in advance. I always screw this up so here it goes: Three tables: Line, Part, LinetoPart I could set these tables up differently, but Part A can be ran on Line1, 2, or 3. LinetoParts has autonumber pkLinetoPart, fkLine, and fkPart. So how do I keep the many table from duplicating say an entry stating that Part A to Line 1? Am I missing the boat here? In the LinetoParts pkParts can duplicate and pkLine can duplicate, but I don't want a duplicate record with the same pkPart and pkLine. *Relationship Algebra why do you plague me so?* At the nuts and bolts level, wh...

Index Match Functions
Has anyone ever combined the Index and Match functions to do lookups? ...

Not matching transactions
I have it set to not automatically do anything. I've tried resetting the account quite a few times with the help of technicians, but it's too long and involved, and I'm tired of rebalancing the account every time. I have 3 accounts, 1 works without passport and does fine. One of the others I can manually download from the web site, and have no problems doing it like so and decided this is the best way, prior tries it wouldn't work with passport. But the other still insists they are new transactions, and I can't download from their site directly. They only allow...

Average only cells >0
Is there any way to create a formula that takes an average of all cells in range that are greater than the value of zero? If the value is zero, it should not be included in the average. Thank you! ...

How do I import large data files?
When I try to import a data file with more rows than Excel, the text box says I can import the rest by re-importing the file and excluding the lines already imported. The Import Wizard doesn't seem to give me this option... suggestions? Hi Russell Maybe http://support.microsoft.com/default.aspx?scid=kb;EN-US;q120596 XL: Importing Text Files Larger Than ???? Rows -- Regards Ron de Bruin http://www.rondebruin.nl "Russell Seguin" <Russell Seguin@discussions.microsoft.com> wrote in message news:184036AB-8922-4EAA-85B6-05FAB3E577F5@microsoft.com... > When I try to...

Extract X data from Chart
I have looked through the information provided by this group for chart events, and I have a good code to get the embedded chart to show the message box with the point information. (Thanks Jon Peltier) What I would like to do now is to extract that X data and paste it into another area of the workbook to run some other analysis. Is there some way to have the X value be copied into another cell on another sheet? I am using this code in a MouseUp event: Dim ElementID As Long, Arg1 As Long, Arg2 As Long Dim myX As Variant, myY As Double With ActiveChart ' Pass x & ...

550 [PERMFAIL] destination not valid within DNS
Hello, I'm using Outlook 2002. I can receive emails OK - but cannot send emails. I get the error message "550 [PERMFAIL] destination not valid within DNS". I used to be able to send and receive OK. Recently I installed XP SP2 and change providers. So I'm not sure which one is causing the problem. Would anyone have information that will help me troubleshoot this issue? Regards, Bentwood Bentwood wrote: > Hello, > > I'm using Outlook 2002. I can receive emails OK - but cannot send > emails. > I get the error message "550 [PERMFAIL] destinati...

refresh external data on a protected sheet
I have a worksheet that has an external data area and some other sections that need to be protected. I have unlocked all the columns used by the external data. When I protect the sheet, I can no longer refresh the external data area. Even if the entire worksheet is unlocked, the Refresh button is unavailable. ...

Scatter with Dynamic (Array) Data
Hi, I'm trying to create a scatter plot using Array's instead of using Cell's in a worksheet but can't seem to get it to work. Here's what I'd LIKE to be able to do but it keeps bombing. Any ideas? JS ======= Sub simple_array_table() Dim myXvalues(1#, 2#, 3#) As Double Dim myYvalues(4, 2, 4) As Double Charts.Add ActiveChart.ChartType = xlXYScatter ActiveChart.SeriesCollection(1).xvalues = myXvalues ActiveChart.SeriesCollection(1).Values = myYvalues End Sub I figured out the problem. Or, more correctly stated, I figured out a way aroun...

Data Validation/Fill question
I have a cell in my workbook that I would like to have filled from a dropdown list. The possible option number about 30 so a "Data Validation" would be unwieldly unless I could nest it, as the possibilities fall into 3 to 4 catagories. Is there a way to do this? I thought about a combo box, but I want the same dropdown to be usable by approx. 120 cells. Is there a macro that could be done in wich I select the cell and then a pop-up window will allow the fill, or some way around this? I am using Excel 97. I think this might be a good place to start: http://www.contextur...

Error message regarding updating american express data
When updating my accounts online I receive a message indicating there was an error downloading from american express. It indicates the account may be closed or the number may have changed. Neither is the case, and the update completes and downloads the data from american express correctly. Any suggestions on eliminating the message? AMEX has been having problems for the last two days and I can't even sign on to the AMEX website this morning to THAT get that message today. It appears to be an AMEX problem based on the message on their website yesterday. -- PlsHelpMScan't ...

Excel pivot tables using Access as the data source
I can successfully create the link between my Excel pivot tables to my Access data base, however Excel will only look for the data in the specific path in which it was created. How can I have my pivot table find and read the Access data even if I move the Access file to another directory This mainly causes me problems when I distribute my Excel reports. I bundle the Access database with the Excel report but my instruction to the receiver must instruct them to create a specifically named folder under the C drive and store both the Excel pivot table and Access database otherwise the Excel p...

data in row didnt match import type 12-30-05
while importing almost 800 accounts , i got the following error for majority of accounts 'Data in row did not match the import type' what is the solution? How can i match it? whats is the way of comparision? It looks like a data problem. You need to check if all dates are correct (and filled), if numbers are numbers (not letters), etc... Regards, -- Erik van Hoof CWR Mobility Check our weblog at: http://www.cwrmobility.com/weblog "Aam" <Aam@discussions.microsoft.com> wrote in message news:C51E8ED0-443D-4B35-B436-0A9EDB09B419@microsoft.com... > while impo...

Start a file at a defaulted worksheet/cell?
Howdy, Having a few users to a file, can I have the file open to a default worksheet/cell as I have directions I want them to read before playing... Regards, Kevin Kevin, use something like this, put in thisworkbook code Private Sub Workbook_Open() Sheets("Sheet2").Select Range("C3").Select End Sub -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2000 & 97 ** remove news from my email address to reply by email ** "...

Sort Data Out of One Cell
I've been given a spread sheet that has the customer name and thier customer number in the same cell. Is there a way that I can sort out the customer number to another cell so that a sort can be done by custome number. EXAMPLE:[ ACE HARDWARE 889425 ] (all in one cell) Check out TTC - Text To Columns - in the Help files. That should be perfect for what you need. Post back with any questions. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! ------------------------...