The tutorial demonstrates a number of techniques to compare and match two or more columns in Excel with formulas, conditional formatting and the specialized wizard. Continue reading
Comments on: How to compare two columns in Excel for matches and differences
by Svetlana Cheusheva, updated on
Comments page 16. Total comments: 551
Thanx for such an informative article over excel,still struggling to find the solution ,Pl help if possible.
I've two columns in which one column say B having exhaustive values,while in another say column A ,some values are missing.How to ascertain either through Vlookup,Countif or If,which values are missing. Here,it is to be noted that all data in A is in column B,but vice versa is not true. Just wanted to know missing data. Pl help in getting formula for the same.Total entries in column A is less than column B(vertically) Plzz..
Hello Avinash,
If my understanding of the task is correct, you need to identify the values that are present in column B, but not in column A. If so, you can use the following formula:
=IF(COUNTIF($A:$A, $B2)=0, "missing", "")
Where B2 is the topmost cell with data, ignoring column headers.
When you copy the formula down the column, the word "missing" will appear in the rows containing data in column B that do not appear anywhere in column A. To get a list of missing values, apply Excel's auto-filter, filter the formula column by "missing" and copy the filtered values in column B.
Afternoon,
I want to put a conditional format in column B.
I have a target number in column A and input Actual in B.
I want a conditional format in B
if the number is same as or above target in A then "fill" the cell otherwise no fill.
610 0 - no fill
1525 915 - no fill
610 0 - no fill
610 710 - fill
610 610 - fill
305 0 - no fill
Sorry,
I posted before completing the question
I want to compare likewise
A B C
Danta,Alex,John,Ryan Alex,Ryan,Danta,John True
John,Alex,Ryan Alex,John,Danta False
Michelle,Rob,Sean Michelle,Rob,Sean True
Hello,
I want to know can we compare this type cells
Cell A
Hi,
Let say that column A has 2 cells and are as follow:
A1 : 10
A2 : 20
And in column C there are 2 cells as follow:
C1 : 10,20
C2 : 20,10
In cell B1 I want that it searches in A1 cell that is 10 and if the first number in column C is 10(that is C1 and not C2), it copies cell C1 in B1.
Hi i currently using index and match function in excel, to auto match and index the value back, if i put a set of new data to compare with my original data.
But however sometime a new data had a multiple result , how to i auto insert a row on the original data columns for the multiple result?
this is my formula code : =INDEX($L:$L,MATCH($A:$A,$K:$K,0)) it could match and bring back the index value back i wanted ... but those with multiple result it only return back to the first result it show.
regards istillanoob
Hello.. I now actually trying using Index match/IF function to try to index the value from “ColE” onto “ColB” , by matching “ColA” compare with “ColD”.
For my eg.
Shown below “Urr” got 3 different value in “ColE” how to I match and insert the three “Urr” with the different number
result in “ColE” onto “ColA” with the same exact result number should appear in “ColB”???
And “colA” “Usw” data still match with “ColD” “Usw”even is not in the same row?
something like new data replace with original data orderly, plus without deleting original data if the data don not appear in new data.
Eg.
ColA
Doman
Urr
Usw
ColD ColE
Doman 1234
Urr 12345
Urr 12345
Urr 12346
if column A has names and column D has dates. Is there a way to highlight equal dates that belong to the same name?
Hello Jonathon,
You can create a conditional formatting rule with the following formula:
=COUNTIFS(A:A, A1, D:D, D1)>1
Somebody to help me....
Dear all,
I have a huge problem...
I have two rows A and B,
Values in column A (3,4,4,5,5,6) in column B (3,3,4,5,6,6), I wont to compare this two column
3 with 3 nothing
4 with 4 nothing
4 with x 4
5 with 5 nothing
5 with x 5
nad reverse (if you dont have in column A, have in column B) also to be in column C.
If you understan (match just once number by number), if aleready match to give me value.
Tnks a lot, to all.
Data validation list A =a1:a10
Data validation list b =b1:b10
link cell D1
my problem is if selected list from List A or List B cell D1 change automatically any formula or any solution
Hey sir if I'm having 5 cells in 2 columns say column A and Column B.
with entries like.
Column A Column B
1. B21 Rojer
2. B11 Rojer
3. A11 Adam
4. A11 Adam
5. C22 Chris
6. C22 Chris
How can I select the row 1 and row 2 which are having different code for same name Rojer
USE THE FN EXACT.. FOR MATCHING TWO CLM
Hi
i need to compare one column to all the rows present in Second column.can someone answer?
I have a product if statement but it’s not working properly
see below
=IF(SUMPRODUCT(($A$2:$A2=A2)*($B$2:$B2=B2)*($C$2:$C2=C2))>1,0,1)
….It’s not providing 0’s where there is repeat data across the columns based on the rows above. If it were working it would place a 0 rather than 1.
Thank you Everyone!
That output looked funky so I wanted to clarify:
Col. A = #
Col. B = Name
Col. C = TYPE
Col.D = Nails
with 1 and 0 in data which shows a 1 as a unique listing throughout the rows above and columns A – C.
Col. E = Comedy with the same statement as above with 1’s and 0’s
Col. F = Mane ….
Col. G = Paw ….
# Name TYPE
abcd Abbott Nails
abcde Cost Comedy
baco Lion Mane
zhty Bear Paw
zhty Bear Paw
The above is a simplified version of my data however there are numerous rows and columns but overall I would like to identify unique values across columns and rows simultaneously by documenting it with a 1 as unique or a 0 as it occurred in previous rows across columns.
# Name TYPE Nails Comedy Mane Paw
abcd Abbott Nails 1 0 0 0
abcde Cost Comedy 0 1 0 0
abcde Cost Comedy 0 0 0 0
baco Lion Mane 0 1 0 0
baco Lion Mane 0 0 0 0
zhty Bear Paw 0 0 0 1
zhty Lion Mane 0 0 1 1
Thanks for tutorial. The comparing can be done with Excel Vba Codes .The compare process can be made with Excel Vba Worksheet.Countif Function .
Two columns in different worksheets were compared in this template .Found different results as entire row were copied to second worksheet .
Codes :
"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
...
"
Template at :http://goo.gl/ZeOwa2
Hi,
your description is actually very clear and helpful,thanks a lot. But with my data set it didn´t work for some reason. I want to compare two columns with gene names to find genes which are in column A and in column B, so i tried your example "Example 3. Highlight matches (duplicates) between 2 columns" respectively "Example 2. Highlight unique entries in each list", but unfortunately no cells were marked although there are duplicates. Do you have any idea why this happened?
Thanks a lot!!
I've been using a column compare macro which gives me the data (which is there in column A but not in column b) into column C AND the data (which is there column B but not in column A) into column D. I'm trying to find out the formula used to make this macro.
Thanks!
kindly help me to assign the remarks of attendence as excellent, good or bad based on the student attendence. It will be mentioned in range as 95-99 as Excellent,90-94 as good, 85-89 as average and like.
kindly reply
Hi Manoj,
You need nested IF functions for this. For example:
=IF(A2>=95, "Excellent", IF(A2>=90, "Good", IF(A2>=85, "Average", "Poor ")))
I have a spreadsheet that needs to be alpha dominant where column A is a lower alpha character and column B wants to be a higher alpha character relative to column A.
An example of this is.
Column A Column B
ABC DEF
However, if the columns A and B values are flipped where it is not the correct output such as will be incorrect.
Column A Column B
DEF ABC
I need a formula to weed out the lower alpha to higher alpha issue on a spreadsheet with thousands of rows.
Thanks
I read this article and its great but I haven't found solution to my problem.
I need to compare every to cells in a column that belongs to same object in 2 rows.
I mean for each object (out of many objects) I have 2 rows and multiple columns and for every each of this columns I need to compare every 2 cells (that are one row after another) to see if the data is the same, if not then I need to highlight the 2 cells.
I hope someone can understand me and answer me. I need some condition or maybe to write a command in Macro.
I have large amount of data,
Example
COL-A " PIPE SEAMLESS API 5L Gr. B PSL2 NACE+HIC 3/4"(20 MM) SCH 80 PLAIN END ASME B36.10M "
COL B- " PIPE SEAMLESS API 5L GR B PSL2 NACE + HIC 0.75 SCH 80 PLAIN END ASME B36.10M "
I want to find the difference in content of cell,
e.g in example difference between COL-A & COL-B is {3/4"(20 MM) & 0.75}
Can anyone help?
Hi,
I have 1 spreadsheet with 4 separete text tables. Each table contains a code on the first column and a description on the second.
How can I highlight the mtaching letters sequence or identical words on the second column (description) of two considered tables?
I have 2 excel worksheets. 1st sheet contain 900 names with details in 9 columns.
2nd sheet is with 600 names (which are available in above 900) but with different details in 6 columns.
I want to merge the details of 2nd list of 600 with those details of 600 available in the 900 name list.
How to do this.
Hi I have beeen stuck in a problem I have data with mulipt column with simlier values but different lenght =INDEX($B$2:$B$6,MATCH($D2,$A$2:$A$6,0))
but problem is i have four columns withe common values in one two or three oe all four like your banan expmale so i want to extract the rspective values for common values in multiple columns how it will work tell me please or some other method
Its very good site, which is useful and saved my one week manual effort.
Regards,
Satish
I've been searching for a solution to this, but have so far haven't found an answer.
In a soccer league consisting of several matches there are a number of two differing outcomes to a specific criteria.
Here's a summary:
League and Result
ASIA: East Asian Championship Won
ASIA: East Asian Championship Lost
ASIA: East Asian Championship Women Lost
ASIA: East Asian Championship Women Won
AUSTRALIA: Brisbane Premier League Won
AUSTRALIA: Brisbane Premier League Lost
AUSTRALIA: Brisbane Premier League Won
AUSTRALIA: Brisbane Premier League Lost
AUSTRALIA: Brisbane Premier League Lost
AUSTRALIA: Brisbane Premier League Won
AUSTRALIA: Capital Territory Won
AUSTRALIA: Capital Territory Won
AUSTRALIA: Capital Territory Won
AUSTRALIA: Capital Territory Won
AUSTRALIA: Northern NSW Lost
AUSTRALIA: Northern NSW Won
AUSTRALIA: Northern NSW Lost
AUSTRALIA: Northern NSW Won
AUSTRALIA: Northern NSW Lost
AUSTRALIA: Northern NSW Won
AUSTRALIA: Northern NSW Lost
AUSTRALIA: Northern NSW Lost
What I want to do is create a report that lists each league and counts the number of Wins and Losses in each:
League Criteria
ASIA: East Asian Championship Won 1
ASIA: East Asian Championship Lost 1
ASIA: East Asian Championship Women Lost 1
ASIA: East Asian Championship Women Won 1
AUSTRALIA: Brisbane Premier League Won 3
AUSTRALIA: Brisbane Premier League Lost 3
AUSTRALIA: Capital Territory Won 4
AUSTRALIA: Northern NSW Lost 5
AUSTRALIA: Northern NSW Won 3
I've looked at Pivots, and that goes some of the way there. But I can't figure out how to count the number of wins and losses in each of the leagues.
Many thanks :)
row to row cheque the same the
numbar
hi....
I need a help to compare two sheets but it should compare the entire row only if there first column matches.
for example:
Sheet1
Row ID Order ID
32298 CA-2012-124891
26341 IN-2013-77878
25330 IN-2013-71249
13524 ES-2013-1579342
Sheet2
Row ID Order ID
32298 CA-2012-124891
26341 IN-2013-77878
25330 IN-2013-71249
here suppose row number 2,4,6 are blank or u can say contains other data. But need to compare row only when rowID matches...
32298==32298 but they are in different row numbers
Sorry, that earlier post was accident :)
Hi!
How can I make Excel sheet which compares Game result to my quess? And then I get points depends how good was my quess.
Here is small Example
Result My quess Points
0-1 0-2 4 winner right and one value right
1-1 1-1 10 Both values right = 10 points
1-1 2-1 0,5 One value right
1-2 0-3 3 Winner right, both values wrong
0-0 1-2 0 Both values wrong
Thanks very informative content
Hi can someone help me how i can compare two sheets columns and that compared cells has to be seen in other sheet. i consider it will very great i mean it may more visible
Hi, I need to show in a cell the number of rows that have value in any of the cells in it and give me the column titles.
example CFGA
3
because: there were three rows in the celection that had value only in columns CF and GA out of celected 4 columns.
Will greatly appreciate it.
Hi.. I would like to compare 2 different spread sheet data in excel. each spread sheet contains 15 columns and 1000 records. I would like to write a formula to know whether the spread sheet one data is matching with all the columns of spread sheet two or is there any change in each column. please let me know.
I would like to compare two different spreadsheets. The first spreadsheet has several names that are the same but with different criteria. Ex:
First column will have:
John Smith
John Smith
John Smith
John Smith
Peter Tom
Peter Tom
Peter Tom
Charlie Jones
Charlie Jones
Second column next to John Smith will have
Apples
Oranges
Pears
Bananas
On another worksheet I have John Smith then next to it Pears. What I want to do is to see if John Smith that has Pears is on the first worksheet.
Can anyone assist me?
how to get the sum for the folowing values in excel with out draging the mouse
ColumnA ColumnB ColumnC
1234 05:00
1234 03:30
1234 05:00
1234 03:30
1234 05:00
1234 03:30 =Totalhrs?
5678 05:00
5678 03:30
5678 05:00
5678 03:30
5678 05:00
5678 03:30 =Totalhrs ?
I want total hrs of 1234 and 5678 in columnC with some formulas urgent please
how to get the sum for the folowing values in excel with out draging the mouse
ColumnA ColumnB
1234 05:00
1234 03:30
1234 05:00
1234 03:30
1234
how to get the sum for the folowing values in excel with out draging the mouse
ColumnA ColumnB
1234
1234
1234
1234
1234
I'm trying to find how to write a formula for this scenario (pseudocode as follows): If Status="A" and dueDate contains a value, OR if Status = "C" (No dueDate info needed in this part), then it's True. Is there a way to have a formula order similar to If(OR((Status="C"),AND(Status="A",dueDate""),True, False))), or am I completely off track? Thank you in advance!
Hai
i have a big doubt to find the duplicate values and match the duplicate data's actual values
Hi ,
I wanted to find similar occurrences of string between two columns, If Column 1 text=Marks , then i need to find how many 'marks' in column 1 have 'pass' or 'fail' in column 2.
Thanks a TON!!!!!!
Thank you very much, these notes are really good and helpful for analysis.
Best,
Sameer
I need to sort column A where there is a duplicate number in red text. All numbers that don't have a duplicate red text number need to be removed.
Hello,
I am struggling with how to compare values, and if there is a match, insert a new column. For example, sheet 1 has:
Column A has server names, and will contain duplicates (column B contains application name, multiple app's can run on the same server)
Another spreadsheet (sheet 2) has unique server names by Datacenter (1 Excel sheet for each datacenter). I planned on copy/paste it into column D on sheet 1. If there is a match between column A and D, add a value (the value would be the same each time, as I am pasting only datacenter 1, then datacenter2, etc.) for the Datacenter into column C.
Hope that made sense, and thank you in advance!
I think I found a useable way to get this done, but not ideal. I'll compare the values, if it matches, write MATCH in column C. Then just replace the word MATCH with the Datacenter I am running it for. After a few runs, I'll be done, since I will paste one DC at a time. :-)
Hi,
can you please tell me how to take the difference of dates in cases like:
STARTTIME
24-MAR-16 09.25.20.859619000 AM
ENDTIME
24-MAR-16 09.25.23.028557000 AM
Hi!
I would like to check if the ask how to check two columns (one reference, one data) to another two columns even if they are not same in order. For example:
1) WORK 1 2) WORK 2
REFERENCE DATA REFERENCE DATA
001 RED 005 YELLOW
002 BLUE 001 RED
005 YELLOW 002 PINK
** REFERENCE 001 AND 005'S DATA ARE MATCH WITH WORK 2 DETAILS.
BUT FOR REFRENCE 002, DATAS ARE NOT MATCH WITH WORK 1 AND WORK 2
Even if they are not in the same order, by matching the reference and the data, I need to have a formula to know what details are not match for Work 1 to Work 2.
Please help me with this.
Thank you!
I wann compare this two colum in One Colum
Hi I want to compare just first 10 digits of the two cells.What formula can I use.
Did you find an answer for this Deeba? I need it too