Hi,
how can i avoid duplicates in data validation list from dynamic table?
As data validation list source i have:
Screenshot_44.pngPlease Login or Register to view this content.
Please help,
Jacek
Hi,
how can i avoid duplicates in data validation list from dynamic table?
As data validation list source i have:
Screenshot_44.pngPlease Login or Register to view this content.
Please help,
Jacek
You'd have to use code to create a unique list in another range. If you are certain that the list won't ever exceed 255 characters, you could also use a delimited string instead.
Rory
You don't have to use code - it can be done with a formula if you prefer.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
True, if you don't mind overloading a sheet with array formulas sufficient to cover any length of table. Or you could use PQ as long as you remember to refresh.
Rory - it doesn't require an array formula.
Do tell then.
Hi,
thank you.
VBA code - only as the last solution.
oo, it would be great to have this in formula.
Best,
Jacek
Jacek - please provide a sample workbook and I will give you a solution using a non-array formula.
In attachment
No, not a ZIP file - please upload a workbook (.xlsx). It does not need to be your entire dataset - just a small sample workbook.
Oh, well - never mind. I'm sorry, but I won't open a .zip file and I don't have time to create a mock-up from scratch. Your choice.
Omg.
Zip is allowed here so i uploaded.
Xlsx in attachment.
Book1.xlsx
ZIP is allowed here, yes, but I won't open ZIP files - I explained why in my PM to you. I will have a look at your file now.
OK. In F2 (this column can be hidden, or the list can be placed on another tab):
=IFERROR(INDEX(t_table[Tab1],MATCH(0,INDEX(COUNTIF($F$1:F1,t_table[Tab1]),0,0),0)),"")
Copy down as far as you need.
In the data validation box:
=INDIRECT("$F$2:$F"&MATCH(99^99,$F:$F))
Was it the Bard who said "an array formula by any other name would be as computationally expensive"?
Rory - all formulae with arrays in them are array formulae. None of these require entering with CTRL+SHIFT+ENTER, however, which is what I thought you were referring to. Stop trying to score points - it's not very helpful to the OP.
Last edited by AliGW; 08-13-2018 at 12:01 PM.
Thank you,
nice!
can you explaing the result (=5) for below code?:
What is 99^99 (result = 3,69729637649726E+197) here?Please Login or Register to view this content.
Best,
Jacek
Yes, of course!
MATCH(99^99;$F:$F)
99^99 is just a very large number - a number that will be greater than any number in column F. As long as it is greater than any number in that column, it will return the last row with a number in it as a match. In this case, it is whichever is the row of the last cell in the list to contain a value. If you add more values and the lookup list grows, then the row number returned here will also grow, and because of that, your lookup list will grow.
Last edited by AliGW; 08-13-2018 at 12:50 PM.
thank you for explanation,
Best,
Jacek
You're welcome!
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
One more thing.
How to sort this?
Best,
Jacek
That's not just one little extra - it's a whole new ball game!!! Why didn't you say this at the start?
Back to the drawing board ...
Will these only ever be numeric values?
Hi AliGW,
sorry i didnt know about it. Tomorrow morning (Warsaw Time) i found out about this.
It can be numeric or alphabetic...
Best,
Jacek
Oh, dear - OK, well you will need to give me time to work through it. As I said, it needs a whole new approach.
Can you give me some properly representative data to work with? Are we talking about a mix of numeric and alphabetic in one list?
This will definitely require array entered formulae (that use CTRL+SHIFT+ENTER), I am afraid.
Last edited by AliGW; 08-14-2018 at 02:51 AM.
As you have started a new thread for the sorted list, I am marking this thread as solved. I notice that in that thread you have also ditched everything I have done for you here. I hope that others will be able to guide you to a solution, but I'm bowing out now.
For anyone interested in helping, the new thread is here: https://www.excelforum.com/excel-pro...mic-table.html
No further posts should be made to this thread. Thanks.
AliGW,
this is about formula, other topic is about VBA. I have to know 2 solutions.
I can open new topic for sorting lists and getting unique values.
Best,
Jacek
No, I'm sorry - you can have only one thread per issue and get whatever help you need with it there, in one thread, both formulae and VBA. Don't open any further threads.
This thread is now closed.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks