Excel unique from two lists. Download the Excel file and create this drop-down list. For example I have two seperate lists. 1000 Excel Formulas Over 1000 working Excel formulas with detailed explanations, videos, and related links. Oct 22, 2024 · Learn how to generate unique combinations from multiple lists in Excel using step-by-step formulas or quickly with the Kutools for Excel tool. It also has expected results mocked up, relevant cells highlighted and a few explanatory notes. In the example shown, the formula in D5 is: =UNIQUE(FILTER(B5:B16,(C5:C16="b")*(D5:D16>5))) which returns the 3 unique colors in group B with a quantity > 5. A combination of INDEX, MATCH and COUNTIF can easily do this. List of NBA & ABA Champions Oct 20, 2018 · List duplicate values Having looked at extracting a list of unique values, we now move on to consider extracting a list of duplicate values. They each do exactly what I needed. Aug 8, 2025 · Learn how to use VLOOKUP to create a unique list from a table in Excel. The following formula returns values that are unique from two columns combined. Any part of the unique list can be copied and pasted. Any help will, as always, be greatly appreciated! Thanks Ross Attached Files TEST1. To remove duplicate values, select Data > Data Tools > Remove Duplicates. Before the introduction of the UNIQUE function, there wasn’t any function that allowed picking out unique values from a dataset so seamlessly. Each of them contains the Top Sales Person list for a month. Dec 14, 2011 · Hi, I'm looking to combine two dynamic lists in excel and create one combined list with no duplicates, only unique values. Aug 2, 2022 · To extract a list of unique values from a set of data, while applying one or more logical criteria, you can use the UNIQUE function together with the FILTER function. A good sample workbook has just 10-20 rows of representative data that has been desensitised. Whether you’re working with inventory records, customer databases, or project tasks, knowing how to compare two lists in Excel can save time and improve accuracy. I have performed it manually using remove duplicates from the ribbon, using PivotTables and with complex formulas, but that is now a thing of the past. The generic version of the formula looks like this: Nov 8, 2018 · I've been using index, match and countif to list unique values happily for some time, but now I want to list unique pairs from an excel sheet based on two columns of values. I would like to merge these columns such that only the unique values are included in the final list. Jun 12, 2014 · Create unique list of values from two ranges with criteria I have two lists of values in separate columns with condition in adjacent cells, all on the same sheet List 1 consists of numbers from 1 to 12, column to the right is either blank or Free List 2 consists of numbers from 13 to 24, column to the right is either blank of Free Feb 20, 2025 · Discover five methods to extract unique lists in Excel, from simple one-click solutions to advanced formulas, to clean data and streamline your analysis. Apr 17, 2025 · Learn how to create a list of unique values from multiple sheets in Excel using VBA. Jan 27, 2022 · This tutorial explains how to find unique values from multiple columns in Excel, including an example. The Excel UNIQUE function can extract a list of distinct values, or extract a list of values that only occur once. Discover four effective methods to create a clean, unique dataset quickly and efficiently. Unique, Sort, Filter, Validation Lists and Arrays Good morning everyone, I am struggling with array in combination of a validation list. gov if you have limited access to a phone or cell service How to create a profile Jul 28, 2024 · The Excel UNIQUE function identifies and extracts unique values within a given range or list. Manually scanning two lists for differences is slow and error-prone. May 6, 2014 · Merging two list into one using formula and keeping only unique values hi, i'm looking to merge two lists of names into one combined list with only unique values. Jun 16, 2009 · I have two columns of data, A and B. Use this spill range to create a magic drop-down list. In Excel, there are several ways to filter for unique values—or remove duplicate values: To filter for unique values, select Data > Sort & Filter > Advanced. The formula in Cell D3 is so long that it is included on two lines below, but you can have it on a single line within Excel. This step-by-step tutorial will show you how to quickly and easily identify unique values in a range of cells, even if they are repeated in other rows or columns. Unique values are values that only exist once in both columns. Re: lookup Multiple lists with unique part numbers for total quantity Then explain why there are used differant worksheets (instead of 1 worksheet). For example, if the dat Mar 21, 2023 · See how to get unique values in Excel with formulas. It is going to be a dynamic Unique function. The Compare Two Lists tool above does it instantly. You might want to rid your data of duplicates to create summaries, populate drop-down lists, or remove duplicates that found their way into your spreadsheet. xlsx‎ (9. Aug 13, 2024 · To extract unique values from more than one range at the same time, you can use the UNIQUE function with the VSTACK function. Re: Merge two columns/lists into new single column/list removing duplicates (formula) Thanks for both solutions. The picture below shows the unique list in column F. In other words, you'll only see each value once in the result, even if it appears multiple times in the source. Mar 10, 2021 · I have made a excel document containing information on the last 2 years of sports. What have you tried? Aug 29, 2024 · How to Find Unique Values in Excel (UNIQUE Function) Not that it was impossible to find and sort out unique values in Excel – but yes, it was a task if not a hell of a task 🤕 Especially if it was to be done through functions. Aug 3, 2024 · Here, we present 10 methods to Extract Unique Items from a List in Excel. Jul 31, 2024 · You can easily find unique values in Excel and remove duplicates if you study this article on how to get unique values in Excel. Unearth Excel secrets to managing unique values in columns. Formula in cell F3: How to check on the status of an application How to view job applications How to create a login. In Excel, to remove duplicates from multiple lists or columns, we first need to get all the data in one column manually. Here’s my (amateur) way of handling this: Merge lists onto same excel sheet, conditional formatting to highlight duplicate values, values not highlighted by formatting rule is unique. Aug 23, 2020 · There are many scenarios you may come across while working in Excel where you only desire unique values in a list. Learn how to quickly get a distinct list by using Advanced Filter, and how to extract unique rows in Excel. Highlight Duplicate Values Using Conditional Formatting. Oct 9, 2012 · The above formula is working fine for each of the 5 worksheets, but I need to develope a unique distinct list from each of the 5 worksheets onto a sixth worksheet (essentially I need a tally sheet of each ID number from the 5 sheets). Get the sample file to practice. To highlight unique or duplicate values, from the Home tab, use the Conditional Formatting command in the Style group. Syntax: =UNIQUE(array, [by_col], [occurs_once]) array is the range or array you want the unique values Compare two Lists in Excel to find unique values | Filter and CountIf functions #shorts #excelTutorialspoint, a leading ed-tech platform, offers Simply Easy Aug 16, 2021 · How to use the UNIQUE () function in Excel If you’re using Microsoft 365 or one of the 2019 standalone versions of Excel, you can quickly create a dynamic list using the UNIQUE () function. Apr 10, 2025 · Learn how to extract unique values in Excel based on specific criteria using array formulas or the Kutools for Excel feature with step-by-step guidance. Nov 25, 2024 · To extract unique values from multiple ranges, you can apply a formula that uses the UNIQUE function with the VSTACK function. UNIQUE can return a unique or distinct list from one column or from multiple columns. Simplify your data analysis tasks. Press Enter to get the result. UNIQUE is not case-sensitive and works with text, numbers, dates, and other data types. This step-by-step tutorial covers formulas like UNIQUE and VSTACK for Excel 365 users, Apr 12, 2023 · See how to get unique values in Excel with the UNIQUE function and dynamic arrays. If you have multiple records, simply use the unique function. Note: this dynamic array function, entered into cell F1, fills multiple cells. Jun 16, 2024 · 9 suitable ways on how to create a unique list in Excel based on different criteria. 7K Dislike 70 Jul 23, 2025 · Comparing two lists in Excel is a powerful way to identify matches, differences, or duplicates within your data. You can also create a unique list from two or more columns using the pivot table tool. UNIQUE is in a class of functions called dynamic arrays. Extract the Unique List based on SAP Codes and Header from two tabs Dear Experts, Please find here attached Sample workbook. Filter for unique values in large or small datasets to clean up your worksheets and workbooks. It’s really as easy as one, two, and done! See It In Action Check out the video below to see how it’s done. We have three columns, but all with the same type of data. i. In the example shown, the formula in D5, copied down, is: @AndyPark365 Easily compare two Excel lists for duplicates or unique values 6. In the example shown, the formula in cell H5 is: =UNIQUE(VSTACK(range1,range2,range3)) Where range1 (C5:C16), range2 (D5:D15), and range3 (F5:F13) are named ranges. How to filter unique values from a list [Formula] Highlight unique values [Conditional Formatting] Useful tips Remove errors, Excel version 2007 and later Remove errors, Excel version 2003 and earlier How to ignore blank cells What you will learn in this article What is possible with formulas? What is the easiest way to filter unique distinct In this video I will show you how to create all possible combinations from two or more lists in Excel. Although I am able to create a f May 16, 2024 · To extract only unique values from a list or column, you can use an array formula based on INDEX and MATCH, and COUNTIF. Jul 10, 2025 · In daily Excel tasks, it's common to compare two lists of data—such as student name columns of different lengths—to identify which values appear in both lists (duplicates) or only in one (unique). Once we have identified common values, we can extract a list of common values from either list. Unique function easily works wit Mar 17, 2022 · I am working on a payroll sheet, and I need to extract Unique Employee ID from multiple sheets in the workbook and place them in the same workbook in another sheet. Apr 21, 2025 · Identify common values in the two lists Filter one of the lists to extract common values The tricky part of the formula is identifying common values. Learn syntax, examples, and advanced techniques for data deduplication. 9K subscribers Subscribe Jan 6, 2022 · Re: Extract a Unique List from Multiple Sheets There are instructions at the top of the page explaining how to attach your sample workbook. Jun 16, 2024 · Learn in Excel to get unique values from range using Advanced Filter, INDEX & MATCH, LOOKUP & COUNTIF, UNIQUE, VBA, Remove Duplicates. Download our practice workbook and exercise! May 9, 2022 · Extracting Unique Values From Two Lists Hi All, Attached is a simple workbook illustrating the problem I'm trying to solve. Aug 31, 2021 · Anonymous Aug 31, 2021, 8:59 PM Re: unique list from two tables The free "Professional Compare" workbook can list unique items from multiple selections. Aug 12, 2024 · A wholesome article on how to extract unique values based on criteria in excel with 2 effective methods. Trying my best to explain, please have a look at the attached Excel example. Re: Displaying unique data entries when comparing two lists in Excel! morning I think we'd need to see an example of the file you're working with, edit your original post, go advanced and Manage Attachments Register To Reply 03-17-2014, 09:46 PM #3 How to find and remove duplicates in Excel. Excel dynamically spills the results into the cells below. How to count the number of unique values in a range that contains duplicate values in Excel. Easily compare two Excel lists for duplicates or unique values Andy Park 48. Precaution: The UNIQUE function is only available in Office 365. Jul 14, 2025 · Learn how to extract unique values from multiple columns in Excel using formulas, Pivot Tables, VBA code, or Kutools AI Aide. To simplify it, I took an example of a class, with it's pupils and their parents names. 7. Let us have a look at this new data set. Luckily, Microsoft Excel offers many ways to accomplish this, and depending on your particular situation, you may use Aug 13, 2024 · This article explains how to compare two lists in Excel for matches using equal operator, conditional formatting, VLOOKUP, MATCH functions. Doing this manually can be error-prone and time-consuming, especially with large datasets. The application of VBA Macro is also explained. Extract Unique Values from Multiple Columns with Array Formula. Feb 3, 2025 · Question: I have two ranges or lists (List1 and List2) from where I would like to extract a unique distinct list? Merge two lists without duplicates, in other words. Each of the methods are described step by step. 9 KB, 9 views) Download Register To Reply 04-03-2024 Meaning, when data changes, it updates automatically to give you unique values. To use a non-array formula, you have to combine IFERROR, LOOKUP, and COUNTIF functions. That is, A2:G14 and I2:O14. Aug 29, 2024 · Surprisingly, there are multiple other tools and functions that you can use to compare two lists in Excel 2️⃣ Want to learn these? Grab your free practice workbook for this guide here now and read this tutorial till the end to unveil multiple ways to cross reference two lists in Excel. Extracting values Aug 26, 2022 · To extract a list of unique values from a set of data, while applying one or more logical criteria, you can use the UNIQUE function together with the FILTER function. This includes multiple tables across multiple sheets named 'Running', 'Cycling', 'Power', etc. In this tutorial, learn how to use formulas to get unique items from a list in Excel. . the two lists have mostly the same info but list 1 contains some info not in list 2 and visa versa. We often need to compare lists in Excel, and find out which items are in one but not the other. The unique list is placed in the first blank area to the right of the data. To find unique values from multiple columns in Excel, you can use a combination of functions like UNIQUE, TRANSPOSE, and FLATTEN (available in Excel 365) or use Power Query (available in Excel 2016 onwards). Learn how to find unique values in Excel with multiple criteria using the UNIQUE function. Jun 21, 2024 · Learn easy tips and tricks to compare two lists and return differences in Excel. 8. So if a phone number is in both lists it will only show… Jun 18, 2024 · To demonstrate the different methods to obtain unique values from columns in multiple sheets, we will use 3 Excel sheets as a dataset. When using tables, use the UNIQUE function in Excel 365/2021 to extract unique list items. I do have two different working tabs which are Forecast and Acutal and in both the tabs there is a list of SAP Codes and Categories under Header. Using UNIQUE Function. Wow! This behavior in Excel 365/2021 is called spilling. Feb 3, 2025 · Extract unique distinct values from a multi-column cell range - earlier Excel versions Question: I have cell values spanning over several columns and I want to create a unique list from that range. Oct 17, 2025 · Microsoft Excel's UNIQUE function does exactly as its name suggests—it extracts unique values from an array. Below is a walkthrough of the tool, the options, and how to do the same thing in Excel. The result is a dynamic array that automatically updates when source data changes. In the example shown, the formula in D5 is: =UNIQUE(FILTER(B5:B16,C5:C16=E4)) which returns the 5 unique values in group A, as seen in E5:E9. Each has phone numbers. Conclusions Now you know how to compare two lists in Excel. Mar 21, 2023 · The tutorial demonstrates the most efficient ways to find, filter, select and highlight distinct and unique values in Excel. While powerful, COUNTIF has a unique syntax that splits logical conditions into two parts, making it different from many other Excel functions. e i highlighted only the cells that has values. Sep 27, 2023 · Is there a single formula way to get a list of unique values. In this video, demonstrate how to combine or append multiple columns into one column using formulas and the Unique function. Apr 16, 2024 · Learn to merge lists seamlessly using an excel formula to combine two lists without duplicates for efficient data management. Unlock tricks with dynamic arrays, advanced filters, and handling duplicates efficiently. Introduction In this guide you'll learn how to combine two lists in Excel and produce a single, clean list with duplicates removed, a common task that ensures reliable outputs when merging contact lists, inventories, or reporting datasets; this matters because proper data consolidation improves reporting accuracy and p Apr 15, 2022 · Create a list of unique names, dates, or other data in your spreadsheet with a simple function. In this tutorial, we explore how to use the UNIQUE function in Excel 365 to extract multiple unique columns from a dataset. It can be consolidated with an macro. Mar 9, 2024 · sanjaydev Provided you are using Excel365 you could use this: =UNIQUE(VSTACK(NewList,OldList),,TRUE) NewList and OldList refer to the relevant ranges of the two lists. Re: comparing two sheets and Formatting unique names on them Thank you my friend, i highlighted A2 to the end i. The "Professional Compare" workbook looks like We would like to show you a description here but the site won’t allow us. Feb 20, 2025 · Learn how to combine two lists in Excel and remove duplicates easily. Jun 9, 2025 · Master Excel's UNIQUE function to extract unique values from ranges and arrays. Formula examples to extract unique values from a range, based on multiple criteria, sort the results alphabetically, and more. Learn about the UNIQUE function, which returns a list of unique values in a list or range. Paste both lists, click Compare, and you get a clear breakdown of what matches, what's unique to List 1, and what's unique to List 2. It allows you to obtain both strictly unique values and distinct values (where duplicates are removed). Learn how to extract unique values from two lists or columns in Excel. Dec 24, 2025 · Select List 1 Choose List 2 Select destination for unique entries from List 1 Choose cell range for unique values from List 2 Select a destination for matching values from both lists Comparing lists in Excel using VBA Find above a neat comparison of two lists using a VBA macro. Let's look at how it works, how to use it, and how to avoid certain pitfalls. The first sheet contains the Top Sales Person in April list. At the moment, I'm using an array formula from https://www. Jul 30, 2024 · Combine lists without duplicates with Kutools for Excel With installed Kutools for Excel, you can use the Select duplicates & unique cells function to solve the problem that combine two list without duplicates. gov account How to use login. Extract a Unique Distinct List from Two or More Columns Using Pivot Table. That might sound like the same thing but there's a subtle difference which I explain below. See image below. Learn how to apply it to both rows and columns. We would like to show you a description here but the site won’t allow us. Extract Unique Values from Excel Column Using a Formula Without Array. For each sport I li Nov 3, 2016 · What about combining the two lists into one column, then using the "Remove Duplicates" function that's built into Excel? Then, if the formatting goes away, just use a conditional fomatting to say "if x is in List 1, color Green, else Orange". I have attached a sample sheet. =IFERROR(INDEX(A$2:A$16,MATCH(1, Jan 24, 2023 · UNIQUE function in Excel (How to + 6 Examples) I can’t even begin to count the number of times I have created a unique list in Excel. For example, combine List 1 and Feb 3, 2025 · Here is a solution to create a unique list from two columns without using VBA. [SOLVED] List unique values from multiple dynamic lists By andredl in forum Excel Formulas & Functions Replies: 8 Last Post: 10-22-2014, 02:37 AM Apr 4, 2023 · Excel will then highlight the unique values, and your quest to see the differences between the two lists is complete. This tutorial describes multiple ways to extract unique values from a column in Excel. May 11, 2022 · Determine the unique values from 2 different lists of data using remove duplicates, Power Query and VSTACK. Jun 8, 2025 · COUNTIF is one of Excel's most widely used functions, and you will find it in all kinds of spreadsheets that calculate conditional counts based on dates, text, or numbers. In this video, I show 6 methods for doing that including cond Jul 31, 2024 · This video is going to demonstrate how to extract the unique values or lists from a table or dataset in Excel by using a formula. and it coloured 2 names which are on the other list. This tutorial will show you how to remove duplicates based on a specific criteria. Includes key functions like VLOOKUP, XLOOKUP, INDEX & MATCH, FILTER, RANK, ROUND, AVERAGE, COUNTIFS, SUMIFS, UNIQUE, SORT, TEXTSPLIT, and more. I am hoping someone can help me with this, I have tried several different variations of this formula with no luck. Follow our easy guide to efficiently consolidate and manage your data. I want a list that contains a combination of the two list unique values. You'll learn how to easily set up your data in a table and apply the Apr 3, 2024 · Combine Two UNIQUE Lists into one Morning All, I have two UNIQUE Lists, that have different headers that i need to combine into one list, which is once again UNIQUE. This article introduces multiple efficient methods to quickly compare two columns and find or highlight Jul 25, 2025 · Discover how to use Excel UNIQUE() to instantly remove duplicates and extract distinct values from any list. Essentially I have two lists of data with possible duplicate values. Jan 29, 2026 · The Excel UNIQUE function extracts unique values from a range or array. volf ftkpcb xfclz sfiebf fwwtw czqc wnam gfgb obmoxmce xmcj