Extract a unique distinct list from two columns using excel 2007 array formula
Question: I have two ranges or lists (List1 and List2) from where I would like to extract an unique distinct list? Merge two list without duplicates, in other words.
Answer:
Excel 2007
Array formula in C2:
How to create an array formula
- Select cell C2
- Click in formula bar
- Paste array formula to formula bar
- Press and hold Ctrl+ Shift
- Press Enter
- Release all keys
How to copy array formula
- Copy cell C2
- Select cell range C3:C19
- Paste
Earlier versions of Excel
Formula in C2:
Note, the ranges don´t have to be adjacent.
Named ranges
List1 (A2:A20)
List2 (B2:B7)
What is named ranges?
How to customize the formula to your excel spreadsheet
Change named ranges. If your duplicates list starts at, for example, F3. Change C1:$C$1 in the above formulas to F2:$F$2.
Download excel *.xls file
how-to-extract-a-unique-list-from-two-columns-in-excel-2007.xlsx
(Excel 2007 Workbook *.xlsx)
how-to-extract-a-unique-distinct-list-from-two-columns-in-excel-2003.xls
(Excel 97-2003 Workbook *.xls)
Functions in this article:
MATCH(lookup_value;lookup_array; [match_type]
Returns the relative position of an item in an array that matches a specified value
INDEX(array,row_num,[column_num])
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range
COUNTIF(range,criteria)
Counts the number of cells within a range that meet the given condition
IFERROR(value;value_if_error) Returns value_if_error if expression is an error and the value of the expression itself otherwise
This blog article is one out of thirteen articles on the same subject "unique".
- How to extract a unique distinct list from a column in excel
- Extract a unique distinct list from two columns using excel 2007 array formula
- Extract a unique distinct list from three columns in excel
- Extract distinct unique sorted year and month list from a date series in excel
- Create a unique distinct list from a date range in excel
- Unique values from multiple columns using array formulas
- Extract a unique distinct list sorted from A-Z from range in excel
- Sort a range by occurence using array formula in excel
- Filter unique distinct values from two ranges combined in excel 2007
- Create a unique list and sort by occurrances from large to small
- Unique list to be created from a column where an adjacent column has text cell values
- Create unique list from column where an adjacent column meets criteria
- How to create a unique distinct list where other columns meet two criteria
Related posts:
Extract a list of duplicates from two columns combined using array formula in excel
Extract a unique distinct list from three columns in excel
Extract a list of duplicates from three columns combined using array formula in excel
Extract a list of duplicates from a column using array formula
Comparing two columns and sum unique values using array formula in excel
Recommended categories
Can you please post an Excel 2003 sample of this. The 2003 formula does not work.
Thanks
No problem Rav!
I have now attached an excel 2003 file to this blog post.
Hello - trying to use this formula on a spreadsheet (Excel 2007) very similar to the one you show in your screenshot. I'm getting no values returned, so tried to download your example file and instead of an .xlsx file it downloads a zip file that contains a bunch of .xml files. The 2003 example file downloads fine ...
Thanks for posting this!
OK, I manaaged to use the 2003 formula in my Excel 2007 and it works ... right down to cell C24. After that I get a "Value not available error". I see that your example is only 24 rows long, but I don't see anywhere in the formula that it specifies to stop at row 24. If I continue copy/pasting the formula in cells 25 and below, shouldn't it use relative formatting to adjust properly?
David,
I downloaded the .xlsx file and it works fine here.
I am not sure I understand but I´ll give it a try:
The relative reference should adjust properly. But if you add more values to any of the lists, the named ranges are not dynamic. You need to change the named ranges to include the new values.
Thank you for this guide! Really helpful!
Although is it possible to extract an unique distinct list from three columns?
Eduardo Ristow,
Extract a unique distinct list from three columns in excel
Hi Oscar
I can't get these formulas running on excel 2010
I have two columns (A and B) with server names and I need unique values in column C. And is it possible to get unique names from different sheets (column A in sheet one contains server names from one automatic export and column A in sheet two contains server names from another automatic export) and I would need unique values in sheet 3.
Best regards
hrvoje,
The attached file contains three sheets.
Sheet 3 extracts values from column A in sheet 1 and 2.
Sheet 1 contains a dynamic range: =OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A$2:$A$10000))
Sheet 2 contains a dynamic range: =OFFSET(Sheet2!$A$2,0,0,COUNTA(Sheet2!$A$2:$A$10000))
You may have to adjust cell references in both dynamic ranges. Remember, the formulas can´t process blank cells.
Download *.xslx file
how-to-extract-a-unique-list-from-two-columns-in-different-sheets-in-excel-2007-dynamic-ranges1.xlsx
Hi Oscar. Thank you very much for your fast response.
When I click on a link I get error "Sorry, but you are looking for something that isn't here."
And if I have 3 sheets with data then I just add another sheet and paste formula =OFFSET(Sheet3!$A$2,0,0,COUNTA(Sheet1!$A$2:$A$10000))?
Best regards,
Hrvoje
hrvoje,
thanks, it works now!
And if I have 3 sheets with data then I just add another sheet and paste formula =OFFSET(Sheet3!$A$2,0,0,COUNTA(Sheet1!$A$2:$A$10000))
Yes. You need a larger array formula, see my attached file to Eduardo Ristow.
I have two columns containing data as follows,
ISSUE TENOR
06-Sep-12 84
06-Sep-12 84
20-Sep-12 84
20-Sep-12 84
04-Oct-12 84
04-Oct-12 84
06-Sep-12 182
06-Sep-12 182
06-Sep-12 182
20-Sep-12 182
20-Sep-12 182
04-Oct-12 182
04-Oct-12 182
06-Sep-12 364
06-Sep-12 364
06-Sep-12 364
20-Sep-12 364
20-Sep-12 364
26-Jul-12 364
04-Oct-12 364
04-Oct-12 364
Formual should show the following result:
ISSUE TENOR
06-Sep-12 84
20-Sep-12 84
04-Oct-12 84
06-Sep-12 182
20-Sep-12 182
04-Oct-12 182
06-Sep-12 364
20-Sep-12 364
26-Jul-12 364
04-Oct-12 364
I want a unique list of ISSUE dates falling in all three tenors of 84, 182, and 364 days.
Please tell me the formula to reslove my query.
Your help shell be highly appricated.
Nadeem,
Muhammad Nadeem Bhatti,
Read this post:
Filter unique distinct row records in excel 2007
Thanks a lot sir. Surely, you are a great teacher.
(Oscar)
Nadeem,
Sir, Please tell me how to sort on date and tenor.
Look forward to your reply.
Regards,
Nadeem Bhatti.
Muhammad Nadeem Bhatti,
I think you have to copy the values returned from the array formula to a new sheet and then sort on date and tenor.
Perhaps this post is interesting:
Sort values in parallel (array formula)
Thanks Oscar. I've just used this and saved myself a whole load of manual work.
trumpet,
Thank you for commenting!
You are a Prince among men!
WOW you saved me ALL kinds of excel Hell.
I have been pulling my hair out, looking for this solution for years...
You are still a GOD.
But I am having a problem...
The unique list works (I think), but after that I am trying to use the list in a Vlookup and hitting a bug I cannot figure out...
Any chance I could email you a file to have a quick look?
Many thanks! (You have my email...)
Careyz,
You can upload the file here.
OK I uploaded a file for you.
Did you get it?
You can reply here or maybe better to my email.
Muhammad Nadeem Bhatti,
I think you have to copy the values returned from the array formula to a new sheet and then sort on date and tenor.
Perhaps this post is interesting:
Sort values in parallel (array formula)
Reply
Hi Again,
Thanks for your help as above.
Is there any possibility to sort the values in side the primary array formula. Basically I want to avoid copy paste and add another work sheet to save my time.
Look forward to your reply on this.
Thanks and Regards,
Muhammad Nadeem.
Muhammad Nadeem Bhatti,
Sorting multiple columns using array formulas is complicated.
I recommend creating a macro that automatically copy and paste your values.
Oscar,
Using two columns in tables - How do I remove the blanks within this solution?.
Thanks - Alex
Alex,
Array formula:
See this comment
Oscar,
This works like a charm..
Appreciate the help - Alex
I cannot get this formula to work at all. Not being really that familiar with more complex Excel functionality, what is the logic of the formula? (I am using Excel 2003.)
Maddy Eid,
What happens? #NAME? error?
Here is an explanation of the formula:
How to extract unique distinct values from a column
Hi Oscar,
#Name if I haven't set up names, #N/A if I set up names or replace the name by the column reference $A:$A... As the lists are dynamic, I'd prefer not to use names wherever possible.
Thanks,
Maddy
Maddy Eid,
I am not sure why you get #N/A but I recommend converting the list to defined table. Tables are dynamic and easy to reference. I believe there is a similar feature in excel 2003: List
http://www.contextures.com/xlExcelTable01.html
A cell reference to the entire column A makes the array formula very slow.
Hi Oscar,
I'd better explain what I'm trying to achieve: we have a project that set up teams to deliver a specific short term service and we are trying to see the effect on the subsequent requirement for long term services or another episode of short term care after each short term service episode. However, people can have multiple episodes of each of the short and/or long term services, which means I need to end up with a flat file of all services sorted by client ID, then the service start date with the short term episode number and all service episode number. From that, I can create pivot tables detailing length of time it takes to start a long term service after the short term service.
So, I have 3 different data extracts:
* Historic Short Term (up to end of the last financial year) refreshed 2-3 times per year
* Current Short Term (from the start of the current financial year) refreshed every 6 weeks or so
* Long Term refreshed when I update the Excel
However, the Short term extracts overlap by about 3 months each way (no, I didn't set these up and I have no control over what is extracted - it's something horrible involving Oracle tables which IT handles), so I only take episodes terminated up to the end of the last financial year from the Historic data, and all episodes from the Current data (and I'll start missing historic terminations until the Historic data is updated). These I need to combine into a single file, sorted by client ID and service start date from which I create the short term episode number by date.
Once I have done that, I then need to slot in the long term services, but only for those clients receiving a short term service (about 12,000 clients get long term services and about 3,000 have had a short term service). Again, I need to sort the file by client ID and service start date from which I create the any service episode number by date; but the short term service episode numbers must remain static as I am only interested in what is the next service following any short term termination and how long it is until the start of that service.
From that, I create the various pivot tables. Basically, it can be broken into 2 problems: combining the short term data into a single file, then appending the long term data where a client has had short term services. If I have to end up creating an enormous file of all services regardless of whether the client has had a short term service or not, then I will, but I think my boss wants me to do it all in Excel and not use Business Objects (which is my preferred solution).
Maddy Eid,
Can you provide some fake example data and the desired outcome?
Dear Oscar,
I got some data to be sorted.
I have data in below formate
Column -1 Column - 2
ravi@gmail.com 1
ravi@gmail.com 2
ravi@gmail.com 3
ravi@gmail.com 4
ravi@gmail.com 5
sam@gmail.com 1
sam@gmail.com 2
sam@gmail.com 3
sam@gmail.com 4
sam@gmail.com 5
I want this data in below format
ravi@itadka.com 1 2 3 4 5
sam@itadka.com 1 2 3 4 5
Can you please help me to sort this?
Thanks in advance Oscar, Ravi
Ravi,
Array formula in cell B14:
=INDEX($B$3:$B$12, MATCH(0, COUNTIF($B$13:B13, $B$3:$B$12), 0))
Array formula in cell C14:
=INDEX($C$3:$C$12, SMALL(IF(COUNTIF($B14,$B$3:$B$12), MATCH(ROW($C$3:$C$12), ROW($C$3:$C$12)), ""), COLUMN(A1)))
Download excel *.xlsx file
Ravi.xlsx
Hi Oscar,
First of all, thank you for all the great formulas you have posted on this website, it has helped me alot.
I am using the formula above to extract a unique list from two columns. It is working great, except from one small detail which I can't figure out. When I add two or more dates, all are shown in the result column. But when I only have one value in one column, the result column is empty. Do you know what can cause this?
I have uploaded a test file here if you have the time to investigate it:
http://sprend.com/download.htm?C=e30a0e307afb448389a240d69fc7d9ce
Thanks in advance.
Best Regards,
Viktor
Viktor,
It seems that MATCH(0,COUNTIF($C$2:C2,OFFSET($A$1,2,0,COUNTA($A:$A)-2,1))+0,0) returns #N/A.
I converted your dynamic cell ranges to excel defined tables but that didn´t solve it.
Hi Oscar,
Thank you for your reply. So you don't have any idea how it can be solved? Do you have any alternate solution that I can try?
Regards,
Viktor
Viktor,
I remember someone asking the same question and posted a solution. I can´t find the comment.
I have a data set that is a combination of date and a unique id from 2 sources in 2 separate columns , and each day the data gets bigger up to a maximum of 3000 lines how would would i use this formula to list the unique value from these lists, compensating for the blacks as the next they will have data in them in EXCEL 2010?
Many Thanks
James Jones
Meant Blank cells not blacks.
RJJ
RJJ,
See this post: Filter unique distinct row records in excel 2007
Oscar,
Another question relating to my prior 'table question'. I may have a couple of rows that are blank in my first column of data. The index column is then returning a blank in the indexed column. Is there anyway to remove this?.
Thanks,
Alex
Oscar,
To specify, the blank lines in row 2 and 3 after the header.
Thanks,
Alex
Alex,
My previous comment to you seems to be wrong.
New array formula:
=IFERROR(IFERROR(INDEX(Table1[Name], MATCH(0, COUNTIF($G$1:G1, Table1[Name])+(Table1[Name]=""), 0)), INDEX(Table2[Name], MATCH(0, COUNTIF($G$1:G1, Table2[Name])+(Table2[Name]=""), 0))), "")
Download excel *.xlsx file
how-to-extract-a-unique-list-from-two-columns-in-excel-2007-Alex.xlsx
Oscar,
I believe - that has it. I have been using this in conjuction with some automated reports out of system and blanks are an issue, as I limit the data..
Thanks for all the help.
Alex
[...] know that this problem could be solved via formula at all, but the client provided me a helpful link (thanks Oscar), which guided me to working [...]