Compare - Get Digital Help

Archive for the ‘Compare’ Category

Count matching strings using regular expressions
A regular expression is a pattern containing specific characters to search for sub-strings in strings. The patterns are at first glance hard to understand but with a little bit of practice very useful. Imagine you want to extract phone numbers from […]

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 to compare column F of worksheet 1 and column E of worksheet 2; if the value matches, compare column G […]

Calculate time between time zones
This worksheet lets you enter cities and their time difference. Excel calculates the corresponding local times in E5:E8. Press F9 to refresh the time and date in cell E3. Formula in cell E3: =NOW() Formula in cell E5: =$E$3+C5/24 Copy this cell and paste to […]

Count overlapping days in multiple date ranges
The MEDIAN function lets you count overlapping dates between two date ranges. If you have more than two date ranges you need to use a more complicated array formula. I have three date ranges (A2:B4) in this example and I […]

Dynamic scoreboard
Here comes another post about the MMULT function, today I made a dynamic scoreboard. There are five women competing and there are 8 rounds. Look what happens with the scoreboard as I type new values in round 3.

Working with overlapping date ranges
Today's blog post is about date ranges, the techniques demonstrated here can also be applied to time or other numerical ranges. The MMULT function is a great excel function, it allows you to do really amazing calculations with date ranges. Yes, I […]

Compare file names in two different folder locations and their sub folders
Today I would like to share a macro that compares the content in two different folders and their sub folders. It compares the file names but not the file sizes. In this example the picture shows the content in these […]

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 other column? I'm comparing list 1 to list 2 List 1 A 10 A 10 B 5 B 6 B […]

Merge lists with criteria
I have written a post about merging two single columns or ranges before: Merge two columns with possible blank cells . It demonstrates how to merge two different cell ranges dynamically and that is it. The following examples merge data tables with […]

Find the most urgent work orders
This animated picture shows you the most urgent work orders for a location. Type a location in cell F3 and the formula in cell G3 extracts the most urgent value for that particular location. The values in column C are […]

Team ranking with criteria
Today I´ll show you how to rank teams using excel formulas. The following example ranks soccer teams in the Premier League. But first, how are soccer teams ranked in Premier League? Teams are ranked by total points, then goal difference, […]

Identify overlapping records
cwrbelis asks: Hi Oscar, Great website! Keep up the good work. I have a question as to how to expand this to the next step. Per your initial example we can see that Jeff in Row 1 and Shaun in […]

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 these two columns? More specifically, filtering values existing only in column A (or column B)? The array formula in cell […]

Calculate min and max date among overlapping date ranges and based on a condition
Liz asks: I want to identify the overlap based on a criteria but now I want to know what is that min date and the max date. Any tricks up your sleeve? The following formulas calculates the min and max […]

Prevent duplicate records (data validation)
Debra Dalgleish demonstrated last week how to block duplicate entries in excel table. I made a comment and I thought I would share it with you. Here is an excel table with example data. Let me show you how to prevent duplicate records […]

Heat map using pictures
I made a heat map calendar a few months ago and it inspired me to do this post. The heat map calendar changes background color of each cell unlike the technique used here where I change the brightness of each […]

Count students
Kiishore asks Hi Oscar, I have the following issues. Any suggestions. Sheet 1 : Grade Male Female Primary Secondary High School College Gender Sheet 2 : Col A : Student Id Col B: Gender : Here we specify whether the […]

Prevent users from entering overlapping date and time ranges
The picture below shows a table with data validation applied. If a user tries to enter an overlapping date range, the following error message appears. How to create the data validation If you apply data validation to a table, all […]

Compare your stock portfolio with S&P500 in excel
By comparing your stock portfolio performance to index S&P500 you know if the time you spent on analyzing companies paid off. In fact, Warren Buffet recommends investing in an SP500 index fund if you have no knowledge investing in the stock […]

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 address appears in all three (not two out of three) lists then place it in the duplicate column. Also I […]

