E-Mail 'Working with unique values' To A Friend
Email a copy of 'Working with unique values' to a friend
Loading ... Loading ...37 Responses to “Working with unique values”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Hi,
I've been searching for articles to help me to identify unique data across multiple Excel columns, and yours is the closest I have found. However, it seems to deal with only two (2) columns at a time. Is it expandable to cover more than two, in particular where the columns are not adjacent?
What I have is a grid of results:
Cols A, B, C reference result set 1 (with the potentially duplicate data in Col C)
Cols D, E, F reference result set 2 (with the potentially duplicate data in Col F)
and so on, with potentially result sets. The pracical number of rows involved is likely to be no more than 500.
What I need to do is to count all the unique entries in Col C, Col D, Col as a combined set, such that if Col C has data "abc", and Col also has the same data, then the data is only counted once. (I don't actually need to generate a new list of the unique data, but if that is a required stage in the calculation it is not a problem.) Note that can be any value, but is likely to not exceed 500 columns.
Could you advise me if there is a specific, formulaic (not VBA) solution to the problem? If so, could you provide a clue to solving it?
Many Thanks,
Nigel Edwards.
Thanks for commenting!
Interesting question, I have to think about this question for a while...
I created an array formula that counts unique distinct values in three different ranges.
https://www.get-digital-help.com/count-unique-distinct-values-in-three-columns-combined-in-excel/
Hi Oscar,
Would be possible to share this formula again.
It looks like link is years old and was probably disabled.
Much appreciate.
hi, i'm curious on this solution.
Is there a simpler/shorter formula to extract non-duplicate entries?
output shall be BB and EE (non-duplicates on both columns).
I have updated this blog post. I think the formula is somewhat shorter and uses less named ranges.
xcellent!!! thanks!!
Hi,
Just found the site and wow! I've already fixed a few sloppy problems in some my work spreadsheets.
Sorry in advance if this is the wrong way to ask a question.
But this page is the closet I've found to what I am trying to do. I have a table with four columns, Date, Name, Level, and outcome. The range is from row 3 to row 1000.
What I need to be able to do is look at today's date. Determine the month and year and then look up all the values in the date column that match the month and year. I've been trying to get it to work with sumproduct but I can't wrap my head around it.
Then on a separate tab list all the unique events for that month.
So one the seperate tab it would show something like this:
May 2/2010 Bob Smith 3 Requires Attention
May 5/2010 Jim Smith 1 Out of Service
Hope you are able to help. Thanks in advance.
Dave,
see this post: https://www.get-digital-help.com/list-all-the-unique-events-for-a-month-in-excel-array-formula/
Thanks! Works like a treat
I tried this and it gives a divide by zero error. The reason is countif() gives a 0. What are you trying to do with the countif, seems like that using 2 ranges are arguments is not allowed.
Scott,
I think you might have blanks in your two lists?
Get the Excel file
Scott.xls
I am trying to filter dates (with days) using month and year as follows:
=SUMIFS('DATA INPUT'!$C$3:$C$5000,TEXT('DATA INPUT'!$B$3:$B$5000, "yyyy-mmm"),TEXT(E$1,"yyyy-mmm"),'DATA INPUT'!$D$3:$D$5000,"Pilsner",'DATA INPUT'!$E$3:$E$5000,"Beer pack")
- Column "C" is what I would like to sum
- Column "B" is the dates I am TRYING to filter using E1 (inputted month)
- Column "D" and "E" are standard text to be filtered
I have tried playing with the concepts above but to no avail, any ideas? I would like to avoid VBA, etc.
Chris,
Array formula in F1:
Your formula:
becomes
Entered as an array formula.
I may be a bimbo, but I can't figure this out. I have five rows that need to change, sorted by date from the first row.
here are my row names: due date, author, title, changes, date completed
I want the due dates to all be in order, changing the corresponding columns.
HELP a homegirl out! PLEASE!
oh and i have a MAC.
amber,
Is this what you had in mind?
Sort from left to right
How to find Min and Max numeric values in a range of cells that have duplicate numbers and blanks, but only want to find the Min and Max on the largest/top 100 non-duplicate values.
Marc,
read post:
Find max unique value from a range that have duplicate numbers and blanks
[...] Excel, Search/Lookup, Sort values, table on Nov.20, 2012. Email This article to a Friend Marc asks:How to find Min and Max numeric values in a range of cells that have duplicate numbers and blanks, [...]
Hi
I've gotten this to partially work. The problem is my data is split over 4+ columns. So Column A would be Columns S,AA,AI,etc and column B is Columns O,W,AE,etc.
This works:
Date (S2:S21)
Values (O2:O21)
But when I try to add other columns to the named ranges it doesn't like it. This doesn't work:
Data (S3:S42,AA3:AA24)
Values (O3:O42,W3:W24)
Thanks
Laura,
you are right, it does not work. I don´t have a solution for you.
This formula breaks if you change the size of the "tbl" range to only one or two columns of the same data in the "tbl" range.
How is this fixed so that I could use a range of any size in place of the "tbl" range?
tyler,
the formula works if you use two or more columns. I tried with two columns and it worked.
Use this formula if you are working with one column:
How to extract unique values from a column
Remember, the formulas above filter unique values. If you are looking for unique distinct values, see this post:
Extract a unique distinct list sorted from A-Z from range
Suppose if i want the unique list to be start from F3 means, then i have to change C9:$C$9 to F2:$F$2....but i want the list to be start from F1 then what should i write in the formula...instead of C9:$C$9 can i write F0:$F$0. Note: I dont want any heading. I want the list to be start from F1.Please provide the solution
Shariff,
Suppose if i want the unique list to be start from F3 means, then i have to change C9:$C$9 to F2:$F$2...
Yes, correct!
but i want the list to be start from F1 then what should i write in the formula...instead of C9:$C$9 can i write F0:$F$0. Note: I dont want any heading. I want the list to be start from F1.Please provide the solution
Great question and I don´t know the answer. I don´t think you can.
[…] Excel udf: Filter unique distinct values (case sensitive) […]
error have a problem why does it give the answer "00/01/00"
I tried your formula, it came up with (") in the cell.
What could I have done wrong? Here is the formula.
=ArrayFormula(textjoin("",true,if(min(if(((countif($B$5:$EC$16,$B$5:$EC$16)=1)+countif(A36:$A$36,$B$5:$EC$16))=1,(row($B$5:$EC$16)+(1/(column($B$5:$EC$16)+1)))*1,""))=(row($B$5:$EC$16)+(1/(column($B$5:$EC$16)+1)))*1,$B$5:$EC$16,"")))
RANGE : b5:EC16
output header: A36
Regards
Sunny
Sunny Dhillon,
Try this array formula:
=TEXTJOIN("", TRUE, IF(MIN(IF(((COUNTIF($B$5:$EC$16, $B$5:$EC$16)=1)+COUNTIF(A36:$A$36, $B$5:$EC$16))=1, (ROW($B$5:$EC$16)+(1/(COLUMN($B$5:$EC$16)+1)))*1, ""))=(ROW($B$5:$EC$16)+(1/(COLUMN($B$5:$EC$16)+1)))*1, $B$5:$EC$16, ""))
How to just list out the unique from list 1
Nguyen Hai Tuan
Read this:
https://www.get-digital-help.com/how-to-extract-a-unique-list-and-the-duplicates-in-excel-from-one-column/
Dear Sir,
I handle bankruptcy cases for many companies. The details of each company are maintained in different workbooks. The reporting compliances and the processes have very stringent timelines. I need to have all the details of reporting requirements and processes due yesterday to three days from now on a single master sheet distinct from these workbooks.
The details of the reporting required and processes are on the columns G, I K, M O, R of worksheet "REPORTING" and column h of "PROCESS".
PLEASE help me with a formula or a vba code by which I can get the complete rows where the relevant dates appear on the master sheet mentioned above.
I have tried working with the formulas given by you, as I find your solutions to be effective as you explain them nicely and the logic once clearly understood, can be applied anywhere.
I have been able to get the answer for a column but not the complete row, if I am using the same worksheet or even the same workbook but not on a separate workbook. Besides, I get a #num wherever the dates are out of range. Is it possible to get a blank instead of this error message.
I have been struggling with this for long. Kindly help. I have attched the link to my google drive in the column for website. Just for information I use macbook pro and ms excel for mac.
A big thank you for bringing me upto this point ans bigger thank you in advance for leading me to the destination.
I am trying to count the number of unique strings of characters separated by deliminator in a single cell. The strings are made up of either several numbers, combined numbers and text, or single digit numbers. I need to count:
1- Unique strings of numbers only
2- total sets of strings in a cell
3- total number of single digits
4- total number of unique single digits in a cell
5- total number of words in a cell
6- total number of unique words in a cell (specific words)
Can anyone help me? I've been trying to find answers to this for days now. I was able to find a way to count unique words, but for some reason it doesn't always work. Right now, I'm pressed to find out how to count the unique serial numbers in a cell.
I'm looking to get a count of unique strings of numbers or numbers and text within a single cell, each separated by the vertical bar.
I have searched for days and cannot find anyone that can help me. Surely, you must know how to do this! Please!
MARGIE CHAPPELL
I recommend that you check out the "Text to Columns" feature, it will separate values in a cell separated by a delimiting character into multiple cells.
https://www.laptopmag.com/articles/use-text-columns-excel
Once you have values separated you can use the UDFs I have on my website.
Can this formula be changed to an array formula? I also need the DISTINCT values (not UNIQUE values) from both the columns. for e.g. A,B,C from List 1 and C,D,E from List 2, which when combined should result in A,B,C,D,E.