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 11. Total comments: 552
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
=IF(COUNTIF($A:$A, $B2 )=0, "REQUIRED", "NOT REQUIRED")
I want this formula to be stopped when $Bx is null...
wow!!!!!!!!!!!!great...thanks...really useful....
Hello,
I am wanting to compare two columns of data and if those columns are equal, I'd like to execute a calculation.
I am using the formula:
=IF('Raw Data'!A:A=Analysis!A:A,SUMPRODUCT('Raw Data'!C:C,'Raw Data'!I:I)/SUM('Raw Data'!I:I))
This returns FALSE. How do I fix it?
thanks, this page information help me a lot
Hi,
Context - i have 2 versions of a document. In it i have 2 tables with alphanumeric values which i want to compare to see if any changes have been made in the recent version.
Requirement - i want to compare the alphanumeric values of the two columns (one column from each table as mentioned above) tables and want to highlight only the part that has been changed instead of the whole cell and want the result in a third column.
Question - is there a resolution to the above scenario?
Product Id Current profit Margin Last yr profit margin
123 51% 49%
126 53% 50%
129 55% 51%
132 57% 52%
135 59% 53%
Mark red color for the cells where last year profit is greater than current year profit and color pink for the product id which is red cells
have to find using conditional formatting
I have two column that,the value to be matched with the another one another`s criteria column. First column contains exact number and second column contains number with description.
Suggest me further..
Hello,
Very useful, but I need help comparing two columns. I want it to be able to tell that google inc is the same company as Google ABC, or that Brown university is the same as University, Brown. It would be helfpul if the matches are highlighted. Thanks!
EX:
Column 1 Column 2
google inc. Capital one
Brown University University, Brown
Visa Google ABC
If in one column there are name entries with negative and partly failing values associated with them and in another column there are name entries with positive yet yet highly conservative values associated with them, how do I sort the dump the negatives, eliminate the failing and merge into a single positive highly conservative list so that I can quickly get to the golf club?
I have a sheet in which I am trying to find out that a particluar cell value will happen on which date. I have a column where there are equal installments of 2 Lakhs and there is column beside it containing dates starts from 01.04.2017 and ends on 01.01.2024 for those installments. Now, if for the first installment customer pays only 50,000 till 01.10.2017 then how can I find using the formula that from which date the overdue is coming and how much?
Hi,
Below is a sample of the data I have. I want to match the data in Column A and B. If column B is not matching column A, I want to add a row and copy the data from Column A to B. For example, "4" is missing in column B, so I want to add a space and add "4" to column B so it will match column A. 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
3 3 Y B
4 5 G B
5 6 B G
6 8 P G
7 9 Y P
8 11 G Y
9 12 B Y
10
11
12
11
12
How to Salve This Prablem
KS IN_Kirana Stores KSN Kirana Store New
KS IN_Kirana Stores KSN Kirana Store New
KS IN_Kirana Stores KSN Kirana Store New
PS IN_Pan Shop PSA Paan Store
PS IN_Pan Shop PSA Paan Store
KS IN_Kirana Stores CSA Kirana Store New
CS IN_Kirana Stores KSN Kirana Store New
KS IN_Kirana Stores KSN Kirana Store New
KS IN_Kirana Stores KSN Kirana Store New
KS IN_Kirana Stores KSN Kirana Store New
KS IN_Kirana Stores KSN Kirana Store New
PS IN_Pan Shop KSN Paan Store
CS IN_Cosmetic Store CSA Cosmetic Store
Hi Meli,
As i can see it's time sheet of employee. I understand your query. But without sample sheet i am unable to put formula here. so share any short sample sheet fot add formula.
In Time Out Time In Time Out Time
11:00:00 AM 8:30:00 PM 10:50:00 AM 8:40:00 PM = PRESENT
11:00:00 AM 8:30:00 PM - - = Single Punch (This should be absent) Absent
11:00:00 AM 8:30:00 PM 11:30:00 AM 8:35:00 PM = LATE
11:00:00 AM 8:30:00 PM 11:00:00 AM 7:00:00 PM = EARLY
11:00:00 AM 8:30:00 PM 11:30:00 AM 5:35:00 PM = LATE/EARLY
11:00:00 AM 8:30:00 PM 8:45:00 PM - = Single Punch Absent (This should be SP)
11:00:00 AM 8:30:00 PM - - = Single Punch Absent
Hi, I need help with a formula.
The Formula for blank cell was very helpful Thanks.
I have two formula with is correct answer, i combined the formula but there is error.
Formula 1 -
IF(OR(C3="",D3=""),"Single Punch",IF(AND(C3>A3,D3>=B3),"LATE",IF(AND(C3>A3,D3<B3),"LATE/EARLY",IF(AND(C3<=A3,D3<B3),"EARLY","PRESENT"))))
Formula 2 -
IF(AND(IF(AND(NOT(ISBLANK(C3)),NOT(ISBLANK(D3))), C3=D3, TRUE), IF(AND(NOT(ISBLANK(C3)),NOT(ISBLANK(D3))), C3=D3, TRUE), IF(AND(NOT(ISBLANK(D3)),NOT(ISBLANK(C3))), D3=C3, TRUE)), "Absent", "-")
Would you Please help me find solution.
Actual Time Log Time Remarks
In Time Out Time In Time Out Time
11:00:00 AM 8:30:00 PM 10:50:00 AM 8:40:00 PM PRESENT -
11:00:00 AM 8:30:00 PM Single Punch (This should be absent) Absent
11:00:00 AM 8:30:00 PM 11:30:00 AM 8:35:00 PM LATE -
11:00:00 AM 8:30:00 PM 11:00:00 AM 7:00:00 PM EARLY -
11:00:00 AM 8:30:00 PM 11:30:00 AM 5:35:00 PM LATE/EARLY -
11:00:00 AM 8:30:00 PM 8:45:00 PM Single Punch Absent (This should be SP)
11:00:00 AM 8:30:00 PM Single Punch Absent
Please help
Your page is excellent; your team is very helpful. I'm struggling with an issue. I have one sheet where I would like to highlight only the rows where values duplicated in the first column where the there are differing values in the second column. Duplicates are allowed in the first column. Duplicate values are allowed in the second column, also. However, duplicated values in the first column should always be paired with the same value in the second column. If not, then highlight all rows where first column contains the same duplicated value.
Thank you.
Col-A Col-B Format
1 C Highlight Row
1 C Highlight Row
1 D Highlight Row
2 A No Highlight
2 A No Highlight
3 E No Highlight
4 C No Highlight
5 A Highlight Row
5 F Highlight Row
The spread sheet contain two column. In first column there is only the level i.e., Block, GP and Village and in another there is name contain of the respective Block, GP and Village. Let me know how I can manage the sheet that looks like in this manner as follows
Block GP Village
Block Name GP Name Village Name
(if there is 10 village then it is shown in row wise)
GP Name Village Name
Not understand the question dear.
Scenario1 :
Check text "abc.AD" in sheet one, and return the next two cell values in sheet two.
Note: The unique text is not in same column.
Sheet 1 :
Col10 Col11 Col13 Col14 col15 abc.AD Test NOP
abc.AD Ext NOP
abc.AD Int NOP
Let me know if this helps
Hello There, i am kind of stuck with manipulating the excel worksheets.
Scenario : Compare the cell text in two excel and replace range of value in sheet two with Values in Sheet one. Any guess how can we achieve this ?
Scenario : Wanted to Pull the Values from Sheet one With all the Cells containing abc*(text)in sheet 1 and Copy the same Text and adjacent 3 Values from each row.
Sheet 1 :
row/col Col1 col2 col3 col4
row1 123 abcde ghk 143
Sheet 2 :
row /col Col1 col2 col3 col4 col5
row1 abcde ghk c143
Hello There, i am kind of stuck with manipulating the excel worksheets.
Scenario : Compare the cell text in two excel and replace range of value in sheet two with Values in Sheet one. Any guess how can we achieve this ?
Scenario : Wanted to Pull the Values from Sheet one With all the Cells containing abc*(text)in sheet 1 and Copy the same Text and adjacent 3 Values from each row.
Sheet 1 :
row/col Col1 col2 col3 col4
row1 123 abcde ghk 143
Sheet 2 :
row /col Col1
Hi All,
Is it possible to compare my example below ? i need to compare column a + b in column c + d . my problem is on column a there is same values . please help me to create formulas if possible
column a column b column c column d
64571 75 64351 72.1
64351 72.1 64571 75
64571 73 64571 73