Comparing data between two columns

I have a worksheet with two columns that I need to compare data between.  I 
need to see what is different between one column and the next.  There are 
number and letter values in the column and about 180+ rows.  The information 
is scattered throughout the cells so there is no way to filter and check row 
by row.

The columns look like this:

July                           April
MP123456789            MP555555559
MP987654321            MP666 H1258
MP789456123            MP00000 H89
MP987123654            MP123456789

I need something that would tell me that all of the above values are unique 
between the two columns except for MP123456789
0
Utf
5/12/2010 8:45:01 PM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
1380 Views

Similar Articles

[PageSpeed] 30

Hi:

You can try the countif function and if there are none then it is unique

ie if the codes are in columns a and c and then put the formuae in the 
columns b and d. 
A                   B                                 C                      D
MP123456789 =countif(c:c,a1)           MP555555559    =countif(a:a,c1)
MP987654321 =countif(c:c,a2)           MP666 H1258    =countif(a:a,c2)
MP789456123 =countif(c:c,a3)           MP00000 H89    =countif(a:a,c3)
MP987123654 =countif(c:c,a4)           MP123456789   =countif(a:a,c4)

Where there is a number greater than zero means that there is a match.

-- 
Hope this helps
Martin Fishlock, www.nyfconsultants.com, Wolverhampton, UK
Please do not forget to rate this reply.


"ABeezy" wrote:

> I have a worksheet with two columns that I need to compare data between.  I 
> need to see what is different between one column and the next.  There are 
> number and letter values in the column and about 180+ rows.  The information 
> is scattered throughout the cells so there is no way to filter and check row 
> by row.
> 
> The columns look like this:
> 
> July                           April
> MP123456789            MP555555559
> MP987654321            MP666 H1258
> MP789456123            MP00000 H89
> MP987123654            MP123456789
> 
> I need something that would tell me that all of the above values are unique 
> between the two columns except for MP123456789
0
Utf
5/12/2010 9:01:01 PM
You can highlight the unique values by Conditional Formatting as under:

1. Select both the columns,
2. Click on Conditional Formating under Home Tab
3. Select New Rules and select "Format only Unique and duplicate values" 
then select Unique from drop-down box and select formating as you desire.

Hope this will work for you.
rgds...

"ABeezy" wrote:

> I have a worksheet with two columns that I need to compare data between.  I 
> need to see what is different between one column and the next.  There are 
> number and letter values in the column and about 180+ rows.  The information 
> is scattered throughout the cells so there is no way to filter and check row 
> by row.
> 
> The columns look like this:
> 
> July                           April
> MP123456789            MP555555559
> MP987654321            MP666 H1258
> MP789456123            MP00000 H89
> MP987123654            MP123456789
> 
> I need something that would tell me that all of the above values are unique 
> between the two columns except for MP123456789
0
Utf
5/13/2010 6:34:01 AM
Thank you so much!  This made it super easy to find unique values!

"Excel_Learner" wrote:

> You can highlight the unique values by Conditional Formatting as under:
> 
> 1. Select both the columns,
> 2. Click on Conditional Formating under Home Tab
> 3. Select New Rules and select "Format only Unique and duplicate values" 
> then select Unique from drop-down box and select formating as you desire.
> 
> Hope this will work for you.
> rgds...
> 
> "ABeezy" wrote:
> 
> > I have a worksheet with two columns that I need to compare data between.  I 
> > need to see what is different between one column and the next.  There are 
> > number and letter values in the column and about 180+ rows.  The information 
> > is scattered throughout the cells so there is no way to filter and check row 
> > by row.
> > 
> > The columns look like this:
> > 
> > July                           April
> > MP123456789            MP555555559
> > MP987654321            MP666 H1258
> > MP789456123            MP00000 H89
> > MP987123654            MP123456789
> > 
> > I need something that would tell me that all of the above values are unique 
> > between the two columns except for MP123456789
0
Utf
5/13/2010 1:14:01 PM
Thank you!  This is exactly what I was looking for.

"Martin Fishlock" wrote:

> Hi:
> 
> You can try the countif function and if there are none then it is unique
> 
> ie if the codes are in columns a and c and then put the formuae in the 
> columns b and d. 
> A                   B                                 C                      D
> MP123456789 =countif(c:c,a1)           MP555555559    =countif(a:a,c1)
> MP987654321 =countif(c:c,a2)           MP666 H1258    =countif(a:a,c2)
> MP789456123 =countif(c:c,a3)           MP00000 H89    =countif(a:a,c3)
> MP987123654 =countif(c:c,a4)           MP123456789   =countif(a:a,c4)
> 
> Where there is a number greater than zero means that there is a match.
> 
> -- 
> Hope this helps
> Martin Fishlock, www.nyfconsultants.com, Wolverhampton, UK
> Please do not forget to rate this reply.
> 
> 
> "ABeezy" wrote:
> 
> > I have a worksheet with two columns that I need to compare data between.  I 
> > need to see what is different between one column and the next.  There are 
> > number and letter values in the column and about 180+ rows.  The information 
> > is scattered throughout the cells so there is no way to filter and check row 
> > by row.
> > 
> > The columns look like this:
> > 
> > July                           April
> > MP123456789            MP555555559
> > MP987654321            MP666 H1258
> > MP789456123            MP00000 H89
> > MP987123654            MP123456789
> > 
> > I need something that would tell me that all of the above values are unique 
> > between the two columns except for MP123456789
0
Utf
5/13/2010 1:18:01 PM
Reply:

Similar Artilces:

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

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

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

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

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

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

deleting data, keep formulas
I want to delete the data in a worksheet, but keep the formulas so tha I have a worksheet pattern of formulas that I use frequently -- elviejo4 ----------------------------------------------------------------------- elviejo41's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2356 View this thread: http://www.excelforum.com/showthread.php?threadid=39389 Cells.SpecialCells(xlCellTypeConstants, xlNumbers).ClearContents I don't know how large your spreadsheet is, but you could try changin the setting to display Formulas (Tools>Options | View tab, in th Win...

Data Validation Window?
Excel 2000 ... I do not use Validation much, but usually have no problem. I used COPY/PASTE SPECIAL "Validation" to get Validation into other cells ... Issue is ... the Validation Message Window is now opening way off location from the PASTE TO cells. Above said ... if I select each cell & set Validation ... Validation Window appears ajacent to cell where Validation was set (perfect)... However, when I use COPY/PASTE SPECIAL Validation ... the Window is appearing to stay closer to the COPY FROM cell than the PASTE TO cell. How do I fix this ... other than selecting eac...

Counting occurances of same data in different fields
I am creating a conference registration program that allows registrants to select up to three events to participate in. Is there an easy way to count the total number of registations for each event that occurs in the three selection fields? On Mar 16, 2:54 pm, Fred Morris <FredMor...@discussions.microsoft.com>wrote:> I am creating a conference registration program that allows registrants to> select up to three events to participate in. Is there an easy way to count> the total number of registations for each event that occurs in the three> selection fields?I gather you hav...

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 Migration Manager Config fails
I'm trying to configure DMM on XP user Admin rights and system admin in CRM. Install went fine. After putting in the url during configuration, I get an error message " Either the user id or business id is not valid or the user or business is not valid." Has anyone encountered this? Have not been able to get past this point. ...

No Data under Reconciliation
Hi All, I am experiencing the strangest of all problems that I have ever come across.We are using GP 8.0. When I attempt to run Financial reconciliation - when I go to Transactions -Financial -Bank Management -Reconciliation and select the ChequeBook id (any for that) I am able to see the : The statement Balance The CashBook Balance The General Ledger Balance But when I click on the Transactions button there is nothing there-No data absolutely nothing. Any ideas guys? -- Noels "The Best thing in life is life" ...

Graff data series
I have a graff for sales. Data series 1 = Budget Data series 2 = Actual I want the value that is shown with data series 2 to be a % of the value of data series 1. ...

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

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

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

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

DATA HIDING
I WANT TO HIDE DATA IN A SINGLE CELL Format the cell with protection of Hidden, and then protect the worksheet. "REDSHARK" wrote: > I WANT TO HIDE DATA IN A SINGLE CELL ...

Chart does NOT show data entered for the values represented
I continue to find a problem in excel 2007 that was not present in 2003. I will input 2or more columns and rows of data to create a chart, then create the chart; but IF MORE THAN ONE data path is selected to be charted, the other data paths are charted incorrectly. They do not reflect the values in the cells! Obviously then, my charts are wrong and I can't rely on them for analysis. Please respond if this has happened to you and you know how to correct for this. I am very frustrated. -- Judi Hi Judi, Data Path? what is this, are your charts refering to other workbooks? Why ...

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

Excel OLE data in PPT reverting to OLD data after macro updation
Hi, Im cross-posting/multi-posting this in the hope that I could get a solution. I am facing a very strange problem (both in Office 2002 and 2003) with Excel OLE charts in PPT. (Im programming with excel as base) I am using code zilched from Jon P's site in order to copy data from an excel sheet and activate a PPT application (and Slide) and an Excel OLE object within the slide and then updating the data in it and then saving this template with a NEW NAME. Im using this method to update a single slide template tons of time and each time saving it with different (but well-defined) ...

Importing Data
I have two users who have just had email accounts set up and will be accessing mail exclusively through OWA. Both would like to import existing data from their old accounts into OWA. Is this possible and, if it is, can anyone tell me how to do it? Any help would be appreciated. Thanks. "NOBI" <NOBI@discussions.microsoft.com> wrote: >I have two users who have just had email accounts set up and will be >accessing mail exclusively through OWA. Both would like to import existing >data from their old accounts into OWA. Is this possible and, if it is, can >anyone...

Help with organizing data
I was given data in the following format: 00 10 20 30 40 50 0 25 30 41 43 36 38 1 40 56 52 38 35 20 2 25 19 38 44 46 50 3 52 58 48 56 50 48 Is there an easy way to convert it to the following format: 0:00 25 0:10 30 0:20 41 0:30 43 0:40 36 0:50 38 1:00 40 1:10 56, etc. Any suggestions would be apprecated. Assume data is in G1 to M25 go to an open area C1? insert "0" and copy down to C6 in C7 enter =C1+1 copy down til you hav...