I have two columns in Excel that I want to compare and find the differences between them.
- Col A has 50 numbers, i.e. 0511234567
- Col B has 100 numbers in the same format
Super User is a question and answer site for computer enthusiasts and power users. It only takes a minute to sign up.Sign up to join this community
Highlight column A. Click Conditional Formatting > Create New Rule > Use this formula to determine which cells to format > Enter the ff. formula:
Click the Format button and change the Font color to something you like.
Repeat the same for column B, except use this formula and try another font color.
In column C, enter the ff. formula into the first cell and then copy it down.
=if(countif($B:$B, $A1)<>0, "-", "Not in B")
In column D, enter the ff. formula into the first cell and then copy it down.
=if(countif($A:$A, $B1)<>0, "-", "Not in A")
Both of these should help you visualize which items are missing from the other column.
Microsoft has an article detailing how to find duplicates in two columns. It can be changed easily enough to find unique items in each column.
For example if you want Col C to show entries unique to Col A, and Col D to show entries unique to Col B:
A B C D 1 3 =IF(ISERROR(MATCH(A1,$B$1:$B$5,0)),A1,"") =IF(ISERROR(MATCH(B1,$A$1:$A$5,0)),B1,"") 2 5 (fill down) (fill down) 3 8 .. .. 4 2 .. .. 5 0 .. ..
Here's the formula that you are looking for:
Say you want to find those in col. B with no match in col. A. Put in C2:
This will give you 1 (or more) if there's a match, 0 otherwise.
You can also sort both columns individually, then select both, Goto Special, select Row Differences. But that will stop working after the first new item, and you will have to insert a cell then start again.
It depends on the format of your cells and your functional requirements. With a leading "0" they could be formatted as text.
Then you could use IF function to compare cells in Excel:
=IF ( logical_test, value_if_true, value_if_false )
=IF ( A1<>A2, "not equal", "equal" )
If they are formatted as numbers, you could subtract the first column from the other in order to get the difference:
I'm using Excel 2010 and just highlight the two columns that have the two sets of values I'm comparing, and then click the Conditional formatting dropdown on the home page of Excel, choose the Highlight Cells rules, and then differences. It then prompts to highlight either differences or similarities and asks what colour highlight you want to use...
The comparing can be done with Excel VBA code. The compare process can be made with the Excel VBA
Two columns on different worksheets were compared in this template. It found different results as an entire row was copied to the second worksheet.
Dim stk, msb As Worksheet Set stk = Sheets("Page1") Set msb = Sheets("Page2") Application.ScreenUpdating = False sat = (msb.Range("A" & Rows.Count).End(xlUp).Row) + 1 For i = 2 To stk.Range("A" & Rows.Count).End(xlUp).Row If WorksheetFunction.CountIf(msb.Range("A2:A" & msb.Range("A" & Rows.Count).End(xlUp).Row), stk.Cells(i, "A")) = 0 Then msb.Range("a" & sat).EntireRow.Value = stk.Range("a" & i).EntireRow.Value msb.Range("a" & sat).Interior.ColorIndex = 22 sat = sat + 1 End If Next ...
The tutorial's video: https://www.youtube.com/watch?v=Vt4_hEPsKt8