Excel: List intervals between two values
jeyner asks: I need to count in a list the interval between the same value. Example list, 1-2-3-1-4-5-1-6-7-8-9-7-8-1 So the answer must be for the value 1 the spaces are 2,2,6. thank you Answer: Array Formula in cell E2: =SMALL(IF($A$1:$A$14=$C$2, […]

Excel table: Filter unique distinct rows and use additional filters at the same time
This blog post describes how to filter unique distinct rows using an excel table. This post shows you how to do it using array formulas: Filter unique distinct records with a condition in excel 2007 The difference with today´s blog post is […]

Compare two tables in excel: Remove common rows
Table of Contents Compare two tables: Remove common rows Compare two tables using a condition Let me demonstrate how to extract non common records from two tables. The formulas in this post contains the COUNTIFS function and is introduced in excel […]

Compare pricelists in excel 2007
This post describes how to compare two price lists in excel 2007. I am going to create three conditional formatting formulas and highlight: new products yellow prices higher than previous year green prices lower than previous year red Sheet1 - […]

Quickly compare two tables in excel 2007
This blog post demonstrates how to quickly compare two tables in excel 2007. Table1 Table2 Create a fourth column and use this formula in first cell: Table1 =COUNTIFS(Table2[GivenName], Table1[[#This Row], [GivenName]], Table2[StreetAddress], Table1[[#This Row], [StreetAddress]], Table2[City], Table1[[#This Row], [City]])>0 This […]

How to quickly compare pricelists in excel
Today I am going to show you how to quickly compare two tables using conditional formatting. We are comparing two price lists from the year 2010 and year 2011. To make things more interesting, price list 2011 is not sorted. […]

Excel udf: Find numbers in sum
This article describes how to find a sum from a range of numbers using a user defined function. Let´s see how quickly excel solver finds the numbers using the instructions from this post: Identify numbers in sum using solver in excel. […]

Filter overlapping date ranges in excel 2007
Introduction This blog article describes how to extract coinciding date ranges. Example, Array formula Cell range B3:C25 contains example date ranges. Overlapping date ranges Array formula in cell E4: =SMALL(IF(COUNTIFS($B$3:$B$25, "<="&$C$3:$C$25, $C$3:$C$25, ">="&$B$3:$B$25)>1, $B$3:$B$25, ""), ROW(A1)) + CTRL + SHIFT […]

Compare two lists of data: Filter records existing in only one list in excel
In this example we are going to use two lists with identical columns in excel 2007. It is easy to modify the countifs function if your columns are not ordered. This is the first example list on sheet: List 1 […]

Compare two lists of data: Highlight records existing in only one list in excel
Overview There are two lists in this example: Sheet: List 1 Sheet: List 2 Create named ranges Select A2:A13 on sheet "List 2" Type Year in Name Box Press Enter Repeat with remaining ranges: Sheet: List 2 , Range:B2:B13, Name: […]

Compare two lists of data: Highlight common records in excel
Overview In this blog post you will learn how to: Create a countifs function Create a conditional formatting formula and reference cells outside current sheet Highlight common records in two lists Sheet: List 1 Sheet: List 2 Create named ranges […]

Compare two lists of data: Filter common row records in excel
This blog post describes how to extract common rows (records) from two lists in excel 2007. Sheet: List 1 Sheet: List 2 Comparing the two lists We need to know what columns to compare before we can create the array […]

Automate excel: Update list with new values
Overview Updating a list using copy/paste is a boring task. This blog article describes how to update values in a price list with new values. Sheet 1 is the old price list. It contains 5000 products and amounts. Sheet2 is the […]

Excel udf: Filter values existing only in one out of two ranges
This post describes how to filter values existing only in one out of two cell ranges. See picture. This udf is not case sensitive Excel user defined function Function Filter_Values(rng1 As Variant, rng2 As Variant) As Variant ' This udf […]

Excel udf: Filter common values between two cell ranges in excel
I tried the array formula in this post: Filter common values between two ranges using array formula in excel to extract common values between two cell ranges. 40000 random cell values in each cell range. As you might have guessed, […]

Compare and filter values between two lists in excel
Question: Hi, I have seen a lot of examples related to unique list ... mais not found what I need. my problem is the following : I have 2 lists of items in col A and B not necessarely sorted […]

Filter common text values in range 1 and in range 2 using array formula in excel
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, tbl_1)), ROW(tbl_1)-MIN(ROW(tbl_1))+1)), , 1))+(COUNTIF(tbl_2, 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))=0), 0)) + CTRL + SHIFT + ENTER copied down […]

Filter text values existing in range 1 but not in range 2 using array formula in excel
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, "")), , 1))+COUNTIF(tbl_2, 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, "")), , 1))+COUNTIF($B$25:B25, 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, "")), , 1)), 0)) […]

Create a list with most recent data available in excel
Question: i have two sets of data - one has an identifier column and one result column. A2 data1 B2 22 A3 data2 B3 55 A4 data3 B4 44 A5 data5 B5 22 A6 data4 B6 33 A7 data9 B7 […]

Comparing two columns and sum unique values using array formula in excel
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 (A2:A11) List2 (B2:B11) What is named ranges? Download excel sample file for this tutorial. Comparing two columns and sum unique […]

