Merge three columns into one list
The above image demonstrates a formula that adds values in three different columns into one column. Formula in H2: =IFERROR(INDEX($B$3:$B$7, […]
Latest modified blog articles
How to create a unique distinct list based on two conditions
Question: How do I create a unique distinct list where other columns meet two criteria using excel array formula? Answer: […]
Create a unique distinct list based on criteria
The regular formula in cell E6 extracts unique distinct values from column B based on the corresponding number in column […]
Sum number based on corresponding unique value
The formula in cell E14 adds a number from column if corresponding value in column B is unique. Formula in […]
Extract a list of duplicates from two columns combined
The following regular formula extracts duplicates from column B (List1) and column D (List2) combined, the result is shown in […]
Extract a list of duplicates from three columns combined
The following regular formula extracts duplicate values from column B (List1), D (List2) and F (List3) combined, the result is […]
A record is returned if both search strings are found on the same row
Question: I want to search two columns with two search strings? The strings can be anywhere in these two columns […]
Extract a unique distinct list from three columns
Question: How do I extract a unique distinct list from three ranges or lists? The ranges are not necessarily adjacent […]
Extract a unique distinct list from two columns
Question: I have two ranges or lists (List1 and List2) from where I would like to extract an unique distinct […]
Identify missing numbers in a column
Table of contents Missing numbers (array formula) Missing numbers (vba) Missing numbers (array formula) Array Formula in D6 =SMALL(IF(ISERROR(MATCH($E$2+ROW(OFFSET($B$2, 0,0, […]
Count unique distinct values in two columns
Formula in C12: =SUM(1/COUNTIF($B$3:$B$8, $B$3:$B$8))+SUM(IF(COUNTIF($B$3:$B$8, $D$3:$D$8)=0, 1/COUNTIF($D$3:$D$8, $D$3:$D$8), 0)) How to create an array formula Double click on cell C12 […]
Highlight duplicates in two columns
Question: I need to delete duplicates in two different columns together. How do I highlight the second or more duplicates […]
Sort dates within a date range
Array formula in D5: =SMALL(IF(($B$3:$B$12<=$E$3)*($B$3:$B$12>=$E$2), $B$3:$B$12, "A"), ROWS($A$1:A1)) How to create an array formula Copy array formula Select cell D5 […]
How to rank uniquely based on a condition
The following formula ranks text values in column C uniquely based on the category in column B. Formula in D3: […]
Split values equally into groups
Question: How do I divide values equally into groups (3 lists or less)? This post shows you two different approaches, […]
Highlight missing values between to columns
The picture above shows two lists. How do I highlight values in List 1 that are not in List 2? […]
How to highlight duplicate values
The picture above shows duplicate values in column B, only the second or more duplicates are colored and easily identified. […]
Count unique distinct records with a date and column criteria
davidlim asks: re: Count records between two dates and a criterion based on the example, i was looking for 1 […]
Highlight dates in a date range
Question: How do I highlight dates that meet criteria using conditional formatting? Table of contents Highlight values in a column […]
Create a unique distinct list and sort based on frequency
Question: How do I create a new unique distinct list from a column. I also want the list sorted from large […]
Extract unique values from two columns
I read an article Merging Lists To A List Of Distinct Values at CPearson. The article describes code that you […]
Count how many times a string exists in a cell range (case insensitive)
Question: How do I count how many times a word exists in a range of cells? It does not have […]
Extract all rows that contain a value between this and that
Question: I have a list and I want to filter out all rows that have a value (Column C) that […]
Count dates inside a date range
How do I automatically count dates in a specific date range? Array formula in cell D3: =SUM(IF(($A$2:$A$10<$D$2)*($A$2:$A$10>$D$1), 1, 0)) + […]
Delete blanks and errors in a list
The formula deletes blank cells and cells with errors. It doesn't matter if the cells contain numbers or text, they […]
In this blog post I will provide two solutions on how to remove blank cells and a solution on how […]
Calculate last date of a given month
The formula in cell C3 calculates the last date for the given month and year in cell B3. =DATE(YEAR(B3), MONTH(B3)+1, […]
The image above shows a formula in cell D3 that extracts the latest date in cell range B3:B15. =MAX(B3:B15) The MAX […]
Create a numbered list ignoring blank cells
The formula in column B returns a running count based on values in column C. Formula in cell B3: =IF(C3<>"",COUNTA($C$3:C3),"") […]
The image above shows numbers in column B, some of these numbers are duplicates. The formula in D12 adds unique […]
How to rank text uniquely without duplicates
Question: How do I rank text cell values uniquely? If text values were sorted alphabetically from A to Z, the […]
Search values distributed horizontally and return corresponding value
Question: Hi, The formula here works great but I can't figure out how to change it to work with data […]
How to use the PERCENTRANK function
The PERCENTRANK function calculates the percent rank of a given number compared to the whole data set. The image above […]
Match two criteria and return multiple records
Question: I have a table of 3 columns (Security name, date, price) and I have to find the price of […]
Extract records where all criteria match if not empty
Question: I second G's question: can this be done for more than 3? i.e. (Instead of last name, middle, first) […]
Cell range B3:B14 has conditional formatting applied, the formula checks if the date is today. Conditional formatting formula: =TODAY()=B3 A […]
Extract unique distinct year and months from dates
Question: How to create unique distinct year and months from a long date listing (column A)? You can find the […]
How to perform a two-dimensional lookup
Question: How would I go about looking up data in a cross-reference table. I have the header row (i.e. 24) […]
The picture above shows data presented in only one column (column B), this happens sometimes when you get an undesired […]
INDEX MATCH with multiple criteria
The formula demonstrated in cell D13 is a regular formula, most people prefer a regular formula over an array formula […]
INDEX MATCH – multiple results
The array formula in cell E6 extracts values from column C when the corresponding value in column B matches the […]
The picture above demonstrates a formula in cell F3 that allows you to look up a value in column B […]
The array formula in cell C11 gets 3 values in one fetch, the INDEX function allows you to do that […]
INDEX and MATCH – multiple criteria and multiple results
The formula in cell C14 returns multiple values from column Item. It uses multiple criteria specified in C12:C13 and applied […]
How to remove numbers from a cell value
The array formula in cell C3:C7 extracts everything except numbers from cell B3. The following formula contains the TEXTJOIN function […]
Identify all characters in a cell value
Sometimes when you sort values you get unexpected results, the cause is probably unwanted characters in a cell. The same […]
How to remove unwanted characters in a cell
Cell B3 contains a few odd characters and the formula in C3 shows the ANSI equivalent of each character in […]
How to extract numbers from a cell value
The following array formula, demonstrated in cell C3, extracts all numbers from a cell value: =TEXTJOIN(, 1, TEXT(MID(B3, ROW($A$1:INDEX($A$1:$A$1000, LEN(B3))), […]
INDEX and MATCH are more versatile than the VLOOKUP function in terms of lookups, however, it only gets the first […]
How to use the EVALUATE method
The Evaluate method converts an Excel name to an object or value. The picture above demonstrates a macro that uses […]
Welcome! I am 