Comparing two columns of data and Highlighting the Differences

I have values (mix of text and numbers)in column A and B. Each cell
value in column B is also in column A. I want to highlight (Change
Fill color to Red) for example) all those cells in column A that are
also present in the column B. Any help writting a Macro will be highly
appreciated. Thanks in advance.
Harapa
0
harap99a (6)
3/3/2004 9:58:01 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
817 Views

Similar Articles

[PageSpeed] 28

No macro needed. Select Column A's Data. Use Format/Conditional Formatting.
Change "Cell Value Is" to "Formula Is"
Assuming A1 is the active cell, enter this formula:
=NOT(ISERROR(MATCH(A1,$B$1:$B$500,0)))
click Format, click Patterns tab, select Red fill pattern.
Click OK till you're out.

Bob Umlas
Excel MVP


"Harapa" <harap99a@yahoo.com> wrote in message
news:bc4e9b0c.0403031358.39655dc9@posting.google.com...
> I have values (mix of text and numbers)in column A and B. Each cell
> value in column B is also in column A. I want to highlight (Change
> Fill color to Red) for example) all those cells in column A that are
> also present in the column B. Any help writting a Macro will be highly
> appreciated. Thanks in advance.
> Harapa


0
rumlas (268)
3/3/2004 10:03:33 PM
You can use conditional formatting. While in cell 'A1' 
Click Format/Conditional Formatting.  
Choose 'Cell Value is' and 'equal to' and enter =$B1. 
Choose a format and click OK.
While still in 'A1' Click on paint and paint down the 
number of cell necessary.

Charlie O'Neill
>-----Original Message-----
>I have values (mix of text and numbers)in column A and B. 
Each cell
>value in column B is also in column A. I want to 
highlight (Change
>Fill color to Red) for example) all those cells in column 
A that are
>also present in the column B. Any help writting a Macro 
will be highly
>appreciated. Thanks in advance.
>Harapa
>.
>
0
anonymous (74722)
3/4/2004 3:45:20 AM
Thanks alot. It did it.
Harapa.
"Bob Umlas" <rumlas@kpmg.com> wrote in message news:<#qzNgtWAEHA.740@TK2MSFTNGP12.phx.gbl>...
> No macro needed. Select Column A's Data. Use Format/Conditional Formatting.
> Change "Cell Value Is" to "Formula Is"
> Assuming A1 is the active cell, enter this formula:
> =NOT(ISERROR(MATCH(A1,$B$1:$B$500,0)))
> click Format, click Patterns tab, select Red fill pattern.
> Click OK till you're out.
> 
> Bob Umlas
> Excel MVP
> 
> 
> "Harapa" <harap99a@yahoo.com> wrote in message
> news:bc4e9b0c.0403031358.39655dc9@posting.google.com...
> > I have values (mix of text and numbers)in column A and B. Each cell
> > value in column B is also in column A. I want to highlight (Change
> > Fill color to Red) for example) all those cells in column A that are
> > also present in the column B. Any help writting a Macro will be highly
> > appreciated. Thanks in advance.
> > Harapa
0
harap99a (6)
3/6/2004 5:43:18 PM
Reply:

Similar Artilces:

