Related Links:
Count Number of Unique Values, with Excel Functions
Remove Duplicates or Create a List of Unique Records Using Advanced Data Filter
Remove Duplicates in a range, using “Remove duplicates” button in Data Tools
————————————————————————————————————————–
Remove Duplicates or Create a List of Unique Records in Excel
Create a List of Unique Records (Using Excel Formula):
1. Column B contains a list of names, including duplicates and a List of Unique Names is returned in column C, with the creation of the helper column (column A). (refer Table 1)
2. Create a helper Column towards the left viz. column A. Enter 1 in the first cell corresponding to the first name in column B.
3. Enter the formula =IF(COUNTIF( $B$2:B3,B3)=1, A2+1, A2) in cell A3 and copy it down column A. You will notice that if the number increments from the previous/upper cell in column A, then the corresponding record in column B is unique. The formula counts the number of names in column B till the current row, and increments the number in column A (from the upper cell) if the name has occurred only once till that row, else returns the same number as in the upper cell (in column A).
4. =IFERROR( VLOOKUP(ROW()-1, $A$2:$B$16,2, FALSE),””) -> Enter this formula in cell C2 and copy it down column C. The formula does a vlookup of running values starting from 1, searches for corresponding results in column B, and returns unique values in column C.
5. You can delete the helper column viz. column A, as its purpose has been served by now.
Explaining the formula in cell A3 (refer Table 1): =IF(COUNTIF($B$2:B3,B3)=1,A2+1,A2)
=COUNTIF($B$2:B3,B3) [Formula Break]
Counts the number of times the value in cell B3 (viz. Paul Spencer) appears in column B, till the current row (viz. in range $B$2:B3).
Please note the use of absolute and relative references for the range $B$2:B3. The absolute reference ($B$2) is used to start the search from the first cell in the range/column. The relative reference (B3) is used to keep the last cell in the range dynamic, till the current row.
=IF(COUNTIF($B$2:B3,B3)=1, A2+1, A2) [Formula]
If COUNTIF returns 1, which means the value in cell B3 is unique in column B till the current row, then formula increments cell A3 by 1 from cell A2, else cell A3 value remains same as cellA2.
This indicates that if the number in cell A3 increments by 1 from cell A2, then the corresponding record in column B (ie. cell B3) is unique till now.
Explaining the formula in cell C2 (refer Table 2): =IFERROR(VLOOKUP(ROW()-1, $A$2:$B$16, 2, FALSE), “”)
=ROW()-1 [Formula Break]
Row() returns the number of the current row. ROW()-1 is used (in cell C2) to start from number 1, which corresponds to the value in first cell in column A which is 1. This generates running numbers as you move down the column viz. number 2 in cell C3, number 3 in cell C4, and so on.
=(VLOOKUP(ROW()-1, $A$2:$B$16, 2, FALSE) [Formula Break]
This formula does a vlookup of each running number in column A, and returns matching names in column no 2 (which is column B), and these are unique names which appear in column C (where this formula is entered).
Vlookup of a number (viz. vlookup of number 3) will return the first occurrence of the matching value in column B (ie. Jim Clapton in cell B4) and ignore the subsequent occurrences (ie. John in cell B5). This is how the unique names appear in column C.
=IFERROR(VLOOKUP( ROW()-1, $A$2:$B$16,2, FALSE), “”) [Formula]
If the Vlookup formula returns an error (viz. #N/A), the formula will return a blank (“”) value.
Remove Duplicates or Create a List of Unique Records using Excel Formula
VBA TUTORIAL LOGIN
Hi ,Logout
Cond Statements in VBA Test
Text Strings in VBA
Custom Number Formats
Excel VBA Built-in Events
ActiveX & Form Controls
Application.OnTime Method
MsgBox & InputBox in VBA
- Ebook of Excel Solutions
- Excel Formulas Ebook
- Ebook of Excel Formulas
- Unique List, Sorted in Ascending Order, Appearing in One Column and Not in Other
- Count Rows containing Multiple specified Values
- SUM AND / OR Criteria for Multiple Columns
- Position of First Number in a String
- Extract Unique sub-strings of 1st 5 Characters from each column cell
- Count Maximum Number of Consecutive Occurrences of a Value
- SUMIF with Date Range, Multiple Month Names / Numbers & Year
- Extract Text After Last Occurrence of a Number in a String
- Extract Values which appear ONLY once, from a Column
- Excel Functions
- Excel Text and String Functions: TRIM & CLEAN
- ASCII Code, Extended ASCII characters (8-bit system) and ANSI Code.
- Excel CODE & CHAR Functions, VBA Asc & Chr Functions
- Excel Text and String Functions: LEFT, RIGHT, MID, LEN, FIND, SEARCH, REPLACE, SUBSTITUTE
- CHOOSE Function in Excel
- Excel IF Function and IF Statements
- Excel VLOOKUP Function
- Excel OFFSET Function
- Excel INDIRECT Function
- Excel SUMPRODUCT Function
- Excel Pivot Tables
- Printing a Pivot Table Report – Excel
- Create a Pivot Chart in Excel – graphical display of a Pivot Table
- Excel Pivot Table Design & Layout, Pivot Table Styles
- Excel Pivot Table Report – Sort Data in Row & Column Labels & in Values Area, use Custom Lists
- Excel Pivot Tables: Filter Data, Items, Values & Dates
- Excel Pivot Tables: Insert Calculated Fields & Calculated Items, Create Formulas
- Excel Pivot Table Report – Summary Functions & Custom Calculations, Insert Calculated Fields or Calculated Items
- Excel Pivot Table Report – Group Items, Group Date and Time Values
- Excel Pivot Table Report – Field Settings, Expand or Collapse Fields & Items, Refresh Data, Change Data Source & Show or Hide options
- Excel Pivot Table Report – Clear All, Remove Filters, Select Mutliple Cells or Items, Move a Pivot Table
- Excel Tips
- Case Sensitive Vlookup in Excel; Finding the 1st, 2nd, nth or last occurrence of the Lookup Value
- Vlookup Multiple Values – Return MULTIPLE corresponding values for ONE Lookup Value
- Excel Date and Time Functions and Formulas
- Find ‘Smallest’, ‘Largest’, ‘K-th Smallest’ and ‘K-th Largest’ Numbers in a Range
- Remove Duplicates in a range, using “Remove duplicates” button in Data Tools
- Remove Duplicates or Create a List of Unique Records using Excel Formula
- Remove Duplicates or Create a List of Unique Records using Advanced Data Filter
- Count Number of Unique Values in a Range (with Excel Functions)
- Shade Alternate Rows – Conditional Formatting
- Left Lookup with Vlookup Excel Function
- Excel VBA
- Excel VBA: ActiveX Controls, Form Controls & AutoShapes on a Worksheet
- ChartFormat object – line, fill & effect formatting for chart elements: FillFormat object, LineFormat object, ShadowFormat object, GlowFormat object, SoftEdgeFormat object, ThreeDFormat object
- Child Objects common for many chart elements: Border Object, ChartFillFormat Object, Interior Object, Font Object
- Chart Elements in Excel VBA (Part 2) – Chart Series, Data Labels, Chart Legend
- Chart Elements in Excel VBA (Part 1) – Chart Title, Chart Area, Plot Area, Chart Axes
- Charts in Excel VBA – Add a Chart, the Chart object & the ChartObject object
- Create Charts in Excel VBA: Embedded Charts – Line with Markers; Clustered Stacked Column Chart; Clustered Stacked Bar Chart displaying Variance; Pie chart; XY Scatter chart & Bubble chart
- Excel VBA Debugging Tools in Visual Basic Editor – Breakpoints & Break Mode, Stepping Through Code, Debugging Views
- Excel VBA Errors & Error Handling, On Error & Resume Satements, Exit Statement, Err Object
- Excel VBA Date & Time Functions; Year, Month, Week & Day Functions
- ExcelAnytime Site
- My Account
- Website Use Policy
- Excel VBA Online Tutorial
- Testimonials GlobaliConnect.com
- About ExcelAnytime.com
- Excel VBA Online Tutorial – learn Excel VBA programming & access examples, illustrations, live codes and downloadable files
- Excel and VBA Services
- Sample Data-Articles
- test
- Getting Started
- Typography
- Article Categories Module
- Search Module
- Joomla!
- Authentication
- Articles Category Module
- Statistics Module
- Parameters
- Fruit Shop SiteGrowersJoomla!
- Parameters
- Editors
- Banner Module
- Professionals
- Editors-xtd
- Custom HTML Module
- Sample Sites
- Search
- Feed Display
- The Joomla! Community
- Users
- News Flash
- Menu Module
- Weblinks
- Related Items Module
- Breadcrumbs Module
- News Feeds
- Login Module
- Typography
- Who’s Online
- Most Read Content
- Wrapper Module
- News Flash
- Menu Module
- Related Items Module
- Breadcrumbs Module
- Login Module
- Who’s Online
- Latest Users Module
- Banner Module
- Most Read Content
- News Flash
- Related Items Module
- Archive Module
- Article Categories Module
- Articles Category Module
- Latest Articles Module
- Phyllopteryx
- Spotted Quoll
- Wobbegone
- Cradle Mountain
- Pinnacles
- Blue Mountain Rain Forest
- Ormiston Pound
- Australian Parks
- First Blog Post
- Second Blog Post
- Cradle Mountain
- Pinnacles
- Blue Mountain Rain Forest
- Ormiston Pound
- Koala
- Phyllopteryx
- Spotted Quoll
- Wobbegone
- VBATutorial - Cond Stmnts