Compare two columns in different worksheets

sissey asks: Hi Oscar, There are multiple columns in two different worksheets, one has more columns than another. I need […]

Compare two lists and filter unique values where the sum in one column doesn’t match the other column

Sean asks: How would you figure out an unique list where the sum of in one column doesn't match the […]

Compare values between two columns and filter values existing in only one column

There are text values in column A and column B. The question is how do you compare the values in […]

Filter values that exists in all three lists

Peter Voss asks: This is close to what I need. I have three lists of email addresses. If an email […]

Compare two tables: Remove common records

Table of Contents Compare two tables: Remove common rows Compare two tables using a condition Let me demonstrate how to extract […]

How to compare two data sets

This blog post demonstrates how to quickly compare two tables in Excel. Table1 Table2 Create a fourth column and use […]

How to highlight differences in price lists

Today I am going to show you how to quickly compare two tables using Conditional Formatting (CF). I am going […]

Compare two lists of data: Filter records occurring in only one list

In this example we are going to use two lists with identical columns in excel 2007. It is easy to […]

Compare two tables: Filter common records

I will in this blog post describe how to extract common records from two data sets in Excel. I have […]

Compare two columns and show differences

Array Formula in B11: =INDEX($B$3:$B$7, SMALL(IF(COUNTIF($D$3:$D$8, $B$3:$B$7)=0, MATCH(ROW($B$3:$B$7), ROW($B$3:$B$7)), ""), ROWS($A$1:A1))) To enter an array formula press and hold CTRL […]

Filter common text values in two different ranges

Array formula in B26: =INDEX(tbl_1, MIN(IF((COUNTIF(tbl_2, tbl_1)>0)*ISTEXT(tbl_1)*NOT(COUNTIF($B$25:B25, tbl_1)), ROW(tbl_1)-MIN(ROW(tbl_1))+1)), MATCH(0, ISNONTEXT(INDEX(tbl_1, MIN(IF((COUNTIF(tbl_2, tbl_1)>0)*ISTEXT(tbl_1)*NOT(COUNTIF($B$25:B25, tbl_1)), ROW(tbl_1)-MIN(ROW(tbl_1))+1)), , 1))+COUNTIF($B$25:B25, INDEX(tbl_1, MIN(IF((COUNTIF(tbl_2, tbl_1)>0)*ISTEXT(tbl_1)*NOT(COUNTIF($B$25:B25, […]

Filter text values existing in the first range but not in the 2nd range

Click image to enlarge. Array formula in B26: =INDEX(tbl_1, MIN(IF((COUNTIF(tbl_2, tbl_1)=0)*NOT(COUNTIF($B$25:B25, tbl_1))*(ISTEXT(tbl_1)), ROW(tbl_1)-MIN(ROW(tbl_1))+1, "")), MATCH(0, ISNONTEXT(INDEX(tbl_1, MIN(IF((COUNTIF(tbl_2, tbl_1)=0)*NOT(COUNTIF($B$25:B25, tbl_1))*(ISTEXT(tbl_1)), ROW(tbl_1)-MIN(ROW(tbl_1))+1, […]

Create a list with most recent data available

Question: i have two sets of data - one has an identifier column and one result column. A2 data1 B2 […]

Comparing two columns and sum unique values

Array Formula in A15: =SUM(IF(COUNTIF(List1, List1)+COUNTIF(List2, List1)=1, List1)+IF(COUNTIF(List2, List2)+COUNTIF(List1, List2)=1, List2)) + CTRL + SHIFT + ENTER Named ranges List1 […]

Filter common values between two ranges

Question: How do I filter common values between two ranges using array formula in excel? Answer: Formula in B12: =INDEX(One, […]

Filter values occurring in range 1 but not in range 2

Question: How do I filter values existing in one range but not in an other? Answer: Formula in B13: =INDEX(One, […]

Filter common values from three columns

Question: How do I filter values that exists in all three columns? Answer: Formula in A14: =INDEX(List1, MATCH(0, COUNTIF($A$13:A13, List1)+IF(IF(COUNTIF(List2, […]

Highlight duplicate values in two cell ranges combined

Question: How do I highlight duplicate values in two ranges combined? Answer: Range 1, A1:D4, named tbl Range 2, A6:D9, […]

How many common cell values are there in list 1 and 2?

Question: How many common cell values are there in list 1 and 2? Answer: Here is how to count matching […]

How to extract not common values between two columns

Question: How do i remove common values between two lists? Answer: The solution in this article, removes common values and […]

Extract shared values between two columns

Question: How can I compare two columns to find values that exists in both cell ranges? The picture above shows […]

What values are missing in List 1 that exists i List 2?

Question: How to filter out data from List 1 that is missing in list 2? Answer: This formula is useful […]