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 10. Total comments: 552
Hey, I want to compare 3 columns from one sheet to 3 columns in another sheet, can anybody help?
A B. C
2/3/18. 123
2/3/18. 123
1/3/18. 123
2/3/18. 124
I want a formula in C that return any text like say "ok" if B has multiple entry of same values and in A dates are different.
As in above 123 has 3 entries for these 3 entries column A has two dates 2/3/18 and 1/3/18, so formula should return OK as dates are different
Hi,
Below is a sample of the data I have. I want to match the data in Column A , B and C to data in Column F, G and H. If column A,B,C is not matching column F,G,H, I want to add a row and copy the data from Column A,B,C to F,G,H. For example, "880 0 1" is missing in column F,G,H, so I want to add a space and add "880 0 1" to column F,G,H so it will match column A,B,C. I have a large set of data, so I am trying to find a different way instead of checking for duplicate values in the two columns and manually adding one row at a time. Thanks!
A B C D E F G H
880 0 0 880 0 0
880 0 1 880 0 2
880 0 2 880 0 3
880 0 3 880 0 4
880 0 4
Hello,
Please I have one column and I need to know how to highlight two duplicated value but one (+) and one (-)
Ex.
123
142
-123
-142
Thank you!
Hello,
I have 2 lists
Column A has 1000 items in it
Colum B has 500 items in it
I'd like column C to be just a list of items that are NOT shared in either column A or B e.g. Let's say in the 1000 words in Column A - widget is there but no where in column B - I'd like the new list in column C to have the word widget in it along with all the others that are missing in the second column.
Hi, Please help with a formula with this:
I have 2 columns, one with a code (ex: XCSDADAS) and the other with URL embedded with a code. I need to compare both the columns to see if the code in column a matches with the code in the URL.
Thank you
I have 50separate bills of 50 customers in excel. I like to get each bill in another sheet with a reference at a click.Plz help me out.
Hi,
Thank you for helping in advance.
Please assist me in the following two columns, I need to compare two columns and extract the info like "AWC+ST" "AWC+BR" "RR+ST" "RR+BR" seperately.
Thanks & Regards.
Col1 Col2
RR ST
RR ST
RR ST
AWC ST
AWC ST
AWC ST
AWC ST
AWC ST
AWC ST
RR ST
AWC BR
AWC BR
AWC BR
RR BR
RR BR
AWC BR
AWC BR
AWC BR
AWC BR
AWC BR
RR BR
AWC BR
AWC BR
Hi Balaji,
I am sorry, it is not very clear what the result should look like. If you can send us a small sample workbook with your data and the result you want to get, we'll be able to help you better.
Please shorten your table to 10-20 rows / columns and email it to support@ablebits.com. Please also don't forget to include the link to your comment in the email.
Thank you.
I want to compare column A & B in list-1 with A & B In list-2.
I wont have duplicated data but list -1 it will not be in the the same order as list-2.
and some date may be missing from either list. after we compare A & B we need to compare A & C and so on
the files I want to compare to large file of data (I do this often),
I dont want to create a new helper table on the side its hard to keep organized
Can you just apply this idea directly to conditional formatting to spot differences?
I do not use VBA and they dont like VBA at work.
I have researched this to no avail.
I use Power query and power pivot if there is any help there
no one has a video on this
Thanks for your time
Kim Bilton
Hello. Please can you help me.
I have a to compare 2 sheets in the same workbook. One sheet has a full sheet of customer names (12000 rows), and the second has only a few (700). I need to check that the small sheet customers all exist in the big sheet, but there could be spelling mistakes or slight differences in writing customer names. For example, sheet 1 could have Toyota, and sheet 2 could have Toyota PTY.
How do I check the customer names, without scrolling through a 12000 row document.
Thanks.
I'm having a bit of a struggle. I have to columns. One contains the sales person's name and the other contains what they sold. I want to add the salesman's total to what type of product they sold. For example, sales person JW and the different products are home, pro, iLab. What formula can I use to give me a total number of sales for that particular item.
you saved me 72 hours of work in just a day. thnks
I have 3 excel columns which have values like this,
Col_A Col_B Col_C
----- ----- -----
400 600
500 800
400 300
300 200
700 900
800 700
500 100
I want the values to be copied in Column C from Column B, which are not Mache with Column A values.
I mean just copy the values from Column B, which are not available in Column A.
Like below
Col_C
-----
600
200
100
Is there any excel formula using which I can achieve this?
IF WE WANT TO MATCH TO 2 NO OF DIFFERENT COLUMN WITH AS SAME AS POSSIBLE DATA BUT NOT ACTUAL (EX- WB23B4152 IN (A) COLUMN BUT 4152 IN (B) COLUMN) THEN HOW WE CAN FIND?
Please help me
im in accounts and my bank statement didnt import correctly on to our system, i took the amounts from the bank into column b and the imported amouts in column A, what formula can i use to see what amount didnt import?
Hi (and Happy New Year), I would like to:
1)highlight cells with similar text (i.e: A1:blue and C12:Bluepoint) from two different columns
2)Count if and how many times a similar text in a cell (A1:Blue) appears in another cell of another column ((i.e: A1:blue, C12:Bluepoint and C25:Bluesky.
I have two columns with company names and I want to find duplicates (highlight them, count them), but the company names are not written always in the same way, i.e A1: cocacola ltd and C12: CocaCola company.
Is this possible?
Thanks a lot,Kos
New _Position code
JOB049130401803012001-002
JOB052110609801019002-004
JOB055350101805042003-005
JOB055350101805042003-006
JOB055350101805042003-007
JOB073850101805042003-004
JOB073850101805042003-005
JOB077910609801019002-003
JOB077912002801019002-001
JOB077912002801019002-0010
JOB077912002801019002-002
JOB077912002801019002-003
JOB077912002801019002-004
JOB077912002801019002-005
JOB077912002801019002-006
JOB077912002801019002-007
JOB077912002801019002-008
JOB077912002801019002-009
JOB078310107801019001-005
JOB080650101805222015-005
JOB086112001801039001-005
JOB086112001801039001-009
JOB086112001801039008-006
JOB086112001801039016-005
JOB086112001801039017-006
JOB086112001801039025-005
JOB086112001801039025-006
JOB090612002801152011-001
JOB090612002801152011-002
JOB090612002801152011-003
This is my list where i need to know which no is free to put in the end like -004,-005 etc which i have to put to create unique code. kinldy help me
JOB077912002801019002
Hello,
I'm afraid there's no easy way to solve your task with a formula. Using a VBA macro would be the best option here.
However, since we do not cover the programming area (VBA-related questions), I can advice you to try and look for the solution in VBA sections on mrexcel.com or excelforum.com.
Sorry I can't assist you better.
Hi
I want to know the excel formula to compare column E with A to D and get the mismatch indication at column F. Pl. help. Regards
A B C D E F
1 2 3 4 8
2 3 4 5 10
6 7 8 9 5
Hello,
For me to understand the problem better, please send me a small sample workbook with your source data and the result you expect to get to support@ablebits.com.
Please also don't forget to include the link to this comment into your email.
I'll look into your task and try to help.
May I ask how to pull out the text/string data different from column A (ie A2:A10) and Column B (B2:B10) to cloumn C
An example of raw data set:
A B C
Student Name: Student Name: Missing:
Tom Tom Jerry
Jerry Catherine Sam
Catherine Ada
Ada
Sam
Thank you!
Hello,
Please try the following formula:
=IFERROR(INDEX($A$2:$B$10,SMALL(IF(($B$2<>$A$2:$A$10)*($B$3<>$A$2:$A$10)*($B$4<>$A$2:$A$10)*($B$5<>$A$2:$A$10)*($B$6<>$A$2:$A$10)*($B$7<>$A$2:$A$10)*($B$8<>$A$2:$A$10)*($B$9<>$A$2:$A$10)*($B$10<>$A$2:$A$10),ROW($A$2:$A$10)-1),ROW(A1)),1),"")
Please note that this is an array formula. You should enter this formula into the first cell in column C, hit Ctrl + Shift + Enter to complete it and copy the formula down along the column. Just select the cell where you've entered the formula and drag the fill handle (a small square at the lower right-hand corner of the selected cell) down.
Hope it will help you.
I prefer to use Index/Match function instead of Vlookup. In Vlookup we can lookup values with [A5&"*"] option, which is not possible in the Match Function. Can I get a solution to use Index/Match Function with Match(A5&"*"] ?
WHEN I USE =$B2$C2 AS IN THE EXAMPLE ABOVE THERE IS NO RHYME OR REASON TO THE CELLS THAT ARE HIGHLIGHTED. COLUMNS THAT HAVE THE SAME VALUE ARE HIGHLIGHTED ALONG WITH SOME THAT ARE DIFFERNT LIKEWISE IF I USE = INSTEAD OF GET THE REVERSE OF THE CELL HIGHLIGHTE.
???????????????????????????????
Hello,
For me to understand the problem better, please send me a small sample workbook with your source data and the result you expect to get to support@ablebits.com. Please don't worry if you have confidential information there, we never disclose the data we get from our customers and delete it as soon as the problem is resolved.
Please also don't forget to include the link to this comment into your email.
I'll look into your task and try to help.
IF D2=D3 and A2 not equal to A3 insert the word "Duplicate hotkey" in column E
Sorry!
Hello,
For me to understand the problem better, please send me a small sample workbook with your source data and the result you expect to get to support@ablebits.com. Please don't worry if you have confidential information there, we never disclose the data we get from our customers and delete it as soon as the problem is resolved.
Please also don't forget to include the link to this comment into your email.
I'll look into your task and try to help.
IF D2=D3 and A2A3 insert the word "Duplicate hotkey" in column E
Hi Natalia,
Maybe you could help me with this formula?
I have a long Excel report of 37K lines that I would like to find duplicates in.
These duplicates should have different value in another column. So I thought maybe it will be possible to have a formula that will add a word next to these lines.
The formula needs to be along these lines (to my understanding):
IF D2=D3 and A2A3 insert the word "Duplicate hotkey" in column E
Would you be able to write such formula in Excel language?
Many thanks,
Avi
You just saved me so much time!!! Thank you!!!
I have received payment 100k
But In our books 200k with 300 invoice how i find matching invoices against 100k
i'm trying to track at what time of the day we have the biggest failure rate and highlight that time in the column. i have 5 columns, mon-fri. each column has rows broken into half hour increments. ex: row 4 is 8:30, row 5 is 9:00 etc. in the column in question, the value in row 4 is 10, row 5 is 19, row 6 is 27, row 7 is 31, row 8 is 60, and row 9 is 81. i can tell that the largest difference is between rows 7 & 8. is there a way to highlight row 8 to show that that is where the largest difference is? if so, how can i do it?
Thanks in advance.
A2- U 4 8 WENDLAND STREET PORT LINCOLN SA 5606
A3- U 1 5 SCANLAN STREET EAST BRISBANE QLD 4169
A4- U 16 7 BERYL STREET WESTMEAD NSW 2145
-----------------
B2- 4 8 WENDLAND STREET PORT LINCOLN SA 5606
B3- 8 HEIDELBERG STREET EAST BRISBANE QLD 4169
B4- 16 7 BERYL STREET WESTMEAD NSW 2145
When analyzing the differences in the above two data set. As you can see, A2 is similar to B2 (except for the letter U, which represents a Unit. For the purposes of my analysis, this is correct. As is A4 & B4). But clearly A3 is a different address to B3. How do i put a simple formulae in Except, which will extract the variations from two cells?
Thanks
Abs
How to compare 2 columns to find the match exist or not. I need the formula which compares B1 with A:A. If Match found 1 else 0. I tried =IF(B:B=AA, 1,0) didn't work.
A B
0001165186 0094713197
0001166595 0097094894
0001166619 0094640211
0001190181 0097058341
0001196342 0016685760
0001213475 0016748842
0001216979 0016760276
0001217648 0097012553
0001222292 0097075931
Hello, Sarah,
Please try this formula:
=IF(ISNA(VLOOKUP(B1,$A:$A,1,FALSE)),0,1)
Hello,
For work I am tasked with downloading customer Forecasts and updating our backlog accordingly. it is a very time consuming process. there are multiple parts that are on multiple different purchase orders with quantities and dates varying on a monthly basis. the changes are not that frequent but I have hundreds of rows to go through. I am looking for a way to quickly find changes that have occurred month to month however I need to hold reference to the part number, purchase order, quantity and date. have you any thoughts on how I may be able to do this?
Hello, William,
if I understand your task correctly, we have the add-in that may help you.
Please take a look at the Compare Two Tables tool from the Duplicate Remover add-in. With its help you'll be able to find unique records or remove duplicate values between 2 worksheets.
You can download a fully functional trial version to test the add-in and see if it work for you from this web-page:
https://www.ablebits.com/downloads/index.php
Hope this'll help!
thanks.....
Hi,
I have set of lab values in one column & reference range in another column. I want to highlight the lab values which are "Out of Range" & "Within he Range". Please let me know; how to do that.
Pl provide function in excel
Suppose data I column a and b
Name marks
C 9
D 14
E 20
Ans to be in column c and d
Name marks
E 20
D 14
C 9
Name marks
A 9
C 8
D 20
Result to be
Name marks
D 20
A 9
C 8
Pl provide function in office 2007
Hi,
How to lookup the value if the 2 cells having same value, excel is considering 1st cell but not considering 2nd cell(It should be considered)
Example all the 4 cells having the code of 7555(Attachment below) here some of the row having different values and some of the column having different values
For me it should be added and required in another sheet
Code A B C
7555 395
7555 77 343
7555 158 262
7555 210
Really amazing and simplifying the efforts.
Hi,
What I'm trying to do is highlight a row if there is any text found in cells in column E and column I.
So it would look something like:
IF text in Cell E
AND
Text in Cell I
Colour row red
There will always be text in the E column, so I only need the row to highlight if there is text found in the I column.
How do I go about doing this?
Thanks in advance :)
Hi
I want to compare Data in 2 columns A and B and want to highlight the different words only. Eg . my A1 CELL contain Apple, Sweets, good
B1 cell contains
Apple, sweets, pizza, car
In above two cells in A1 good is extra word which is not present in CELL B1. Where as Cell B1 contains words " Pizza, car "which are not present in excel so how to highlight only these different words
I have lot of Data
Hi, Sandy,
I’m afraid there’s no way to highlight only duplicate words in Excel. All the formulas and our add-ins will select or highlight the entire cell.
I can only advise you to ask around Mr.Excel forum in case they come up with some VBA code for you.
I wish I could assist you better.
Is there a way where we can identify
1. % similarity
2. between 2 columns
3. With strings as data.
Example:
Column A | Column B | Match
Cat is back | Dog is back | 33%
Superman | Spiderman | 0%
Compare to worksheets Sheet2 to Sheet1, find sheet2 match and then return data sheet1 from in 1st cell of this matched column.
2 work sheets
1st sheet contains A=data to return (1234) + B-H data to match 456,789,012
2nd sheet contains criteria to match - look for 456 then return A (1234)
hi, i need a formula to detect the values of the weight in a column and if it is more than the value then to add 7.50 but if it is less then the value to add 4.
so for example if the products weigh less than 0.4 then i need the cell to add 4 but if they weigh more than to add 7.5
hi
i have three unequal column and i want to extract unique cells for first column in compare with two another columns. how can i do it?
Thank you very much. Its working. worth full article!
Salaam everyone.
I have two excel columns each containing 63,000 values. I want to find out all the values where entries of column A are greater than those of column B. How can I do that?
Thanks in advance
Stay blessed.
Hello Fiaz,
You can enter the following formula in the first cell with data, say A1:
=IF(A1>B1, TRUE, "")
Then, copy the formula to the entire column. And after that sort or filter your dataset by the formula column.
I am working on aligning university rankings, as you can see the universities are ranked differently in 2008-2009. Is there a way to use excel to align the universities with corresponding rankings? There are hundreds of them for 12 years, so you can see it is mammoth task.
2008 2008 2009 2009
Sussex 26 cardifff 26
Cardiff 27 Sussex 27
Queen's 28 Reading 28
Reading 29 Sussex 29
Glasgow 30 Surrey 30
Birming 31 Strathclyde 31
Manches 32 Manche 32
I need a formula that will help me identify duplicate parts/product at different pricing. When I use the conditional formatting on both columns all of the cells are highlighted because it's looking at just the columns as duplicates.
Hi, Eva,
I managed to do that in the following way:
1) I decided to use a helper column (C) that checks if the product (A1) is duplicated and its price (B1) is unique with the following formula:
=COUNTIFS(A:A,$A2,B:B,"<>"&B2)
If the condition is true the formula returns 0, since it can't find any duplicated products with unique prices. Otherwise, it returns 1.
2) I created a following conditional formatting rule:
=$C1>=1
and applied it to my table =$A:$C
As a result, I can see all the duplicate products with unique prices highlighted in yellow.
Try to do the same with your data, hope it helps!
Hi,
I need to compare two columns that should be the same, but the data in each comes from different sources.
Both columns are dd/mm/yyyy hh:mm format.
My issue is that in one column, there's an extra " " between the date and time, and when I run my comparison =AND formula, the extra space is causing it to fail.
Can you suggest a way I can easily remove the extra space, or get Excel to ignore the extra space?
Hi
I have tow columns of data. in column2 there is sum of data of column1 how i can compare tow columns and recognize which numbers is sum one number in other column? for example column1 is contains 1,2,5,3,6 and column 2 is 8,9 how i can determine 8 in column2 is sum of which numbers in column 1
thank you
Hi,
I have 3 columns of data and am comparing Column 3 data with Column 1 and 2 for duplicates. I could use the conditional formatting but I was thinking if there is an excel formula that I could use?
Column 1 Column 2 Column 3
15 14 12
12 17 11
11 18 14
10 17 16
20 25 15
Thank you.
I have 2 columns, each column have value. Is it possible to move a value beside its same value? for example:
Column 1 Column 2 OUTPUT
1 1 1
2 3
3 3