E-Mail '5 easy ways to extract Unique Distinct Values' To A Friend
Email a copy of '5 easy ways to extract Unique Distinct Values' to a friend
Loading ... Loading ...165 Responses to “5 easy ways to extract Unique Distinct Values”
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.
Hey there - thanks a lot for this tip - though I initially could not get this to work - eventually I figured out MY problem the INDEX rownum was using "ROW(A2:A20)-1", when it should be using "ROW()-1" - otherwise it ALWAYS returns the start of the array.
Thank you for visiting Get Digital Help and commenting! I have changed the formula and the attached file. Using named ranges simplifies formula customization a lot!
Many thanks for the useful information posted on this website! However, as I am working on a very large set of data (from between 50 - 8000 records) and whenever I change the name range to go beyond the 1000th record, the unique list value would return error. I have tried to name the whole column A but it also doesnt work.
would be grateful if you could assist on this.
Many thanks for all your effort!
Thank you!
I tried to recreate your error, but failed. I have attached an excel file with over a 1000 records that works for me. The excel file is at the end of the above blog post. Try it and see if the excel file returns an error.
I am using excel 2007, what version are you using?
Have you changed cell ranges for both named ranges in your excel sheet?
List (A2:A20)
unique_start (B2)
Maybe you have a blank cell somewhere in your list? A blank cell returns an error. See this post:
https://www.get-digital-help.com/how-to-automatically-create-a-unique-list-and-remove-blanks/
There is a somewhat shorter approach:
Let initial items in colA be named as "List". In B2 enter the
following array-formula:
=INDEX(List,MATCH(0,COUNTIF($B$1:B1,List),0)) and copy it down as far as necessary, to get unique list.
That is really amazing, you made the array formula even shorter!
To remove any errors, Excel 2007 users can use IFFERROR() function:
=IFERROR(INDEX(List,MATCH(0,COUNTIF($B$1:B1,List),0)),"") + CTRL + SHIFT + ENTER and copy it down as far as necessary.
Thank you for your valueable comment!
hi.
thanks for the formulas (eero's and the revised one) guys. it really cured my headaches.
for the unique list generated (Column B), can the...
1) unique list sorted A-Z order?
2) unique list sorted based on occurrence (highest to lowest)?
the trick is: can the sorting be done without complicating eero's formula above?
reason i'm asking because the other posts on sorting is too complicated for my head to wrap around ;)
thx!
Interesting questions!
I´ll see if I can come up with some good solutions.
Thanks for commenting!
thanks!
in case you're wondering, eero's formula does not need the unique list range (Column B) to be same size as the main list (Column A).
I just need to copy down the formula to get the rest of the unique list.
Your other posts on sorting is based on range-based array formula (i.e. Column B must be same size as Column A in order for the array formula to work). This works well if you have short list, but if u have thousands of entries, unique list range must equal the same.
One other that thing that i noticed is the ranged-based array formula is fixed (you can't delete those 'empty' cells in this range).
anyways, thanks again for looking into another solutions and not to forget your valuable blogs here!
David, here is the answer to your first question.
unique list sorted A-Z order?
Array formula in cell B2, see picture above.
=INDEX(List, MATCH(MIN(IF(COUNTIF($B$1:B1, List)=0, 1, MAX((COUNTIF(List, "<"&List)+1)*2))*(COUNTIF(List, "<"&List)+1)), COUNTIF(List, "<"&List)+1, 0)) + CTRL + SHIFT + ENTER and copy it down as far as necessary, to get unique list sorted alphabetically from A to Z. If anyone can come up with a shorter formula, i would be happy!
oh wow! it works! :)
THANKS!
David, here is the answer to your second question.
unique list sorted based on occurrence (highest to lowest)?
Array formula in cell B2, see picture above.
=INDEX(List, MATCH(IF(MAX(COUNTIF(List, List)*IF(COUNTIF(B$1:$B1, List)=1, 0, 1))=0, 1, MAX(COUNTIF(List, List)*IF(COUNTIF(B$1:$B1, List)=1, 0, 1))), COUNTIF(List, List)*IF(COUNTIF(B$1:$B1, List)=1, 0, 1), 0))+ CTRL + SHIFT + ENTER and copy it down as far as necessary, to get sorted based on occurrence (highest to lowest)
I have to think some more to get this formula shorter, I don´t like the length of it.
works great! :D
thanks!
p/s: as long as the array formula works, i'm happy. but do keep up your great job on the formulas! MS Excel gurus should visit and give you a thumbs up! :)
Wow the short formula works, amazing!!!
Thanks!
Wow! This is just amazing. Works great. I would love if someone could explain how this works step by step. I tried, but couldn't. I am trying to extend this fomula with a user defined formula.
[...] How to extract a unique distinct list from a column in excel [...]
Sriram Venkitachalam,
I now have tried to explain the array formula, see the new section above.
Thanks for commenting!
Would you please advise me on how to created named range which includes A-Z sorted list of UNIQUE values of other list?
Thanks a lot for this example. But is there also a way without using CSE? because a use a tool where i import Excel file which does not support CSE formulas in Excel.
Hopefuly someone has an idea
Dear All, It's my first post right here, and I'd like to thank you all with special THANKS for ADMIN. Well, I'm looking for more info and explanation about the current topic of https://www.get-digital-help.com/how-to-extract-a-unique-list-and-the-duplicates-in-excel-from-one-column/ It has mentioned above,
"How this array formula works: =INDEX(List,MATCH(0,COUNTIF($B$1:B1,List),0)) The array formula uses MATCH() to find the first 0 (zero) value in the COUNTIF array. When you copy the array formula down the COUNTIF formula range expands. The first cell: COUNTIF($B$1:B1,List), the second cell: COUNTIF($B$1:B2,List) and so on"
It's understood, but I'm still puzzled for the following reasons:
1. When applying the mentioned formula, the result is always the first record only.
2. All array elements shows INDEX(List,MATCH(0,COUNTIF($B$1:B1,List),0)) in formula bar, which indicates that the range expansion mentioned above is not applicable.
3. The attached example sheet shows
@ cell B2: INDEX(List,MATCH(0,COUNTIF(B1:$B$1,List),0)),
while @ cell B3: INDEX(List,MATCH(0,COUNTIF(B$1:$B2,List),0)),
and It’s OK, if we are talking about singular cell formula.
So how come (B1:$B$x) changed into (B$1:$Bx) from cell to another within the same array command?
Do I miss something?
Is there any Excel settings should be takes place?
Please help & advise.
Thank YOU & BEST Regards,
Raymond,
You need to convert your formulas to values.
Copy and "Paste Special.." the values created by the CSE formula.
Select "Values" and press with left mouse button on OK button.
OnlineAlone,
Copy =INDEX(List,MATCH(0,COUNTIF($B$1:B1,List),0)) + CTRL + SHIFT + ENTER into your first cell. (Cell B2)
Copy your first cell an paste to the remaining cells.
The formula then automatically changes to:
Cell B3: =INDEX(List,MATCH(0,COUNTIF($B$1:B2,List),0))
Cell B4: =INDEX(List,MATCH(0,COUNTIF($B$1:B3,List),0))
and so on.
Oscar,
Thank you for your quick response.
OK, I got it.
OnlineAlone,
I missed this question:
So how come (B1:$B$x) changed into (B$1:$Bx) from cell to another within the same array command?
You have found an error in the attached file.
I have now changed the formula in the attached file and uploaded it to this blog post again.
Here is an excellent explanation of absolute and relative references:
https://www.cpearson.com/excel/relative.aspx
hi all,
thanks for the great formula/array formula. it works great.
lately, i noticed that the array formula will make the excel calculations CRAWL if I have thousands of entries (dates; but mostly repeated because of different product).
e.g.
1-Jan-2010 ProductA
1-Jan-2010 ProductB
1-Jan-2010 ProductC
1-Jan-2010 ProductD
2-Jan-2010 ProductA
2-Jan-2010 ProductB
2-Jan-2010 ProductC
...
...
the unique distinct values from the dates are 1-Jan-2010, 2-Jan-2010, and so on.
In a month (31-days) x 13 products = 403 entries. Multiply this to 12-months = 403 x 12 months ~est. 4.9k dates to be parsed.
the jackpot question: Is there a way to easily parse long list, without sacrificing performance (recalculations)?
thx!
David,
have you tried "Advanced Filter"?
https://www.get-digital-help.com/how-to-extract-a-unique-list-and-the-duplicates-in-excel-from-one-column/#advfilter
Thanks for commenting!
2 problems i see using Advanced Filter:
a) The first 2 generated entry is a repeat. i.e. 1-Jan-2010, 1-Jan-2010, 2-Jan-2010, 3-Jan-2010, and so on.
first entry is named “Extract”).
b) The dates from my master table is set with a dynamic named range. Advanced Filter will generated unique
distinct list, manually.
I daily update the table
thx!
david,
See this blog post (and the attached file):
https://www.get-digital-help.com/create-a-unique-distinct-list-of-a-long-list-without-sacrificing-performance-using-vba-in-excel/
just noticed this standalone post. incredible.
will try it out.
note: reason why Advance Filter is not preferred (by me anyway) is the list will gets longer daily. i wouldn't want to keep (manually) using Advanced Filter.
'sides, i'm preparing the file for Excel noobs. he/she should not be complicated by the steps :D
the VBA macro button is interesting tho! will try it now!!
thx guys!!
hi oscar, finally been trying very hard to use this. my situation as such:
1) First sheet is called VC (the master table with dates).
2) Second sheet is called Calc (where the calculations are located).
3) Third sheet is called Charts (where the VBA-macro button is located).
Charts sheet is the only visible sheet. Managers are viewing this only.
Calc sheet is hidden (to prevent ppl snooping or ruin the formulas :))
It seems that Advanced Filter function itself cannot PUSH filtered data to Calc sheet (target).
I've tried the other way round to PULL filtered data from VC sheet (source). However, the macro uses an absolute address of the selected range.
this is the vb code in Calc sheet:
Sub Macro3()
'
' Macro3 Macro
'
'
Sheets("VC").Range("A2:A5004").AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=Range("C2"), Unique:=True
End Sub
David, try this:
Sub AdvFilter()
'
' AdvFilter Macro
Range("VC!A2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
"Calc!C2"), Unique:=True
End Sub
thx oscar. it works, but only after i added this:
Sub AdvFilter()
'
' AdvFilter Macro
Sheets("VC").Select
Range("VC!A2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
"Calc!C2"), Unique:=True
Sheets("Chart").Select
End Sub
-----------
problem with Adv Filter is that it cant just pull data from another sheet. The sheet must be active before Adv Filter can be executed.
my rough tweak is to Select the VC sheet first, then let the Adv Filter routine run (generated on Calc sheet). Then change back to Chart sheet where the button is.
this creates a short sheet "flipping" when the button is created.
David,
To remove "flipping" use: Application.ScreenUpdating = False
/Oscar
thx oscar, the "no-flipping" function works.
btw, do u know that the Adv Filter-unique value doesnt work if the first 2 values are the same?
e.g.
10001
10001
10001
10002
10002
10003
10004
10004
10005
generated list from Adv Filter are...
10001 <-repeat
10001 <-repeat
10002
10003
10004
10005
david,
Advanced Filter uses your first value as a column header.
/Oscar
I been trying to do this for a long time. I tried by pivotTable to extract the list without duplicate items.
This much easier, clear, and great! Thanks a lot!
I found out the column 'A' must choose the title of it 'A1' also, to avoid in column 'B' shows 'A2' twice, and there is no problem to choose 'A1' coz it shows the same title in 'B1'.
Thanks again!
In excel 2003 and older you can remove error by using ISNA() function.
The formula is brilliant but it took me some significant amount of time to understand it (use Evaluate formula).
Wiciu,
Thanks!! I have added the ISNA() function to this post.
/Oscar
Oscar, these countif function formulas are great because they are able to extract a list with blank cells in the range. Is there a way to extract a list using sum(if instead of countif because this will work with closed workbooks (when the need arises), while also having the ability like the countif function to handle blank cells in the range? Maybe we need to put a condition in the front of the formula to handle the blank cells. Keep up the brilliant work. This website is one of the top Excel websites out there.
Sean,
Thanks!
As far as I know, you can´t extract a unique distinct list from a closed workbook with excel formulae.
Sean,
See this post: https://www.get-digital-help.com/extract-unique-distinct-numbers-from-closed-workbook-in-excel-formula/
Sean,
Here is another post: https://www.get-digital-help.com/extracting-unique-distinct-text-values-from-a-closed-workbook-in-excel-formula/
Oscar,
Thanks for the great article! Is it possible to modify the array formula to return the entire reduced list in a single cell? Instead of taking up as much real estate as the original list. For example the function '=offset(A1,0,0,10)' would return a list 10 numbers long in one cell. Not too useful in that cell, but I can then refer to only that cell for a data validation list or use it to do additional processing to.
Trey,
Data Validation was designed to work with lists of cells. I don´t know how to solve your problem.
I suppose another way to word it would be, I'm looking to return a list. The result of the above formula is a single value. Can array functions return a list instead of a single value? I am guessing, but isn't the result of typing "=Offset(A1:A10,0,0)" into B1 a list in cell B1? If your formula could output a list into one cell (say B2 from the example), a Data Validation reference could be made to just B2 instead of B2:B20.
As far as I know, "=Offset(A1:A10,0,0)" + CTRL + SHIFT + ENTER creates an array. You would only see the first value in cell B1. Creating a data validation reference to cell B1 won´t work. Only the first value is used.
Can array functions return a list instead of a single value?
Yes, but not in a single cell.
What would the code look like if I have two collumns?
In first one are names of people and they can occur many times.
In the second one there are different cities. So for each name one or more different cities can occur in second column.
The upper code does well when listing unique names. What if in certain cell I select from dropdown list one of these names and excel has to create secondary unique list (based on only one choosen name from column one). Any suggestions? Would mean a lot!!!
THANK YOU THIS HELPED. IT TOOK ME A WHILE TO FIGURE OUT... WHAT REALLY HELPED WAS BEING ABLE TO GET THE EXCEL FILE SO THANKS!
joško,
See this post: https://www.get-digital-help.com/create-dependent-drop-down-lists-containing-unique-distinct-values-in-excel/
I tried to use the =IF(ISNA(INDEX(List, MATCH(0, COUNTIF($B$1:B1, List), 0))), "", INDEX(List, MATCH(0, COUNTIF($B$1:B1, List), 0))) + CTRL + SHIFT + ENTER and copy it down as far as needed.
Its not working. I get a blank cell only. Could you give some guidance. Working with excel 2003.
Thanks,
Greg
I forgot to mention I do have blank values and that the list is on a different sheet than what the formula would be does this cause a problem?
Thanks again,
Greg
Greg,
Yes, I think that causes a problem.
See this post: https://www.get-digital-help.com/create-a-unique-distinct-sorted-list-containing-both-numbers-text-removing-blanks-in-excel/
Thanks for your comments!
Greg,
This is how I handled blanks in a range with this formula.
=INDEX(list,MATCH(0,IF(ISBLANK(list),"",COUNTIF($B$1:B1,list)),0))
Oscar,
I use this formula a lot. By adding conditions in front of the countif, I can extract a list with numbers, etc.
Is there a way to extract a list from Column A based on the total for this value being above say 30 in column B? I have been trying to use Sum(if to make it work without any success. The result below would be CC.
aa 10
aa 15
cc 40
cc 5
Sean,
That is the formula I should have written as an answer. Great contribution! I have added the formula to this post.
THANKS!
Extract a unique distinct list from column A where the total for this value in column B are above 30:
Array formula in cell C2:
=INDEX(list, MATCH(0, IF(SUMIF($A$1:$A$4, $A$1:$A$4, $B$1:$B$4)>30, COUNTIF($C$1:C1, list), ""), 0)) + CTRL + SHIFT + ENTER.
Fantastic Oscar. I was told that sumif cannot coerce a range, which could make the next part more difficult. I am looking for the values that are not equal to zero correct to 2 decimal points. Sometimes the sum is 0.0005, which is zero correct to 2 decimal points.
Is there a way to do this?
Oscar, the sum could be 0.000789 or -0.0000046464. These are rounding differences. Using Sum(if instead of sumif could easily coerce the entire range in memory to 2 decimal points.
Oscar, I figured it out.
INDEX(List, MATCH(0, IF(ROUND(SUMIF($A$3:$A$10, $A$3:$A$10, $B$3:$B$10), 2)0, COUNTIF($E$1:E1, List), ""), 0)).
Dear Oscar,
I try to find out if it is possible to rank the outcome of the most forecoming uniques in the 'Unique distinct list'. Like a top 10 (or 25) list of the most found uniques?
I work here with a huge list, it can be large as +12.000 rows. From this list i want to find out the top 10/25 most forecoming uniques, and ranked them. Most found uniques on top of list.
Sokolum,
First create the unique distinct list.
Use advanced filter to create a unique distinct list. Your list is huge so the formula on this page is too slow.
Read more about advanced filter.
Next to the unique distinct column create a formula to calculate occurences. Somtehing like this: =COUNTIF($A$1:$A$12000, C2) + ENTER. Copy the cell and paste it down to the end of your unique distinct list.
And last, sort the unique distinct list and occurences largest to smallest.
Thanks sean and oscar!
I used: =INDEX(list,MATCH(0,IF(ISBLANK(list),"",COUNTIF($B$1:B1,list)),0))
All I get is the NA# error now. All the blanks are at the end of this list name
Thanks,
Greg
I think I mentioned this before but my data is on different sheets and I am using excel 2003.
Thanks,
Greg
These results is exactly i was looking for. Indeed that a formula is slow. Now i will try to find out if i can automate this by a script.
Greg Savinda,
Did you adjust the relative reference? Bolded in formula:
=INDEX(list,MATCH(0,IF(ISBLANK(list),"",COUNTIF($B$1:B1,list)),0))
Oscar,
I have used the following:
=INDEX(list,MATCH(0,IF(ISBLANK(list),"",COUNTIF(Sheet1!$D$1:D1,list)),0))
Would this error be to the fact that it is in a different sheet?
Thanks,
Greg
Greg Savinda,
I don´t think so.
Did you adjust the named range to your list?
Did you forget CTRL + SHIFT + ENTER?
Is it possible to extend this by matching items that meet a criteria?
I have a list of credit card transactions showing the name of the card holder, their Branch and the amount. I want to produce a report for each Branch, so what I want is to extract only those people who match the Branch name. For example:
Frank Branch A
Frank Branch A
Frank Branch A
Joe Branch A
Mary Branch B
Jane Branch C
Mike Branch A
Joe Branch A
Dave Branch C
I would like a list of only those people for Branch A, and then be able to summarise the transactions. Or should I do this in two stages?
Anura,
See this post:
https://www.get-digital-help.com/extract-a-unique-distinct-list-by-matching-items-that-meet-a-criterion-in-excel/
I can't believe the speed of the response! I will test out with some real data from one of my reports and see what happens. Thanks for this.
[...] gospieler says: September 8, 2010 at 10:34 pm This tutorial show you How to extract a unique distinct list from a column in excel (without using advanced filter) https://www.get-digital-help.com/how-to-extract-a-unique-list-and-the-duplicates-in-excel-... [...]
Oscar,
I have a sheet with 3000 rows of invoice dates that are out of order. There could be a maximum of 6 dates and a minimum of 1 in each row. The invoice dates are in row A1:F1 and I would like the ordered, unique distinct list to be in G1:L1, and also display a blank once the data runs out. How do I alter your formula to accomplish this? Thanks!
Aamer,
Read this post: Excel udf: Remove duplicates from a large dataset
and this post: Remove duplicates and sort dates by each row in excel
The good thing about this formula is that it is short and easy to remember. The main drawback with countif is that it is not able to coerce a range. I would like an unique list based on the last 4 characters in a cell. This would mean using the right function.
1845-2145
1846-2145
1845-2176
The unique list here is 2145 and 2176. Is there a short but sweet formula like the original countif formula above?
Sean,
Values in cell A1:A3:
1845-2145
1846-2145
1845-2176
Array formula in cell B2:
=RIGHT(INDEX($A$1:$A$3, MATCH(0, COUNTIF($B$1:B1, RIGHT($A$1:$A$3, 4)), 0)), 4) + CTRL + SHIFT + ENTER.
Copy cell B2 and paste it down as far as needed.
Oscar!
THANK YOU SOOOOOOOOOOOOOOOOOO MUCH!!!! You have no idea how long I've been looking for this!!! I was told it couldn't be done but here it is! You've done it and without using any macros!!!
Many, Many Thanks!!!!
Keep up the AWESOME work!
Charlie,
I am happy you found what you were looking for! Blog reader Eero is the creator of this formula.
Hi, Oscar!
"Maybe someone else have some lightning fast formula technique?"
You can try and explore the following, something different approach:
I tried this on my laptop with Excel 2007:
The following macro fills the column A with 1048574 random numbers between 1 and 1048574
Public Sub fillrandomnumbers()
Dim m As Long
m = 1048574
With Range("a1:a" & m)
.Formula = "=RandBetween(1,1048574)"
.Value = .Value
End With
End Sub
Now, it is needed to sort the list in ascending order!
Then, to get a unique list of numbers into column B run the following macro:
Sub uniquelist()
Dim s As Long
Dim v As Variant
Dim z As Long
s = 1048574
v = [a1]
Columns("A").Insert
With Range("a1:a" & s)
.Formula = "=if(b1<>b2,b2)"
.Value = .Value
.Sort Range("a1"), xlAscending
z = .SpecialCells(2, 3).Count
Range("c1:c" & z) = .Value
End With
Columns("A").Delete
[b1] = v
End Sub
It takes about 20-25 second to complete this huge task on my laptop.
Remarks:
The last macro works with strings as well. The sorting procedure can also be inside the macro. And there must be at least one free cell below the initial list.
Hi..would u provide some video tutorial for this?
It seems some characters may be lost in the macro code after posting.
If running macros fails then you can send me a request and i will send a file by email:([email protected])
Thanks Eero for your contribution!!
Oscar, it works great. I also tried it with the mid function. I thought countif does not work with arrays. The following formula will not work with countif- =TEXT(A1:A3,mmmmyy).
Sean,
Read this post: Remove duplicate text strings based on the 4 last characters in a cell in excel
Is there a way to create a unique distinct list using as criteria the values that do not exist in another column?
COLA ColB
A Z
B A
C B
D C
D X
The result is Z and X.
The formula I have used to create criteria based on another column
=INDEX($B$1:$B$4, MATCH(0, IF($A$1:$A$4="A", COUNTIF($D$4:$D4, $B$1:$B$4)), 0))
Sean,
Array formula in cell C2:
=INDEX($B$1:$B$5, MATCH(0, COUNTIF($C$1:C1, $B$1:$B$5)+COUNTIF($A$1:$A$5, $B$1:$B$5), 0)) + CTRL + SHIFT + ENTER. Copy C2 and paste it down as far as needed.
Oscar,
It works fantastic. Thanks for all your help.
[...] You might notice in Debra's workbooks that the DinnerPlan worksheet has dinner items listed multiple times. In order to create a unique list for the recipe lookup dropdown list, I used the code found at Extracting a Unique List. [...]
[...] A2 and paste down as far as needed. This formula creates uniue distinct symbols. Read this post: How to extract a unique distinct list from a column for a formula [...]
Actually, there is shorter way without having to do the "array-formula."
The regular formula is: =IF(COUNTIF($A$1:A1,A1)=1,A1,"")
As soon as the formula finds the first one, it will blank-out the dups.
*****Note: Must sort the list first
The formula is even faster if you use countifs in Excel 2007 instead of countif. Countifs calculated 7,000 rows with 3 criteria very quickly for me.
Sean,
Great tip, I didn´t know that!
This has been very helpfull, thank you!, going to spend alot of my free time on this site, thanks! :)
=INDEX(List,MATCH(0,if(list2=A1,COUNTIF($B$1:B1,List)),0))
A1="a". Now I want to change the condition to list2=A2 ("b"). Is there a way to move onto the next condition ("b") automatically after it finds all the unique entries for "a"?
A1="A"
A2="B"
Sean,
If order is important, try:
=INDEX(List,IFERROR(MATCH(0,if(list2=$A$1,COUNTIF($B$1:B1,List)),0), MATCH(0,if(list2=$A$2,COUNTIF($B$1:B1,List)),0))
Not important, try:
=INDEX(List,MATCH(0,if(countif($A$1:$A$2, list2),COUNTIF($B$1:B1,List)),0))
Oscar,
It works great. This saves time in pulling a big list. Countif is so flexible or countifs if using Excel 2007. The only thing now is that I need another column showing if it is an A or B for the unique entries in List.
Dog A
Cat A
Cow B.
Oscar,
I think I figured it out. Just change the index to list2.
=INDEX(list2,MATCH(0,IF(COUNTIF($F$3:$F$4,list2),COUNTIF($F$8:F8,list)),0))
Keep up the great work.
Oscar,
If there is two duplicates with one equal to "A" and the other equal to "B", it only picks up the duplicate once. It should be twice since they have different conditions.
I would like to use =INDEX(List,MATCH(0,if(countif($A$1:$A$2, list2),COUNTIF($B$1:B1,List)),0))
dog a
cat a
dog b
Result
dog A
cat B
How do you fix this?
Sean,
I think you will find the answer in this article: Vlookup with 2 or more lookup criteria and return multiple matches in excel
The original extraction of names from a list works, but on trying the sort order formula, it doesn't. It DOES pick up the 1st alphabetized name, but then just repeats. And yes, the cell ref does increment:
=INDEX(List1, MATCH(MIN(IF(COUNTIF($A$1:A1, List1)...
=INDEX(List1, MATCH(MIN(IF(COUNTIF($A$1:A2, List1)...
The full formula is: =INDEX(List1, MATCH(MIN(IF(COUNTIF($A$1:A1, List1)=0, 1, MAX((COUNTIF(List1, "<"&List1)+1)*2))*(COUNTIF(List1, "<"&List1)+1)), COUNTIF(List1, "<"&List1)+1, 0))
I tried sorting the original formula list, and it worked for a moment, and then flipped back. Wild! Formulas in excel I just don't get. I'm an admin, not a coder :) Any help please?
Larry,
I think your formula is copied from here: Unique distinct list from a column sorted A to Z using array formula in excel
How to create an array formula
1. Copy (Ctrl + c) and paste (Ctrl + v) array formula into formula bar.
2. Press and hold Ctrl + Shift.
3. Press Enter once.
4. Release all keys.
I have edited the original post, I hope I have explained it in greater detail than before. Read: Unique distinct list from a column sorted A to Z using array formula in excel
The formula doesn't work, is says that a circular reference is created, I got the Excel file with the example and it gives NA as a result.
Dave,
I tried the file and it worked here.
I'm having the same problem as Dave, I've got the sample file and it looks fine when you view it, but as soon as you press with left mouse button into the Formula and press enter it removes the "{" around the statement and it then returns NA. I'm using Excel 2007.
Ah! pressing the control + shift after pasting into the formula bar sorts it, what does that actually do ?
That´s how you enter an array formula.
How to create an array formula
Copy (Ctrl + c) and paste (Ctrl + v) array formula into formula bar.
Press and hold Ctrl + Shift.
Press Enter once.
Release all keys.
I don't know why I can't make this work ...
To be sure I'm not doing anything wrong I made an example like yours but the array formula gives me an error for the first "List" and appears "#NAME?"
I got your example and there is working just fine.
Now I pasted my data in your example and it calculates only for the first 20 rows like in the example. So it isn't working for the whole column ..
Do I need to create a list or something?
pau,
you are right. I forgot to add how to create a named range.
I have now added how to create a named range in this blog post.
Hi Oscar,
This is fantastic.
I tried using this formula and it really helped.
I have a small problem that I am not sure on how to solve.
I now have a list of 15 cells with 3 unique values (arr formula in all 15 cells). When I use this into a pivot table, I want to get the count as 3 since that is the unique count. But I am getting 15 as the value!
I am assuming that the pivot table is reading the formula as a value and counting it. Any way to circumvent that?
ExcelBeginner,
read this post: Count unique distinct values in a pivot table in excel
From your article - https://www.get-digital-help.com/how-to-extract-a-unique-list-and-the-duplicates-in-excel-from-one-column/
You mentioned a formula to make lists from one column in alphabetical order. I've used this formula with success for a couple years but just stumbled on a problem I hadn't encountered before. For some reason it isn't functioning in the attached worksheet with the range I am searching in. You'll see in the attachment that in the Stats sheet D3 I have entered the formula
=IFERROR(INDEX(DlvDT1,MATCH(MIN(IF(COUNTIF($D$2:$D2,DlvDT1)=0,1,MAX((COUNTIF(DlvDT1,"<"&DlvDT1)+1)*2))*(COUNTIF(DlvDT1,"<"&DlvDT1)+1)),IF(ISBLANK(DlvDT1),"",COUNTIF(DlvDT1,"<"&DlvDT1))+1,0)),"")
And it produces a "". The range being looked up is in the Deadheads spreadsheet (Deadheads!$I$2:$I$3000) and named DlvDT1.
I couldn't figure out what was causing it to mess up after a while so I started messing with the range values to see if that had something to do with it. Surprisingly it did. If I do the same formula using range name DlvDTOne (Deadheads!$I$2:$I$706) the results will show, if I make it anything after I706 it will not show.
I also tried to go from Deadheads!$I$639:$I$3000 (DlvDTUno and got it to work successfully but it will not go any lower than I639.
That tells me something in the values between I638 and I707 is causing issues with your formula format. Can you look into it and see what you can figure out? I've made all three tests I explained available in cells D3, D4, & D5 respectively. Let me know if you can figure anything out.
Thanks!
(P.S. - Tried to submit via your upload page and it wouldn't let me. Email me and I can send you the xlsx file.
Zen Archery
In his book Wonders of Numbers (Oxford: Oxford University Press, 2001), pp. 275-276, Clifford Pickover posed a "Zen Archery" problem. In its simplest form, there is a target with 24 numbers on it. The archer must shoot 5 arrows at the target and hit numbers adding up to 200. The 24 numbers on the target are
97,101,139,41,37,31,29,89,23,19,8,13,
131,19,73,97,19,139,79,67,61,17,113,127
Pickover posed a similar problem at Archery by the Numbers. This is really a combinatorial problem -- given the 24 numbers taken 5 at a time, which unique combinations add up to 200?
There is some quick and dirty Java code on the Web, associated with Pickover's book, which solves the Zen archery problem for the 24 numbers given. However, it is not exactly a model of good programming, and it even assumes some foreknowledge of the answer in the code, i.e. the fact that all combinations adding up to 200 include the number 8.
Zen Archery
In his book Wonders of Numbers (Oxford: Oxford University Press, 2001), pp. 275-276, Clifford Pickover posed a "Zen Archery" problem. In its simplest form, there is a target with 24 numbers on it. The archer must shoot 5 arrows at the target and hit numbers adding up to 200. The 24 numbers on the target are
97,101,139,41,37,31,29,89,23,19,8,13,
131,19,73,97,19,139,79,67,61,17,113,127
Pickover posed a similar problem at Archery by the Numbers. This is really a combinatorial problem -- given the 24 numbers taken 5 at a time, which unique combinations add up to 200?
There is some quick and dirty Java code on the Web, associated with Pickover's book, which solves the Zen archery problem for the 24 numbers given. However, it is not exactly a model of good programming, and it even assumes some foreknowledge of the answer in the code, i.e. the fact that all combinations adding up to 200 include the number 8. Zen Archery
In his book Wonders of Numbers (Oxford: Oxford University Press, 2001), pp. 275-276, Clifford Pickover posed a "Zen Archery" problem. In its simplest form, there is a target with 24 numbers on it. The archer must shoot 5 arrows at the target and hit numbers adding up to 200. The 24 numbers on the target are
97,101,139,41,37,31,29,89,23,19,8,13,
131,19,73,97,19,139,79,67,61,17,113,127
Pickover posed a similar problem at Archery by the Numbers. This is really a combinatorial problem -- given the 24 numbers taken 5 at a time, which unique combinations add up to 200?
There is some quick and dirty Java code on the Web, associated with Pickover's book, which solves the Zen archery problem for the 24 numbers given. However, it is not exactly a model of good programming, and it even assumes some foreknowledge of the answer in the code, i.e. the fact that all combinations adding up to 200 include the number 8. the result is 27 groups of 5 number, the optimal combination how can I do that in excel 2003
Hi,
I have a table, I want to find which records that have criteria (example: column TOTAL > 0). PIVOT Table did not solve that I want.
I want relist this table into another sheet.
Could you help me? Thanks.
I was only able to get this to work in Excel2007 by switching to compatibility mode. What is different between the two versions that causes it to fail in Excel2007? Is there a version that works for Excel2007?
Hello,
I'm using VBS with Array
=FilterUniqueSort($A$2:$A$8212)
if there are no data I got #VALUE! how get rid of this please?
Sheet1,
VBA code:
Function FilterUniqueSort(rng As Range) Dim ucoll As New Collection, Value As Variant, temp() As Variant Dim iRows As Single, i As Single ReDim temp(0) On Error Resume Next For Each Value In rng If Len(Value) > 0 Then ucoll.Add Value, CStr(Value) Next Value On Error GoTo 0 If ucoll.Count = 0 Then FilterUniqueSort = "" Exit Function End If For Each Value In ucoll temp(UBound(temp)) = Value ReDim Preserve temp(UBound(temp) + 1) Next Value ReDim Preserve temp(UBound(temp) - 1) iRows = Range(Application.Caller.Address).Rows.Count SelectionSort temp For i = UBound(temp) To iRows ReDim Preserve temp(UBound(temp) + 1) temp(UBound(temp)) = "" Next i FilterUniqueSort = Application.Transpose(temp) End FunctionThis is actually code that I'm using from this link that is posted up
https://cdn.get-digital-help.com/wp-content/uploads/2009/03/Extract-unique-distinct-sorted-values-from-a-cell-range.xls
here I got #VALUE! error
p.S. any way that this script shift or insert cell for new unique value that appear and remove when value is not on the list....
I would like to have 2 lists sorted by this script with unique data under each other but with this one it just take next available cell to show data
Function FilterUniqueSort(rng As Range)
Dim ucoll As New Collection, Value As Variant, temp() As Variant
Dim iRows As Single, i As Single
ReDim temp(0)
On Error Resume Next
For Each Value In rng
If Len(Value) > 0 Then ucoll.Add Value, CStr(Value)
Next Value
On Error GoTo 0
For Each Value In ucoll
temp(UBound(temp)) = Value
ReDim Preserve temp(UBound(temp) + 1)
Next Value
ReDim Preserve temp(UBound(temp) - 1)
iRows = Range(Application.Caller.Address).Rows.Count
SelectionSort temp
For i = UBound(temp) To iRows
ReDim Preserve temp(UBound(temp) + 1)
temp(UBound(temp)) = ""
Next i
FilterUniqueSort = Application.Transpose(temp)
End Function
Function SelectionSort(TempArray As Variant)
Dim MaxVal As Variant
Dim MaxIndex As Integer
Dim i, j As Integer
' Step through the elements in the array starting with the
' last element in the array.
For i = UBound(TempArray) To 0 Step -1
' Set MaxVal to the element in the array and save the
' index of this element as MaxIndex.
MaxVal = TempArray(i)
MaxIndex = i
' Loop through the remaining elements to see if any is
' larger than MaxVal. If it is then set this element
' to be the new MaxVal.
For j = 0 To i
If TempArray(j) > MaxVal Then
MaxVal = TempArray(j)
MaxIndex = j
End If
Next j
' If the index of the largest element is not i, then
' exchange this element with element i.
If MaxIndex < i Then
TempArray(MaxIndex) = TempArray(i)
TempArray(i) = MaxVal
End If
Next i
End Function
thank you for reply
Ok I figure out but now I'm getting 0 what shows in line for all arrays in that column and affect formulas from beside cell's that takes value from this one example
Col A Col B array Col C
#N/A 0 0
#N/A 0 0
#N/A 0 0
#N/A 0 0
etc...
also prevous question can this VBS insert or shift cells when new unique data appear?
Thanks
Sheet1,
This is actually code that I'm using from this link that is posted up
https://cdn.get-digital-help.com/wp-content/uploads/2009/03/Extract-unique-distinct-sorted-values-from-a-cell-range.xls
here I got #VALUE! error
I got the excel file and it works here.
p.S. any way that this script shift or insert cell for new unique value that appear and remove when value is not on the list....
Yes it does, if the value is in the cell reference.
I would like to have 2 lists sorted by this script with unique data under each other but with this one it just take next available cell to show data
The custom function can only process a single cell reference. I am not sure I understand..
Ok I figure out but now I'm getting 0 what shows in line for all arrays in that column and affect formulas from beside cell's that takes value from this one example
Col A Col B array Col C
#N/A 0 0
#N/A 0 0
#N/A 0 0
#N/A 0 0
etc...
Have you created an array formula with the custom function?
Ok I just followed instructions listed abowe and copied VBS so I selected 100 cells and applied array on it..
array is pointed on column on another sheet, but if there are empty cells I got
Col A Col B array Col C
#N/A 0 0
#N/A 0 0
#N/A 0 0
#N/A 0 0
for all of them
How to stop to not shows to the end all 100 same error?
and is there any chance for this VBS instead of Error GoTo 0 put that is empty (doesn't show anythin)? as other formulas aroound when get 0 from col B start to showing #N/A
Thank you
Sheet1,
What is shown in the formula bar? What cell reference are you using?
Did you also copy the selectionsort function from the attached file?
Hi
Many Thanks for your site
I have used "the Extract unique distinct sorted values from a cell range (vba)" code and allmost everything is fine
But the first cell value in the sorted list is allways the first cell value from the input data, not correctly sorted
List1
B
C
D
A
FilterUniqueSort(list1)
B
A
C
D
Is there some way to fix it?
Hej Mats!
I recreated your setup but I can´t see anything wrong?
Hi
This is amazing
I deleted the VBA module and reimported
Now it works :-)
I have had very good use of your excelent code and information about array formulas
Many thanks
Mats
Oscar,
I am using the VBA code & FilterUniqueSort array to generate unique lists that drive Selection Change AutoFilter on multiple colums. Is there a way to make the list only return the unique values that are visible in the filtered source data?
I see a similar answer above using just an array formula, but my source is too long for that to be practical. Any help would be greatly appreciated.
Robert Jr
Robert JR,
Great question, a blog post is soon coming up.
Robert J,
Read this post: Extract unique distinct values from a filtered table (udf and array formula)
When I do this formula in Excel 2007, it just produces the same list as the original. ??
Terry,
Did you remember to create an array formula? (Ctrl + Shift + Enter)
Did you adjust cell references (bolded)
Hi Oscar, thanks for your reply. Yes I did all that. What I discovered was, this formula only works when the two columns are physically adjacent to each other. I am unable to do this in this case, so my unique list is in another part of the spreadsheet. Seems like it should work though, since I am using a named reference for the source data. I suppose I could just reference the source data in a column next to the unique list as a workaround.
Terry,
They don´t have to be adjacent. They don´t have to be on the same sheet.
Get the Excel file:Terry.xls
Oscar, I copied my list into your sheet and it behaves the same way as it does in mine. I am using a list of nos. rather than text. Would this matter?
Terry,
I changed the text values to numbers and the formula filters unique distinct numbers.
See attached file:Terry1.xls
I am using the blanks formula below to recover a list when a set of numbers in a second identically sized list(List2) are blank
I was wondering if there is a way to modify this section of the formula so that it searches for a particular colum (Range "B:D") and takes rows 2 to 10 as the list. The column headings will be dates.
=INDEX($A$2:$A$10,MATCH(0,IF(ISBLANK($B$2:$B$10),"",COUNTIF($E$2:E2,$A$2:$A$110)),0))
Does anyone have any insight on this matter?
Cheers
Is there a way to take the results of an Array Formula (i.e. a list of names called CompScenList (a name range)) and gather the unique distinct values? I looked but could not see this question in the history...
Hello Oscar,
It's incredible that you've maintained this post for nearly three years, now I too have a question for you.
I am filtering my list from one sheet to another (Sheet 1 to Sheet 2), and everything seems to be in working order, except that my unique list is printing all my results twice:
Ashley
Ashley
Jenna
Jenna
Angie
Angie
Sarah
Sarah
Here is a copy of the formula I am using:
=INDEX(ClientList,MATCH(0,COUNTIF($A$1:A1,ClientList),0)) + Ctrl + Shift + Enter
What can I do to solve this?
Next, I am already using the first row on Sheet 1, and the first two rows on Sheet 2 to for column titles. When I attempt to change "COUNTIF($A$1:A1..." to "COUNTIF($A$3:A3..." I get a circular reference error. What should I do to make sure my unique list isn't checking for column titles?
Thank you in advance for your time!
-Philip
Please help to create a formula to pick all cells refering to a pertical condition.
Let me brief, suppose Colume A is having a list of employees (ofcourse, unique values) and Column B is having thier respective higher qualification (consider some employees with same qualification), now I want to put formula which enables to find list of all employees if I type a qualification (side by side) i.e if I type "MS" in Cell C1, the list of all employees, who are with "MS" in column B - should be there in D1, E1, F1........
Raja,
You can do exactly that with the formula in this post:
How to return multiple values using vlookup
Thanks for commenting!
Philip Adams,
If you enter the array formula in cell B3, the relative cell reference (bolded) in the formula must be:
When you copy the formula down to cell B4 the relative cell reference changes automatically to:
If you use the same cell reference as the current cell, you will get a circular reference warning.
Oscar,
Thank you very much for your speedy reply. I was wondering if it would be possible to add more criteria to the unique list. For example, if I only wanted the names in ClientList to appear if the text "Auto-Renewal" appeared in their respective column O.
Thank you again for your time.
-Philip
Philip Adams,
This post is probably interesting: Create unique distinct list from column where an adjacent column meets criteria
Hey Paul,
Trying to do something very easy, I'm sure. :)
I have a list that shows people who have been interviewed and whether they accepted or not. For the ones who have not, I want to have another table that lists their names and then I can comment on reasons for not accepting.
So I want a table like this:
Person1 - accepted
Person2 - not accepted
Person3 - accepted
Person4 - not accepted
To output to a table like this:
Person2
Person4
... and so on, in order...
Thanks!!
Q:
Considering A1: name
B1: Status
A2 to A10 person1, person2...
B2 to B10 accepted, not accepted...
in D1 accepted
in E1 not accepted
then in d2:
=IFERROR(INDEX($A$2:$A$10, SMALL(IF("accepted"=$B$2:$B$10, MATCH(ROW($B$2:$B$10), ROW($B$2:$B$10)), ""), ROW(A1))), "")+Ctrl+Shift+Enter
and in e2:
=IFERROR(INDEX($A$2:$A$10, SMALL(IF("not accepted"=$B$2:$B$10, MATCH(ROW($B$2:$B$10), ROW($B$2:$B$10)), ""), ROW(B1))), "")+Ctrl+Shift+Enter
With this you'll have the list of the accepted people in column D and the list of not accepted in column E.
file available at:
https://uploading.com/files/e76mam54/Q.xlsx/
Cheers,
Cyril.
You're awesome Cyril! It totally worked... thank you so much!
Thank you so much for the unique values formula. It saved tons of coding to replicate and it does it automatically.
Dear Mr. Oscar,
Excellent, thanks for spending your valuable time for me.
Really its amazing, i can't believe that alla these things can happend in excel, after reading these posts.
But in the answer you provided for me, some formulae are used, which were new to me. Ofcourse I know that Excel help is there, but is there any thing else (any blog or page or book) that explains more effieintly that help.
Thanks in advance
Chip,
Thanks, but the work is done by Eero.
Raja,
Ofcourse I know that Excel help is there, but is there any thing else (any blog or page or book) that explains more effieintly that help.
I am sorry, I don´t know of any books about array formula basics. Maybe someone else can help me out here?
Cyril,
thanks for helping me out!
Oscar,
Why thank you!
your website is a gold mine, not only it is user friendly but it is filled with examples and real life cases, frankly i think you have here the best excel oriented forum and learning site.
Glad I could be of any assistance, but as I said, you are still the reference here.
kudos!
Raja,
Excel 2007 Bible by John Walkenbach
Publication Date: January 3, 2007 | ISBN-10: 0470044039 | ISBN-13: 978-0470044032
Excel 2010 Bible by John Walkenbach
Publication Date: May 10, 2010 | ISBN-10: 0470474874 | ISBN-13: 978-0470474877 | Edition: 1
Excel 2007 Formulas by John Walkenbach
Publication Date: February 12, 2007 | ISBN-10: 0470044020 | ISBN-13: 978-0470044025
Excel 2010 Formulas by John Walkenbach
Publication Date: May 24, 2010 | ISBN-10: 0470475366 | ISBN-13: 978-0470475362 | Edition: 1
Learn Excel 2007 Expert Skills with The Smart Method: Courseware Tutorial teaching Advanced Techniques by Mike Smart
Publication Date: September 11, 2009 | ISBN-10: 0955459931 | ISBN-13: 978-0955459931 | Edition: 1
Microsoft® Office Excel® 2007: Data Analysis and Business Modeling by Wayne L. Winston
Publication Date: May 16, 2007 | ISBN-10: 0735623961 | ISBN-13: 978-0735623965
Microsoft Excel 2010 In Depth by Bill Jelen
Publication Date: July 5, 2010 | ISBN-10: 0789743086 | ISBN-13: 978-0789743084 | Edition: 1
Cheers
or do pivot table on the list it is much faster and easier
Rona,
you are right! Sometimes you just want to examine the values having duplicates and then take the appropriate actions.
Congratulations for your job, Oscar.
How I can modify the formula to get to make disappear B2(DAVID) because the A1 and D2 have the same value
1/ A B D
2/ 2 DAVID 2
3/ 5 PETER 3
The formula that I use right now is this one:
[=SI.ERROR(INDICE(Devos!$A$2:$A$9999;COINCIDIR(0;CONTAR.SI($B$1:B1;Devos!$A$2:$A$9999);0));"")]
Thanks in advance
(Sorry,this way is understand better)
Congratulations for your job, Oscar.
How I can modify the formula to get to make disappear B2(DAVID) because the A2 and D2 have the same value
1/---A-----B-----D
2/---2---DAVID---2
3/---5---PETER---3
The formula that I use right now is this one:
[=IFERROR(INDEX($A$2:$A$9999;MATCH(0;COUNTIF($B$1:B1;$A$2:$A$9999);0));"")]
Thanks in advance
Jan,
See attached file:
Jan.xlsx
Firstly, great resource, fantastic work Oscar.
Quick question I have three lists of names in the same sheet and I need to pull them into three different distinct lists. I assume All i need to do is reference the correct fields E.G.
=INDEX(Primary,MATCH(0,IF(ISBLANK(Primary),"",COUNTIF($Q$1:Q1,Primary)),0))
=INDEX(Secondary,MATCH(0,IF(ISBLANK(Secondary),"",COUNTIF($Q$12:Q12,Secondary)),0))
The Primary list returns ok, the Seconday list retuns 0.
I can work with the Primary list for now as I'm trying to lookup a beginging date and end date based on the distinc list but am I doning something wrong with my secondary list?
Nick,
You have to enter this array formula in cell Q13 for this to work. You also have to copy the cell Q13 (not the formula) and paste it down as far as needed. Make sure your named range Secondary is correct.
Hi; Thank you for this article. Trying to better my Excel skill, and this topic is one I have been trying to get down for awhile.
I've got your file, and at the very early stage, I am getting #N/A:
=COUNTIF($B$1:B1,List) returns a value of "0" as it is supposed to. But when I proceed to the next step, which is
=MATCH(0,COUNTIF($B1:B1,List),0)returns #N/A and I cant figure out why. Maybe it is a rookie error?
Thank you for you help!
Versch,
Array formula:
=MATCH(0,COUNTIF($B1:B1,List),0)
1. Select cell B2
2. Paste formula
3. Press and hold Ctrl + Shift
4. Press Enter
Hi Oscar,
Thank you for the update. You are correct, I had not made B2 into an array formula, which I now did as per your instruction. However, my
=MATCH(0,COUNTIF($B1:B1,List),0)
returns #N/A again.
Thank you; I will try re-engineering this till it works right.
I had a two coloumn, one coloumn have fifteen records and another have 100 records. both coloumn does't have the same records. i want to merge the both coloumn means i want to add that fifteen records against of against of 100 records (100*15) but i am not able to do that. For e.x. Column A-15 Unique Records and Column B has 100 Unique records and both are not matchning. I want to merge the both records (100*15) and make a single records in every row without missing any single records. Please suggest how to do that.
Aman Kr Tadneja,
Perhaps you are looking for this post:
Extract a unique distinct list from two columns using excel 2007 array formula
Hello Oscar,
Just wanted to leave a thank you note for the countless number of times your formulae have helped me get unique lists faster. I have been through all the "unique-list" posts on your site and have found them to be very useful. Thanks and keep up the good work!!
Playercharlie,
thanks for commenting!
The issue with #N/A is if you're using a named list that concludes a whole column. I had the same issue and could not figure out what iw as doing wrong as I kept getting #N/A or zero no matter what I tried.
This is giving me an error when I try to use it with numbers. I am following your syntax exactly.
Excel 2010.
Am I doing something wrong?
William,
the attached file works here (excel 2010).
What is the error message? You can upload the file here if you want me to look at it.
Oscar, this is good stuff. Wondering if you could help me take this a step further. I have a named range called "Date" in column C which is dynamically pulling from another data source with a few other columns of data. I would like to use VBA to create a unique list in another dynamic column range called "Date2".
If I may, additionally I would like to do a sumif function in Table2 (which contains Date2 in one column and Cost2 in the column next to it) in Cost2 all of the costs in column Cost (pulled in from the aformentioned query) if the date in Date2 matches Date. I was hoping to keep all of this dynamic so I can have a chart which shows cost over time automatically when I press with left mouse button on the button. Can this be done?
|Date | Cost | Date2 | Cost 2|
7/26/2015 $5
7/26/2015 $6
8/1/2015 $7
9/1/2015 $8