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. 

0
Striker3070
5/10/2010 2:08:54 PM
excel.programming 6508 articles. 2 followers. Follow

2 Replies
1061 Views

Similar Articles

[PageSpeed] 16

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 A","In A")
Sort on column C and/or Column D
-- 
Hope this helps.  
If it does, please click the Yes button.
Thanks in advance for your feedback.
Gary Brown



"Striker3070" wrote:

> 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. 
> 
0
Utf
5/10/2010 2:32:01 PM
this is great, but I prefer to do it in code so I can send this out and 
nobody can change the formulas and mess it up.


"Gary Brown" <junk_at_kinneson_dot_com> wrote in message 
news:31F1ED1A-8A9F-43A7-956D-44CAA0A75063@microsoft.com...
> 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 A","In A")
> Sort on column C and/or Column D
> -- 
> Hope this helps.
> If it does, please click the Yes button.
> Thanks in advance for your feedback.
> Gary Brown
>
>
>
> "Striker3070" wrote:
>
>> 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.
>> 
0
Striker3070
5/10/2010 3:00:29 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...

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...

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 ...

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...

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 ...

Advice on comparing data sets
Hi, Can anyone advise on how I can compare data on 1 sheet with dat compared on another. Example attached. I want to be able to show that whenever the UK is shown (can appea multiple times) on this sheet it checks on sheet2 and enter th corresponding band value in this case for the UK (show in cells B6, B8 will show band A SHEET1 Ref Country Band 1 UK 2 Germany 3 UK 4 France 5 Italy SHEET2 Country Band UK A GERMANY B ITALY B FRANCE D SWEDEN D DENMARK E As always thanks for your help. Simo +------------------------------------------------------------------- |Filename: ...

Compare and delete row
I need some help - I need to compare a cell, let's say A1, that wil contain a date. I need Excel to compare it to today's date, if it i over 60 days, I need it to go ahead and delete that row. If not, I nee it to leave alone. Thanks in advanc -- Message posted from http://www.ExcelForum.com Try this Sub test() If IsDate(Range("a1")) = True Then If Range("a1").Value > Date + 60 Then Range("a1").EntireRow.Delete End If End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "intheway >" <<intheway.176s7x@excelforum-nospam.c...

compare side by side
Using WindowsXP-Media Edition with Service Pack 2 and Office 2003. When I choose the "compare side by side" option, my two workbooks appear one above the other rather than side by side. Am I missing something?? >>Am I missing something?? I commented on that to MS during the beta test. They ignored me! -- Jim "BK" <nospam@nospam.com> wrote in message news:%233jhCdasHHA.1216@TK2MSFTNGP04.phx.gbl... | Using WindowsXP-Media Edition with Service Pack 2 and Office 2003. | | When I choose the "compare side by side" option, my two workbooks appear ...

comparing cell
i have a spreadsheet that contain a column of name (some the same and some not) and would like to place each name in a row acroos the top of the same spreadsheet. Smith Jones Brown Smith Smith Brown Smith Jones Jones Smith Brown Hi Stanley First insert a heading above your column of names. Then Data>Filter>Advanced Filter mark the source as your column of names. Choose copy to another location and choose a detination cell on the sheet. Select Unique values only. Having got a unique list of names, copy this list and Paste Special>Transpo...

Comparing Excel Spreadsheets
Ok, got one I can't handle.. I have one of two scenarios. Either I have 2 Excel workbooks, or 1 workbook with 2 worksheets.. Either way, I have 2 one Column data sets. I need a way to look at the data sets and find what is missing Example Data 1 = 1 2 3 4 5 Data 2 = 1 3 5 6 Results = 2 4 6 Does anyone know how to do this? Thanks Joe ...

compare time
I would like to compare a calculated time in my work sheet vs. the actual system time. For example, if the caculated time is >than or = to the system time I want excel to tell me to "CALL", if not then "REST"...any help would be appreciated. Thanks, DE Put your calculated time in a cell (say A1) In B1 put =NOW() in C1 enter =IF(A1>=B1,"CALL","REST) -- Gary''s Student "Eddie Munster" wrote: > I would like to compare a calculated time in my work sheet vs. the actual > system time. For example, if the caculated time is ...

Column comparing
I am trying to compare 2 columns of numbers so that I can identify and delete numbers no longer required. Can anyone help me find a formula for this please? Many thanks DT Check your earlier post. Dave T wrote: > > I am trying to compare 2 columns of numbers so that I can identify and > delete numbers no longer required. Can anyone help me find a formula for > this please? > > Many thanks > DT -- Dave Peterson ...

Comparing spreadsheets
I am to compare spreadsheet A to spreadsheet B to find out what B is missing from A. Any suggestions to function I could use to do this? How about trying Myrna Larson's and Bill Manville's Compare (an add-In utility): http://www.cpearson.com/excel/download.htm Gabe <rgdelaluz@hotmail.com> wrote in message news:052101c357ca$b07231b0$a501280a@phx.gbl... > I am to compare spreadsheet A to spreadsheet B to find out > what B is missing from A. > > Any suggestions to function I could use to do this? Thanks... But Ms. Larson already wrote to me indicating that ...

Compare this!
Right click in Dock the entourage icon, and the apple mail icon! Why can't MS prvovide such a simple thing?! Haren Sanghavi <intellibuzz@mtnl.net.in> wrote: > Right click in Dock the entourage icon, and the apple mail icon! > > Why can't MS prvovide such a simple thing?! Tell them!! Use the Send Feedback command in the Help menu in Entourage. Corentin -- --- Mac:MS MVP http://www.cortig.net/wordpress/ --- http://www.mvps.org - http://mvp.support.microsoft.com MVPs are not MS employees - Les MVP ne travaillent pas pour MS ...

comparing cells
I need to compare two cells and color them if different, but not compare spaces. I've tried the following: Conditional formating: =B10<>C10 // But it compares spaces Formula: =IF(C10<>B10,"","Different") // But it will compare spaces and I don't know how to make it color fill C10 & B10 =exact(C10,B10) // But it will compare spaces and I don't know how to make it color fill C10 & B10 when false I'm afraid I am way over my head in this. Is there a way to move the contents of a cell to a buffer, remove spaces, make it all the same ca...

Compare dates to copy data
Help please... How would I get the following to occur. (Sheet 1 cell A1 = 2/22/10 when I run macro, look for 2/22/10 on sheet 2, and copy an area from sheet 1 to the appropriate area of sheet 2. So each time sheet one date changes, it copys the same area of sheet one into the correct area of sheet 2 that matches the date. -- Thank you for your time! John If it was me, I would do the following: 1. Turn on macro recorder. 2. Go to Sheet2, filter the data for the date you want. 3. Copy the data to its destination. 4. Turn off macro recorder. 5. Modify the macro the pickup the ...

Comparing data before updating
Does anyone know how to compare data? If same update the quantity else just ignore it? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200706/1 On Fri, 08 Jun 2007 05:06:15 GMT, "EMILYTAN via AccessMonster.com" <u33296@uwe> wrote: >Does anyone know how to compare data? >If same update the quantity else just ignore it? Emily, don't you have that backwards? If it's the same, then updating it will change 123 to 123. Please explain WHAT you are comparing to WHAT, and under what circumstances; and what you ...

Comparing objects
Hello, I don't know if it's the right process, but I would output any names which are not found in a text file, but was found in Active Directory. The text file is using this format: samaccountname:givenname:name user123:USER:USERR I'm using this commands: compare-object -includeEqual (get-aduser -filter * | ft samaccountname) (import-csv -path c:\comptes.txt -delim ":" | ft samaccountname) -prop samaccountname I don't understand what is returned. Is anybody know if I follow the right way? Thanks Why no output of what you don't un...

Comparing tables with specfic parms
I have two tables I want to compare. A third table that lists the parameters for the other two tables. Can anyone please help? Thanks. ...

Compare text
I have two huge paragraphs. I want to check if both of them are same. Can macros help me in easing this process? If I have left out any word in the para it shd should indicate which word I have left out. Is that possible? thanks for the help The following macro should work. Enter the numbers of the first and second paragraphs to check in the dialog boxes. If the paragraphs are the same the macro will report that. If not it will highlight the first non-matching character in the second chosen paragraph. Sub CompareParagraphs() Dim FirstPara As Range Dim OtherPara As Range Dim...

Compare side by side
Compare side by side is grayed out. This doesn't happen for all documents. At this point the only document I saw this happen with was password protected. Would that have anything to do with it? Thanks. Compare side by side could be grayed out for several reasons. One is that there is only one window open<g>. Another is with workbook protection if the protect windows option is selected. With this selected it's not possible to move the window at all. Since the Excel 2003 compare side by side feature moves the open windows it doesn't work with windows protected. -- ...

COMPARING DATES
I have a workbook with two sheets. One of them has some data and the other has a ODBC connection to an Oracle database. I use the following array formula to get some values = INDEX('Cementaciónes Ya Cargadas'!$D$2:$D$76,MATCH(1,('Cementaciónes Ya Cargadas'!$A$2:$A$76='Cementación Secundaria'!$B8)*('Cementaciónes Ya Cargadas'!$B$2:$B$76='Cementación Secundaria'!$E8)*('Cementaciónes Ya Cargadas'!$C$2:$C$76='Cementación Secundaria'!$I8),0)) The problem resides in that one of the values to compare, s...

Comparing and moving unsorted data in Excel
Hi I have two columns of data - the first column contains a list of part numbers (Eg: AB100, AB101, AB101/2) and the second contains a filepath to an image for each part number (Eg: D:\Images\ab-100.jpg). Some products use the same image, but different part numbers (An example would be a screwdriver which comes in different lengths SDD8, SDD10 - two different lengths but the same style, hence only one image). Hopefully this isn't coming across as patronising, I'm just trying to make everything clear! :-) What I am trying to do, is for every part number see if it has a corres...

Sort and compare
After sorting a column of strings, the compare commands don't seem to work for a few characters. In particular > and > don't seem to to value characters the same as the sort command. In particular | and a few others. These are characters that are produced using doss or xp dos commands. hope there's a way around this. John ...

Compare sheet utility that can compare formula too
I am using MS Excel 2003 runs in vista. I need a utility program that can compare two worksheet, not only just the value but the formula as well. For instance: File 1: cell a = 5, b = 5, c = 5 , d = a + b = 10 File 2: cell a = 5, b = 5, c = 5 , d = a + c = 10 (note that d having same value as file 1 but formula is different) Will it be more reliable to use microsoft office product rather than 3rd party program? Any free MS product available for download? Regards, Adam, It is easy enough to do it quickly by hand. Copy the workbooks to preserve the originals, th...