Excel unique combination of multiple columns. In the examples above, the arrays for our UNIQUE formulas are range D2:D11, and D2:D17 respectively. I was recently working on a project where I had multiple lists in Excel and I had to create all the possible combinations using these lists. Feb 3, 2025 · 6. By manipulating the formula and combining it with other functions, you can extract unique values from multiple rows/columns, concatenate unique values, or extract unique values based on a single/multiple criteria. Oct 19, 2016 · Hi, On my worksheet Data, I have columns A, B, and C with hundreds of rows of data. Presently, i am populating a list, but its showing all values and also, i cannot use that for point 2 below. Jun 23, 2024 · How to find unique values from multiple columns in Excel. 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. 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. Option 2: Combine or append data instead of summarizing it If you need to merge or stack rows from multiple sheets, not calculate totals, you’ll need a different approach. Nov 28, 2025 · In this comprehensive guide, we’ll explore five easy and effective ways to find unique values across multiple columns in Excel. Easy Formula to Create All Possible Combination. Jun 16, 2024 · The Excel UNIQUE function in Excel is useful for extracting unique values from a range of cells. Next you need to sort by the size column: Go to Data → Sort. In this article, we will explore how to create a formula that checks for unique combinations of values across multiple columns, specifically focusing on identifying instances where there are more than two different values in a specified column based on Apr 14, 2020 · Add a Custom Column to and name it List1. The result is a list of unique pairs of color and region, sorted by color. Jan 15, 2015 · I would like to create a new table with those 3 Columns and 1000 rows, where each row would have a unique combination of those 3 elements (so 2 rows will never have the same 3 elements). For example if Jul 21, 2024 · Excel has a great impact on our daily life. There's many different combinations between the three columns in this list, and I need to have those unique combinations listed on a different worksheet without the duplicate combinations. In this guide, I’ll show you exactly how to identify duplicates based on multiple columns, highlight or remove them as needed, and even provide tips to handle future data changes seamlessly. Jun 9, 2020 · To get unique pairings: Go to Data → Remove Duplicates. For example: Column A has Cat, Dog, Mouse in the first 3 cells Column B has White, Orange, Black Jan 19, 2012 · On a worksheet called Constructors Data, I have columns A and B with hundreds of rows of data. Learn how to create or generate all possible unique combination from a single column with Let, Textbefore, Textafter, Filter , unique function . 2 simple Power Query solutions. If a label appears in one sheet but not another, Excel still includes it. Nov 15, 2018 · The other day, I was about to create a unique combination of two non-adjacent columns in Excel. Ensure both columns CN and CS are selected. Caveats: It requires Excel 2019 or Excel 365 It does not take columns as inputs - it takes an array, so you must size the input array to fit the columns that you want. Wow! This behavior in Excel 365/2021 is called spilling. Greetings, I would appreciate some help processing a large spreadsheet. (pls see notes below) Hi all, I’m having some trouble with a workbook I’m creating and can’t seem to find an answer. Let's say you want to extract the first and last names of all prize winners from this table, named T_Prizes, but only once if they have won multiple prizes. Follow this guide to create a list of unique combinations for analysis and reporting. Today we will discuss how to generate all possible combinations of a set of numbers in Excel. This article describes the user with 3 possible ways to generate the possible combination of 3 or more available columns in Excel. Then we bring all the listed row numbers to the top by a combination of the SMALL and ROW functions. For example: Column A has Cat, Dog, Mouse in the first 3 cells Column B has White, Orange, Black Jul 2, 2024 · How to apply all combinations of 3 columns in Excel is done by using the power query feature, and formula as well as by running a VBA code. For instance, if you have two or more columns of values, manually listing every combination can be tedious and error-prone, especially as the number of values grows. Follow our step-by-step guide for accurate data analysis. The first example is based on using the user-defined formula, to generate the possible combination. Try different combinations! It takes the values in a specified range and puts them in a single column. 8. chatGPT, BARD, GPT4 etc) to create forum answers is not permitted. Mar 8, 2023 · Unique combinations from two columns by DaveIsWorking » 21 Apr 2023, 14:31 I have a Table of sales information for multiple customers (Column A) and multiple products (Column B). Dec 4, 2024 · Learn how to use VLOOKUP with multiple columns in Excel effectively. It extracts unique values from a range or array, removing duplicates. Try different combinations! Nov 13, 2024 · Generate all possible combinations of data from one column in Excel. However, we can write a formula to work around that. In the field Column select CS. Getting a unique value from a single list is straightforward, and can be accomplished in Excel using the UNIQUE () function in Excel. The second example is based on the method to use the VBA code, to generate the required combination. What I’m looking to do is create a new table in C:D that shows all possible combinations of the values from A and B. From here, you have a list of unique combinations of conduit and size. Enter the formula =List1. You'll learn basic syntax, practical applications, and advanced techniques to master this essential Excel function. Jul 14, 2025 · Learn how to extract unique values from multiple columns in Excel using formulas, Pivot Tables, VBA code, or Kutools AI Aide. Easier to show a screen shot of an example. You need to have a mathematical operation in the pivot-table "Values How to VLOOKUP with Two Columns Advanced technique for using VLOOKUP with multiple criteria by combining two or more columns. 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. 7 KB, 11 views) Download Register To Reply 04-22-2017,05:38 AM Mar 13, 2026 · To count unique values across multiple columns, you can use a combination of the UNIQUE function and COUNTA function: =COUNTA (UNIQUE (TRANSPOSE (A2:C100))) Note: The TRANSPOSE function is necessary here to handle the multi-column array correctly. 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. 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. 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. May 31, 2023 · The following example shows how to use this formula in practice. Really appreciate any help!!! 3 days ago · Note: in Legacy Excel, this is an array formula and needs to be entered with Control + Shift + Enter. Re: Unique constraint on combination of list value and text Attached the sample. I will teach you two Solution. In this article, we will explore how to create a formula that checks for unique combinations of values across multiple columns, specifically focusing on identifying instances where there are more than two different values in a specified column based on Make a header into your spreadsheet: A1 = color1, B1 = color2, C1 = combination 1- Type on C2 =A2&"-"&B2 drag the formula down on column C until the last row in which there are data on columns A and B. The first sheet contains the Top Sales Person in April list. Jan 25, 2016 · The row number is returned instead of the value so that it can be used in the next two columns. Learn how to genrate all possible combination and Permutation with a very easy formula using Excel 365 for single column. To select multiple columns: use Shift+Click or Ctrl+Click on additional column headers. Just for clarification, the order of the Columns has to be the same (A, B, C) and of course elements of one column always stay in that column. On the Home tab, select Remove Rows > Remove Duplicates. I will show you how to accomplish this in this tutorial using Power Query, formulas, and VBA. Jan 19, 2012 · On a worksheet called Constructors Data, I have columns A and B with hundreds of rows of data. more Creating Unique Columns from Multiple Data Sources in Excel Written By: Ada Codewell – AI Specialist & Software Engineer at Gray Technical Imagine you have data scattered across multiple columns in an Excel sheet, and your goal is to create a unique column that combines specific values from these different sources. Expand out the new List1 column and then Close & Load the query to a table. Copy An array can be thought of as a row or column of values, or a combination of rows and columns of values. And finally, we just need to return the INDEX of the Combined column corresponding to the row of the Sorted column. Aug 4, 2024 · 4 Different Examples to Count Unique Values with Criteria by COUNTIFS in Excel. In the field Order select A to Z. This guide covers different techniques, examples & tips to make your data lookup efficient. This video explains about UNIQUE function available in MS Office 365 and how to use the formula to get the unique list and unique combination from the given data table. Each method is suitable for different scenarios, levels of complexity, and Excel versions. In newer versions of Excel that support dynamic array formulas, this formula will work seamlessly. 7. Something like Jul 18, 2025 · Learn 8 easy ways to count unique values in Excel with multiple criteria using formulas, PivotTables, filters, or VBA—works in all versions. Jan 24, 2023 · This post shows how to use and master the UNIQUE function in Excel. Formulas to count unique entries in a column, with multiple criteria, ignoring blanks, and more. Apr 4, 2025 · The UNIQUE function is a powerful dynamic array function in Excel. May 9, 2023 · 0 I have a data set which I would like to reformat and create all possible combinations from the different data entries in a single column, which are linked together by another column which groups them. g. To highlight unique or duplicate values, from the Home tab, use the Conditional Formatting command in the Style group. Download the Excel file and create this drop-down list. The following formula works in all versions, however, I recommend Excel 365 users to go with the much smaller and easier formula in section 2. . Since this is a multi-year table, a lot of possible combinations are duplicated or more. The methods include formulas as well as VBA code. Anyone have a formula 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. Unique function easily works with single rows or columns, but with Help Center Community Gemini in Docs Editors Google Docs Editors Privacy Policy Terms of Service Community Policy Community Overview This help content & information Aug 15, 2012 · Count the quantity of unique combinations of multiple columns in Excel Ask Question Asked 13 years, 7 months ago Modified 10 years, 5 months ago Excel is a powerful tool for data analysis, and one of its strengths lies in its ability to handle complex calculations through formulas. This tutorial provides a comprehensive guide to using UNIQUE with detailed examples. Power Query will keep one row for each unique combination of the selected column values and remove the rest. Example: Find Unique Values in Non-Adjacent Columns in Excel Suppose we have the following dataset that contains information about basketball players on various teams: Suppose we would like to find the unique combinations of teams and positions. Excel IF function with multiple conditions (OR logic) To do one thing if any condition is met, otherwise do something else, use this combination of the IF and OR functions: Re: List all combinations of multiple variants Do you mean unique combinations in the sense that if all 9 cells contained 2, the only unique combination would be {2,2,2,2,2,2,2,2,2}? • Can DISTINCT be used on multiple columns? • Does DISTINCT remove NULL values? 📊 Understanding how to retrieve clean and unique data is a key step toward becoming a strong Data Analyst. 2- Go to "Insert" --> "PivotTable" Drag "combination" into the "Row Labels", and Drag "combinations" into the "Values" label. Create separate queries for each column to be included in the combinations. Was this page helpful? Forum Microsoft Office Application Help - Excel Help forum Excel Formulas & Functions [SOLVED] Create unique array from columns with multiple repeats The use of AI tools (e. First Concept : TextJoin with TextSplit (small data )Second Concept: Reduce with textSplit (Faster and For L Jun 9, 2021 · Here is an extensible formula that can generate all combinations from an array input of up to five columns. Formula examples to extract unique values from a range, based on multiple criteria, sort the results alphabetically, and more. A new row or column is created in the result. In MS Excel, I could achieve both above goals by using a combination of XIRR, Choose and Filter functions (as per formulae in columns J & M respectively) of the attached sample file with hypothetical data. Jun 16, 2024 · 9 suitable ways on how to create a unique list in Excel based on different criteria. Unique records are those where the combination is unique In the Incident worksheet I need to put formulas that will pull: How to find and remove duplicates in Excel. Nov 19, 2014 · Hi Folks, I need to extract unique value combinations across multiple columns in a table. May 11, 2022 · There are many times where you will need to acquire a unique value from not just one list of values, but from two separate lists. Below is the screenshot. once you will learn these formula you can easily Jan 27, 2022 · This tutorial explains how to find unique values from multiple columns in Excel, including an example. I usually do this with Remove Duplicates or with Advanced Filter, but I thought I would try to do it with the new UNIQUE function coming to Office 365 in 2019. Learn how to find unique values in Excel with multiple criteria using the UNIQUE function. Unlock Excel's potential for endless number combinations! Tips on formulas and VBA to enhance your combination skills. How to lookup the values in the excel and display in a multi-column textbox ONLY with the unique combinations. In Excel, you may sometimes want to generate all possible combinations from two lists, for instance, to display all product variations. Dear all excel experts,* I have another question about counting duplicated cells in multiple columns and here I attached the file for reference. This formula will essentially create an array of the non-adjacent columns that you want to reference in the UNIQUE function. Note: this dynamic array function, entered into cell F1, fills multiple cells. Each of these tables contain fields you can combine in a single PivotTable to slice your data in multiple ways. Then you run the count: Go to Mar 16, 2026 · By removing duplicates based on the ‘Name’ and ‘Age’ columns, you’ll end up with a dataset that only includes unique combinations of these columns. Download our practice workbook and exercise! Mar 14, 2023 · See how to quickly count unique values in Excel with dynamic array functions. Another column also differentiates the data column. May 6, 2019 · To work with unique entries, either use the standard Excel tool Data > Remove Duplicates, or in Excel Power Query Editor, right click the data column header and click Remove Duplicates. xls. Jan 12, 2023 · This post explores how to list all possible combinations from multiple lists in Excel (known as a cartesian join). Each of them contains the Top Sales Person list for a month. What's different about this PivotTable? Notice how the Field List on the right shows not just one but a collection of tables. 1 day ago · Select the column (or columns) that define what counts as a duplicate by clicking their headers. Nov 6, 2025 · Learn how to generate all combinations of data from 3 or multiple columns in Excel using formulas, VBA code, or the powerful Kutools for Excel feature. Is there a way using VBA to return the maximum value from separate columns that share a unique identifier based on two other columns? For example, for every unique combination of identifiers from columns A and B, I want to create a new worksheet with a single row for each unique combination, and the maximum values from the Retrieve Unique values from 2 columns Hello Team, I want a formula to retrieve unique values from combination of 2 columns. 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). No manual formatting or data preparation is necessary. In this video, demonstrate how to combine or append multiple columns into one column using formulas and the Unique function. So, while entering the data, user should not able to enter the same combination of Employee and Project. The table will have all the combinations of items from both lists and we saved on making a custom column in List1 and avoided using a merge query altogether! Jun 18, 2024 · In this article, we have discussed 4 methods to get all combinations of 2 columns in Excel. Click OK. In this video I will show you how to create all possible combinations from two or more lists in Excel. xlsx‎ (8. It extracts unique distinct values from all three cell ranges B3:B11, D3:D6, and F3:F8 Aug 7, 2024 · Learn how to count unique row combinations across multiple columns in Excel. The ‘Remove Duplicates’ feature in Power Query can be a powerful tool for data analysis in Excel. Anyone Feb 16, 2021 · The answer to Irene's question is: on it's own, the UNIQUE function can only find unique combinations from adjacent columns. Here we are trying to answer "How many distinct combinations in each product based on Date and Campaign columns?". The UNIQUE () function can take a single column, and extract unique values from the column. I have a list of values in column A and a second list of values in column B. In this video you'll learn how to use it in combination with the UNIQUE function to get a distinct list from a range. Jan 21, 2004 · I have a list consisting of two columns which may contain duplicate data, but I need to pull out all unique entries across the two columns. Multiple Criteria Lookup Demo This shows how to lookup using TWO criteria (Name + Department) by combining them with a helper column. In this video i am going to explain how to genrate all possible combination with a very easy formula using Excel 365 for single column. Aug 26, 2022 · To extract unique values from non-adjacent columns in a set of data, you can use the UNIQUE function, with help from the FILTER function to remove unwanted columns. However; when there are multiple columns How to VLOOKUP with Two Columns Advanced technique for using VLOOKUP with multiple criteria by combining two or more columns. Apr 12, 2023 · See how to get unique values in Excel with the UNIQUE function and dynamic arrays. Unlike Create all possible combinations of two lists of values in Excel Apr 17, 2025 · When working with data in Excel, you may encounter situations where you need to generate all possible combinations from multiple lists. The UNIQUE function will return an array, which will spill if it's the final result of a formula. Just watch an Dec 9, 2022 · I have two columns, A on one sheet, B on another. There's roughly 30 different combinations between the two columns in this list, and I need to have those unique combinations listed on a different worksheet without the duplicate combinations. I want to return a third column which is the unique values that appear in these columns. In the example shown, the formula in cell H5 is: Where range1 (C5:C16), range2 (D5:D15), and range3 (F5:F13) are named ranges. Select OK to generate the consolidated report. Learn how to generate or create all possible combinations from two columns, pairs, or lists using Excel formulas. Jul 24, 2014 · When you have your columns in letters, you can use the =CONCATENATE to get the combinations, if your data of letters is is Column A, B, C, D: =CONCATENATE (A1,B1,C1,D1) Finally, use the COUNTIF function to 'count' the unique combinations that exist in the range we have created with the CONCATENATE function. You can immediately build a PivotTable based on related tables as soon as you import the data. Nov 12, 2025 · Learn how to create a Crossjoin (all combinations) from two columns in Excel using formulas, Power Query, Pivot Table, or VBA — in just a few steps. I have done quite a bit of googling and research and it seems that this is no easy task. 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. Column AQ is a concatenation of D and F. Unlike Create all possible combinations of two lists of This video shows how you can create unique combinations from a set of columns using Power Query. If a user is believed to have used such tools to provide a forum answer, sanctions may be imposed. I found this whopper of a function to 'Extract unique distinct values (including first duplicates)' Does the UNIQUE function work on multiple columns in Excel? Absolutely it does and it's actually a very handy way of identifying unique combinations of data in a dataset. Use this spill range to create a magic drop-down list. To remove duplicate values, select Data > Data Tools > Remove Duplicates. Find unique values from multiple columns in Excel in just few seconds. Simplify your data analysis tasks. In the field Sort On select Values. Filter for unique values in large or small datasets to clean up your worksheets and workbooks. Download our practice workbook for free, and exercise! Hi all, I’m having some trouble with a workbook I’m creating and can’t seem to find an answer. In the example shown, the formula in G5 is: =SORT(UNIQUE(FILTER(data,{1,0,1}))) where data is the named range B5:D15. Anyone Excel is a powerful tool for data analysis, and one of its strengths lies in its ability to handle complex calculations through formulas. Jan 8, 2019 · End Sub which worked beautifully, for one column (BE is the Name1 column in the table above - Data is the sheet where the data is stored, Crew is the sheet where I want the unique values to go). UNIQUE is a power dynamic array function which you need to know. For more details and a sample workbook, see INDEX and MATCH with multiple criteria. Order # Part # Answer 1 p1 p1 1 p2 p3 2 p1 p4 3 p3 4 p2 5 p4 As you can see from the above screenshot, my requirement is to retrieve only p1, p3 and p4. Sep 29, 2023 · This tutorial explains how to list all possible combinations between multiple lists in Excel, including an example. This means that Excel will dynamically create the appropriate sized array range when you May 8, 2023 · This article describes the user with 3 possible ways to generate the possible combination of 3 or more available columns in Excel. Extract a unique distinct list from three columns The image above shows three different cell ranges populated with names, they are B3:B11, D3:D6, and F3:F8. But I couldn't for the life of me figure out how to make it take values from multiple columns (BE to BJ). When using tables, use the UNIQUE function in Excel 365/2021 to extract unique list items. First I'll show you how to do this using a simple trick Oct 17, 2025 · Extracting Unique Values From Two or More Columns or Rows The UNIQUE function in Excel can also be applied to two or more columns or rows simultaneously to return unique combinations. 1) I want to find out how many cells are appeared in these 3 columns ? Mar 22, 2023 · For more information, please see IF AND formula in Excel. For example there are 8 rows in Product A but 6 unique values as per combination of Date and Campaign. That is the constraint I am aiming to implement Attached Files Unique Combi List. . vcg guy igbghg ejk ejrlp jdcj eub kgxre dvz lwato