Records - Get Digital Help

Archive for the ‘Records’ Category

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

Highlight date ranges overlapping selected record (vba)
The following example shows you how to highlight overlapping ranges. How it works Select a date in the table. Conditional formatting highlight date ranges overlapping the selected record in the table. Instructions VBA Code Right click on sheet name Click […]

Quickly search a table using many criteria
Mohsin Ali Raziq asks: I have problem, and o dont know how to solve it, i have data of almost 10000 forms, from which i have to find 1500, so it is very difficult to dig out 1500 one by […]

Unique distinct records sorted by frequency
Sara asks: How can you use large with multiple criteria?? Example looking for top 5 of a list based on name, state and city.. Answer: Array formula in cell E3: =INDEX($A$2:$C$29, MATCH(LARGE(IF(COUNTIFS($E$2:E2, $A$2:$A$29, $F$2:F2, $B$2:$B$29, $G$2:G2, $C$2:$C$29)=0, COUNTIFS($A$2:$A$29, $A$2:$A$29, $B$2:$B$29, […]

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

How to sort a table in a custom order in excel
Your boss wants you to sort the company´s products by a new criterion, quality. You receive a list from your boss and now you have to sort your products by this list. You copy […]

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

Excel 2007 pivot table: Count unique distinct records (rows)
Table of Contents Count unique distinct records Count duplicate records Excel 2007 pivot table: Count unique distinct records (rows) The table I am working with is in cell range B2:D7. I have then added another column (E) to count unique […]

Sum unique distinct invoices in excel 2007
Question: I have a long table The key is actually col B&C BUT…sometime there are few rows with same key (like rows 3:4 or rows 8:10). I'd like to sum data in column D and to consider same key rows […]

Filter unique distinct records with a condition in excel 2007
Sean asks: If Tea and Coffee has Americano,it will only return Americano once and not twice. I am looking for a unique distinct list based on each condition. I remember reading that Excel has difficulty with these type of or conditions […]

Excel udf: Filter unique distinct records (case sensitive)
How to use udf (array formula) Select cell range F3:G6 Type =UniqueRecords((C3:D8) in formula bar. Press and hold CTRL + SHIFT Press Enter once Release all keys Excel user defined function: Function UniqueRecords(rng As Variant) As Variant() ' This udf filters […]

Quickly highlight records containing text strings in excel (AND Logic)
This blog post shows you how to highlight rows where text string criteria are found. Example, continents criterion (cell B3) is only searched in column Continents (B7:B28). Color criterion is searched for in column Color, and so on. Conditional formatting […]

Quickly highlight records in a list in excel (AND logic)
This blog post shows you how to create a conditional formatting formula and highlight matching records. You can easily change criteria by editing row 3 and 4. Conditional formatting formula: =SUMPRODUCT(IF(AND(ISBLANK($B$3:$B$4)), 1, ($B$3<=$B7)*($B$4>=$B7))*(IF(ISBLANK($C$3), 1, $C$3=$C7))*(IF(ISBLANK($D$3), 1, $D$3=$D7))*(IF(AND(ISBLANK($E$3:$E$4)), 1, ($E$3<=$E7)*($E$4>=$E7)))) How […]

Quickly highlight records in a list using multiple criteria in excel
This blog post shows you how to highlight rows with multiple criteria using OR logic. The criteria is found in row 3. A row is highlighted if: Date criterion is found in column B or Color criterion is found in column […]

Find duplicate records in excel 2007
Formula in cell G3: =IF(COUNTIFS($B$3:B3, B3, $C$3:C3, C3, $D$3:D3, D3, $E$3:E3, E3)>1, "Duplicate", "") + ENTER Copy cell G3 and paste down as far as needed. How the formula works in cell G13 Step 1 - Understand how relative and […]

Count unique distinct records in excel 2007
This example sheet has 4 columns with some random data. It is quite complicated trying to count unique distinct records from this table but Excel can help us with that. A record is an entire row in the table below. […]

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

Filter and highlight duplicate column records in excel 2007
This article describes how to highlight duplicate records and filter duplicate records. There is one record in each column, cell range B2:E5. See picture below. The array formula and conditional formatting formula in this article contain "Countifs", a function introduced […]

Filter duplicate rows in excel 2007
This article describes how to filter duplicate rows. The array formula in this article contains countifs, a function introduced in excel 2007. Countifs applies criteria to cells across multiple ranges and counts the number of times all criteria are met. Array […]

Highlight duplicate rows in excel 2007
This blog post shows you how to easily identify duplicate rows or records in a list. Conditional formatting formula: =COUNTIFS($A$1:$A$30, $A1, $B$1:$B$30, $B1, $C$1:$C$30, $C1)>1 + ENTER How to use conditional formatting formula Select cells A1:C30 Click "Home" tab Click […]

Match two criteria and return multiple rows in excel
Question: I have a table of 3 coloms (Security name, date, price) and I have to find the price a a security at a certain date in a table that contain many securities and prices for this securities for different […]

Vlookup of three columns to pull a single record
Question: Does anyone know how to do a vlookup of three columns to pull a single record? Answer: I am guessing that you want to lookup a value in any of three columns to pull a single record. In this […]

Filter unique distinct row records
Table of contents Filter unique distinct row records Filter unique distinct row records but not blanks Filter unique distinct row records that does not begin with *string* Filter unique distinct row records that begins with *string* Question: How do I […]

« 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