compare
Hello everyone, I have been reading this group for a while, however I am quite a rookie in using of excel. I would appreciate if someone could help with this issue. I quite often have to compare two sheets (from different workbooks-files, but with the same sheet name). Calculations are thus updated time by time and I need to check where were the main differences. Cells contain both values and formulas. I have found through this newsgroup nice add-ins of Myrna Larson and Bill Manville, and Rob Bruce. However I would need the macros to highlight only significant differences (let's say fro...

UnHide Columns in Excel 2002
In Office XP in Excel I can not select two columns it automatically expands the selection to include the Merged Row above it which includes all the columns below - not just the two I selected. When I select just two cells the right click does not include the option to "Unhide" or "Hide". I have to go to the format menu, select column and select "Unhide" or "Hide". Is there a way to select columns without including the merged rows within the spreadsheet? Is there a shortcut to "Hide" or "UnHide" with a right click or keyboar...

How do I print 4 different postcards on the same page?
I am trying to print a mail merged document of postcards. I only need one copy of each post card. The only options I can find are to either print four of the same postcard or one per page. I would like to print four different post cards on one page. Does anyone have any idea how I can do this. You're doing a mail merge for four postcards? "EMCL" <EMCL@discussions.microsoft.com> wrote in message = news:BA4A5215-CA5F-4144-941C-295770B06219@microsoft.com... > I am trying to print a mail merged document of postcards. I only need = one=20 > copy of each post car...

How to make two codes "Worksheet_Change" work together in same sheet code page
Friends, Please, anybody knows how to make these two VB codes work together? When I put them together in the same "sheet code page" in VBA, th second one doesn't work. Why? *** Code 1 *** Private Sub Worksheet_Change(ByVal Target As Range) * * On Error GoTo QuitCode * * If Intersect(Target, Range("c1:c15")) Is Nothing Then * * * * Exit Sub * * ElseIf Target.Value <>*"" And Target.Offset(0, -1).Value = "" Then * * * * MsgBox "You haven't typed the name of the client yet." * * * * Target.Offset(0, -1).Activate End If QuitCode: ...

Two Email Accounts not able to reply out of one of them
Hello All, I was wondering if someone here has ran into this problem. I currently have two email accounts setup in Exchange I can receive email in both of these accounts and view the email in Outlook 2003. In my default account if I receive an email I can reply back. But in my secondary mailbox if I receive an email I cannot reply back I get a error message which is listed below. On the secondary account I have added my username under permissions and gave it full access but I am still getting the below error. Anything will help thanks. Your message did not reach some or all of the inten...

Comparing
I have two columns of numbers column A could be up to 3500 rows. Column B may be more or less than 3500 rows. What I'm trying to do is see if any number in columns "B" appears anywhere in Column "A", and if so we can just highlight it in both columns. Actually I would want to be able to sort by highlighted. so maybe add someway to sort by matched or unmatched. I hope this makes sense. In column C put... =IF(ISNA(VLOOKUP($A1,$B:$B,1,FALSE)),"Not in B","In B") In column D put... =IF(ISNA(VLOOKUP($B1,$A:$A,1,FALSE)),"Not in ...

Convert Rows data
Hi there, I have the following sample data: PARTNUMBER QTY RATe PRUN VALID_FROM_VALID_TO ZWR1 0.1095 USD 1 20050512 20050526 XREZ12 0.1095 USD 1 20050527 99991231 is it possible to put this data in the following: 20050512 20050526 20050527 99991231 ZWR1 XREZ12 0.1095 0.1095 USD USD 1 1 So basically make the exis...

Two databases or one
Hi, What if you have two organizations and you plan to design a database to do the same task but within that task, the defined tables will vary because of unique qualities related to the individual organization. Would it be best to create separate databases for each organization or keep the two organizations together? Anyone know the guidelines related to this? Thanks for any feedback. Ask yourself this - How often will you need the combined data? -- Build a little, test a little. "AccessKay" wrote: > Hi, > > What if you have two o...

How do I insert the degree symbol in a column of numbers?
I have a column of numbers on the format ddmm.mmm and would like to insert a degree symbol after the dd. I can do it one cell at a time. Can I do the whole column at once somehow? Try this, JKB: http://www.officearticles.com/misc/symbols_and_characters_in_microsoft_office.htm ******************* ~Anne Troy www.OfficeArticles.com www.MyExpertsOnline.com "JKB" <JKB@discussions.microsoft.com> wrote in message news:AF3C8E29-68E2-47E1-8889-69070B1B4D62@microsoft.com... > I have a column of numbers on the format ddmm.mmm and would like to insert a > degree symbol after th...

how to create formula to divide two rows autoaatically
Is there any way to setup a sheet or create a formula so that it will divide the data in column A by Column B anytime the data is entered and put it into columnC ? What I am trying to do is create a spreadsheet for calculating fuel MPG. So I have columns as miles, gallons and the calculation as MPG. What I want to do is anytime a value is entered into miles and gallons, to calculate mpg and put it into that respective cell. Is there any way I can do this ? Thanks -- Tony Tony, in Column C, type the formula =sum(a1/b1). This should give you the result you are looking for. Hop...

2 workbooks
I have got 2 workbooks with a list of names. I need to find out if a name appears in both books. One of the workbooks came from a different source and the other one is a report I ran from our database. I need to find out whether the workbook from out of the company has got any of our own names on it. I am using Microsoft Excel 97 and am fairly new at this so please be gentle. One way I did think was to combine the two workbooks into one and find the duplicates but thought there may be another way. Thank you. Hi "queen on", Assume that In book1 the names are in column A, s...

Data Markers
Can I change data markers so that it reflects the conditional formatting on that cell? For example if the data in column c is being plotted and the conditional formatting I have on the cells are if column D = "Y" can I have it so that those markers are plotted with a circle marker and the rest with a square marker? Thanks! Hi, Have a look at Jon's page for conditional charts. http://peltiertech.com/Excel/Charts/ConditionalChart1.html Cheers Andy ivy_gayle wrote: > Can I change data markers so that it reflects the conditional formatting on > that cell? For exampl...

More visible FIND highlight
When using the Find function in a large database, the information is found but because the Find highlighting is so subdued, you still have to search a whole screen. I've tried find and excel preferences but cannot find any means of making the find highlight or colour stronger so that it stands out in the page. Appreciate suggestions, it's sending me blind. I look at the name box (to the left of the formula bar) to get the address of the activecell. You could hit the Fill icon button to change the color (then undo to put it back)? Ken Newton wrote: > > When using the Fin...

Compare two files and update data from another file base on words in a cell separated by commas
I have two file with several colomns. I need to compare two Col B fileA Col B of FileB as shown in example. http://spreadsheets.google.com/ccc?key=0AgUVfFOnkiaKdFBiNDFLamcybXdhW... Each col have about 1000 rows. Each row contains thousands of words and phrases separated by Comma. As you can see from example, my data has soo many words and phrases separated by comma in each row of two colomn A and B. ============== i Need to merge data of corresponding row from COLA$FileB TO corresponding row of COLA$FileB Also merge data of corresponding row from COLB$FileB TO correspondin...

how to summerise values from specific columns
Hi, I have tried to find the right formulah, but failed. I have three column Amount A Amount B Amount C 30 10 20 34 and a fourth column where I have to fill in % that applies to all values A-C in the row. 12% 45% How to make a formula in a cell that summerise all amount A's * the respective % For example: 30 * 12% + 10 * 45% = Also for two other cells for B and C Bart Vista/excel 2007 try sumproduct() =SUMPRODUCT(A2:A3*D2:D3) HTH Regards Sebation "AA Arens" <bartvandongen@gmail.com> ??????:1189666230....

Filling in Data
I have several points where I need data. I only have the start and the end values and I want to fill in each of the points between the two. How is that accomplished. Example. Point 1 = 50, Point 10 = 600. I need Points 2-9. 550/9 = the increment to add moving from point 2 to point 9 Jamie wrote: > I have several points where I need data. I only have the start and the end > values and I want to fill in each of the points between the two. How is that > accomplished. > > Example. Point 1 = 50, Point 10 = 600. I need Points 2-9. Hi, Try this and ente...

differences between two tables
I'm trying to find the differences between two tables, A and B. There are some records in A that are not in B, there are some in B that are not in A, and there are some in both but with differences in the fields. I'm not getting everything. Does anyone have any ideas how I can tackle this? Thanks! Use a UNION ALL query. -- KARL DEWEY Build a little - Test a little "denise" wrote: > I'm trying to find the differences between two tables, A and B. There are > some records in A that are not in B, there are some in B that are not in A, > and there are s...

two tables with same field names
I create an order status table for my division each week by running queries on corporate's systems. The field names are always the same, and there lies my problem. I need to measure the change in promise dates weekly to the order line level. For example, I need to pull last monday's promise date minus this monday's promise date to get the change in number of days. Post your table structure so a query can be assembled. -- KARL DEWEY Build a little - Test a little "deb" wrote: > I create an order status table for my division each week by running queries > o...

Why does Outlook open two windows?
When installed Outlook 2003 with a new profile about two hours ago, it would open one window each time I started Outlook. But now, after I've been doing a lot of work on the folder structure, particularly transferring dozens of folders by drag and drop within a single non-default PST file. I notice that the computer has been very noisy for a while, apparently furiously coping with the changes I've been making. Now when I start Outlook, it opens two windows; presumably that's an outcome of something I inadvertently did when transferring all the folders. I'd rather it ...

can I make a field data type a choice ("x" or "y") instead yes/no
in Access 2003 I would like to enter a choice in a table data type so I can have a two choice ("x" or "y") ro a multiple choice ("a" or "b" or "c"). How can I do this? You need to create a related table, so you can have as many choices as apply. Take a look at this article: http://allenbrowne.com/casu-23.html It uses a sport example, where a student may be involved in several sports. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rath...

Stacked and Column Chart
Hello, Using the data below I would like to create a single chart with a Column Stacked Chart for Revenues and a Column for Expenditures. Can u help me? Revenues FY03 FY04 FY05 Private/Local Govt. $1,500,928.00 $2,598,394.00 $7,030,199.00 State $821,388.00 $3,467,473.00 $20,413,581.00 Ship Funding $15,806,578.00 $19,233,564.00 $23,902,364.00 Flowthru (Federal) $10,036,098.00 $10,595,406.00 $13,369,788.00 Federal Government $68,276,270.00 $1,604,082.00 $63,199,215.00 Expenditures $132,1452,481.00 $128...

1-way data sync into CRM (v3)
How do I determine if I need to purchase Scribe (and training) or write a customized web application using web service calls? (Any classes on that?) How do I know which would be less expensive now and in the long run? I have no knowledge of Scribe at this time, and I am not familiar with the SDK / C#. Issue: I need a one-way synchronization of data from a SQL database app into CRM for Leads, Opportunities, and Contacts. That data needs to be in CRM only for management reporting purposes and will not be updated by users. This will be an ongoing daily update process. The project man...

need Excel formula that figures out which data adds up to total
I have 33 different dollar amounts listed along with their invoice numbers and I need to figure out how many of those amounts add up to a check that I received. For example, I have 33 different rows of numbers in 1 column that add up to 53,545.69. Which rows of numbers add up to that? I received a check for $11,646.54 but do not know which invoices it pays. Is there an Excel formula that can tell me which ones could add up to that total? Thanks for any help, Kerri Google for SOLVER "Kerri" wrote: > I have 33 different dollar amounts listed along with th...

Referencing two (or more) cell values in formula
Hi, Can anyone tell me the correct syntax for referencing two cells as a criterion in a formula. For example if I want to sum cells in b1:b5 if cells a1:a5 are greater than the value in c2 I would write the following: =sumif(a1:a5,">"&c2,b1:b5) If I want to sum b1:b5 if cells in a1:a5 are greater than the value in c2 and less than c3 what should I write? =sumif(a1:a5,and(">"&c2,"<"&c3),b1:b5) this doesn't work and neither does this.... =sumif(a1:a5,and(>c2,<c3),b1:b5). Any help gratefully received. Thanks, Will willcull@...

I need to compare to columns and indicate the matches in another
am wanting to compare 2 columns for exact matching. If there are two matching items I want to be able to say "A match" in a chosen cell for all the ones that match. Column C will be retrived in an random order... so how would I write the formula for that????? Example: A B C 45time 11tune A match 11tune 89time 47doog 43jkjkj A match 123ABC 123ABC If possible include how to highlight the ones that make as another option. Thanks Try something like this: For a value list in B1:B5 and a ...