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 […]

## Archive for the ‘Compare’ Category

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 […]

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 […]

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 […]

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 […]

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.

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 […]

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 […]

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 […]

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 […]

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 […]

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, […]

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 […]

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 […]

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 […]

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 […]

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 […]

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 […]

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 […]

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 […]

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 […]

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, […]

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 […]

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 […]

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 - […]

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 […]

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. […]

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. […]

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 […]

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 […]

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: […]

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 […]

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 […]

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 […]

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 […]

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, […]

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 […]

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 […]

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)) […]

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 […]

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 […]

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, […]

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, […]

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 […]

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))), […]

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, […]

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 […]

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 […]

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 + […]

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 […]