Filter common values between two ranges using array formula in excel
Question: How do I filter common values between two ranges using array formula in excel? Answer: Formula in B12: =INDEX(One, MIN(IF((COUNTIF(Two, One)>0)+COUNTIF(B12:$B$12, One)=1, ROW(One)-MIN(ROW(One))+1, "")), MATCH(0, NOT(COUNTIF(Two, INDEX(One, MIN(IF((COUNTIF(Two, One)>0)+COUNTIF(B12:$B$12, One)=1, ROW(One)+1, ""))-MIN(ROW(One)), , 1)))+COUNTIF(B12:$B$12, INDEX(One, MIN(IF((COUNTIF(Two, One)>0)+COUNTIF(B12:$B$12, One)=1, ROW(One)+1, […]

Filter values existing in range 1 but not in range 2 using array formula in excel
Question: How do I filter values existing in one range but not in an other? Answer: Formula in B13: =INDEX(One, MIN(IF((COUNTIF(Two, One)=0)+COUNTIF(C12:$C$12, One)=1, ROW(One)-MIN(ROW(One))+1, "")), MATCH(0, COUNTIF(Two, INDEX(One, MIN(IF((COUNTIF(Two, One)=0)+COUNTIF(C12:$C$12, One)=1, ROW(One)-MIN(ROW(One))+1, "")), , 1))+COUNTIF(C12:$C$12, INDEX(One, MIN(IF((COUNTIF(Two, One)=0)+COUNTIF(C12:$C$12, One)=1, ROW(One)-MIN(ROW(One))+1, […]

Filter common values from three columns in excel
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, List1)>0, 1, 0)+IF(COUNTIF(List3, List1)>0, 1, 0)=2, 0, 1), 0)) + CTRL + SHIFT + ENTER copied down as far as […]

Highlight unique distinct values in two ranges combined using conditional formatting in excel
Conditional formatting formula in B2:E5: =IF(ROW(B2)>MIN(ROW(tbl)), COUNTIF(OFFSET(tbl, 0, 0, MIN(ROW(B2)-MIN(ROW(tbl))), MAX(COLUMN(tbl))), B2)+COUNTIF(OFFSET(tbl, MIN(ROW(B2)-MIN(ROW(tbl))), 0, 1, MIN(COLUMN(B2))), B2), COUNTIF(OFFSET(tbl, 0, 0, 1, MIN(COLUMN(B2))), B2))=1 Conditional formatting formula in B7:E10: =IF(COUNTIF(tbl, B7)+IF(ROW(B7)>MIN(ROW(tbl1)), COUNTIF(OFFSET(tbl1, 0, 0, MIN(ROW(B7)-MIN(ROW(tbl1))), MAX(COLUMN(tbl1))), B7)+COUNTIF(OFFSET(tbl1, MIN(ROW(B7)-MIN(ROW(tbl1))), 0, 1, MIN(COLUMN(B7))), […]

Highlight duplicate values in two ranges combined using conditional formatting in excel
Question: How do I highlight duplicate values in two ranges combined? Answer: Range 1, A1:D4, named tbl Range 2, A6:D9, named tbl1 Conditional formatting formula in A1:D4: =IF(ROW(A1)>MIN(ROW(tbl)), COUNTIF(OFFSET(tbl, 0, 0, MIN(ROW(A1)-MIN(ROW(tbl))), MAX(COLUMN(tbl))), A1)+COUNTIF(OFFSET(tbl, MIN(ROW(A1)-MIN(ROW(tbl))), 0, 1, MIN(COLUMN(A1))), A1), COUNTIF(OFFSET(tbl, […]

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 entries in two columns. Array formula in cell E2: =COUNT(MATCH(A2:A17;B2:B17;0)) + Ctrl + Shift + Enter Explaining array formula =COUNT(MATCH(A2:A17;B2:B17;0)) Step […]

How to remove 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 the original values, as seen on the picture below. To create a unique list from two columns or two cell […]

How to identify two consecutive dates in a list
Question: How to identify two consecutive dates in a list? Answer: Array formula in cell B1: =IFERROR(LARGE(IF((A1+1)=$A$1:$A$30,A1+1,""),1),"") How to enter an array formula Select cell B1 Copy above formula (Ctrl + c) Click in formula bar Paste formula (Ctrl + […]

How to find common values from two lists
Question: How can I compare two columns to find common values? Array formula in C2: =INDEX($A$2:$A$11, SMALL(IF(COUNTIF($B$2:$B$11, $A$2:$A$11), MATCH(ROW($A$2:$A$11),ROW($A$2:$A$11)), ""), ROWS($A$1:A1))) How to create an array formula Select cell C2 Click in formula bar Copy Paste array formula to formula […]

« previous entries

Welcome! I am Oscar and here to help you! Feel free to comment and ask excel questions.
Don´t forget to add my RSS feed and subscribe to new blog articles by email.
Sign up for free blog updates

Email
Search website
Popular articles

1. How to return multiple values using vlookup

2. How to extract a unique distinct list from a column

3. How to create a dynamic chart (excel 2003 and 2007)

4. Match two criteria and return multiple rows

5. How to create a list of random unique numbers

6. Create dependent drop down lists containing unique distinct values

7. Create a drop down list containing only unique distinct alphabetically sorted text values using excel array formula

8. Concatenate a cell range without vba

9. Auto refresh a pivot table

10. Excel udf: Lookup and return multiple values concatenated into one cell
10,303 subscribers