Ignore blanks & duplicates in data validation
- Thread starter ALAMZEB
- Start date
Smallman
Excel Ninja
Hi Alamzeb
Why don't you just copy the data to another location using the advanced filter. Copy unique records only and sort in Ascending order. That way your blanks disappear. Then base your data validation on the new list.
This would be the easiest way I can think of.
Take care
Smallman
Why don't you just copy the data to another location using the advanced filter. Copy unique records only and sort in Ascending order. That way your blanks disappear. Then base your data validation on the new list.
This would be the easiest way I can think of.
Take care
Smallman
Karthik Thandapani
Member
Hi Alamzeb,
As suggested above, you can make use of the advanced filter module and put the sheet to veryhidden once done. I usually get the desired result this way.
If you go for array formula, that may eventually slow down the calculation process, more the enormous data you put in.
As suggested above, you can make use of the advanced filter module and put the sheet to veryhidden once done. I usually get the desired result this way.
If you go for array formula, that may eventually slow down the calculation process, more the enormous data you put in.
ashish mehra
Active Member
Hi Alamzeb,
Post a sample file & you will get the answer.
Regards,
AM
Post a sample file & you will get the answer.
Regards,
AM
Smallman
Excel Ninja
Hi Alamzeb
Yes the hidden column can be changed every time you update your list. This is an example for demonstration purposes.
If you put some data in A15 you will notice your list in Col H updates. So all you have to do is apply this to your scenario and hide the relevant column.
Take care
Smallman
Yes the hidden column can be changed every time you update your list. This is an example for demonstration purposes.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, [A1:A100]) Is Nothing Then
[A1:A100].AdvancedFilter 2, , [H1], True
[H2:H100].Sort [H2], xlAscending
End If
End SubTake care
Smallman
Smallman
Excel Ninja
Alamzeb
If you are after a formula throw this in the row you are wanting your data to return a result
=IFERROR(INDEX($A$2:$A$54, MATCH(0, IF(ISBLANK($A$2:$A$54), 1, COUNTIF(N1:$N$1, $A$2:$A$54)), 0)),"")
It is an array formula so CTRL shift Enter. Put it in N2.
Take care
Smallman
If you are after a formula throw this in the row you are wanting your data to return a result
=IFERROR(INDEX($A$2:$A$54, MATCH(0, IF(ISBLANK($A$2:$A$54), 1, COUNTIF(N1:$N$1, $A$2:$A$54)), 0)),"")
It is an array formula so CTRL shift Enter. Put it in N2.
Take care
Smallman
Smallman
Excel Ninja
Hi Alamzeb
This bit
I think you just have to base your validation list on the cells that are not blank. You have to think a little creatively but if you have that capacity then it is pretty straight forward.
Create a dynamic Range based on Col N
=OFFSET(Sheet1!$N$2,0,0,COUNTIF(Sheet1!N:N,"?*")-1)
I called it DaList
See file for workings. Notice the cell in Blue is working nicely. No blanks at all.
Take care
Smallman
This bit
I think you just have to base your validation list on the cells that are not blank. You have to think a little creatively but if you have that capacity then it is pretty straight forward.
Create a dynamic Range based on Col N
=OFFSET(Sheet1!$N$2,0,0,COUNTIF(Sheet1!N:N,"?*")-1)
I called it DaList
See file for workings. Notice the cell in Blue is working nicely. No blanks at all.
Take care
Smallman
jeffreyweir
Active Member
I often use a PivotTable when I want to get rid of blanks and duplicates. I don't believe it comes much simpler than that. See attached.
Last edited:
Hi Guys
Thanks for your time, above formula works.
I don’t know if I need to post new thread or can ask here
I am using below formula in data validation but cant ignore blanks because offset & counta formula doesn’t fit in data validation, is there anyway, I can ignore blanks in below formula
Thanks for your time, above formula works.
I don’t know if I need to post new thread or can ask here
I am using below formula in data validation but cant ignore blanks because offset & counta formula doesn’t fit in data validation, is there anyway, I can ignore blanks in below formula
Code:
[B]=IF(A17="WATER",WATER,IF(A17="SPORT",SPORT,IF(A17="JUICE",JUICE,IF(A17="CSD",CSD,IF(A17="CORDIAL",CORDIAL,IF(A17="CUPS_STRAWS",CUPS_STRAWS,IF(A17="EXCEPTION",EXCEPTION,IF(A17="POSTMIX",POSTMIX,MONSTER))))))))
Where below are categories
Category
WATER
SPORT
JUICE
CSD
CORDIAL
POSTMIX
MONSTER
CUPS_STRAWS
EXCEPTION
I have column A and B, if I select catehory such as WATER (A17) from drop down list (like above) than it will show me relevent data in column b17 drop down like below
WATER
Cool Ridge
Cool Ridge Sparkling 500ML
Cool Ridge 600ml
SPORT
Gatorade 560g x 6 PLASTIC JAR
Gatorade 600ml x 12 PET
And so on[/B]NARAYANK991
Excel Ninja
Hi Alamzeb ,
Two points :
1. The formula you are using seems to be overly complex ; can you check this link :
http://www.get-digital-help.com/200...cally-create-a-unique-list-and-remove-blanks/
2. A data validation list is not going to be hundreds of items long ; a formula to create such a small list is not going to make Excel sluggish ; secondly , you seem to be giving different examples at different times , I do not know why.
If you can post real-life working data , unless it is confidential , you will do yourself and those who are trying to help you , a favour.
Instead of using sample data such as Apples and Mangoes , why not give your real-life data right at the outset ; not only can a solution be suggested immediately , it would save you and everyone else a lot of trouble ; this thread is now in its 18th post , where it could have stopped after 2 or 3.
I do not know what is your ultimate objective , but it seems to me that if you have data which is static , the solution would favour one approach , while if it is dynamic , the solution would favour a different approach.
Narayan
Two points :
1. The formula you are using seems to be overly complex ; can you check this link :
http://www.get-digital-help.com/200...cally-create-a-unique-list-and-remove-blanks/
2. A data validation list is not going to be hundreds of items long ; a formula to create such a small list is not going to make Excel sluggish ; secondly , you seem to be giving different examples at different times , I do not know why.
If you can post real-life working data , unless it is confidential , you will do yourself and those who are trying to help you , a favour.
Instead of using sample data such as Apples and Mangoes , why not give your real-life data right at the outset ; not only can a solution be suggested immediately , it would save you and everyone else a lot of trouble ; this thread is now in its 18th post , where it could have stopped after 2 or 3.
I do not know what is your ultimate objective , but it seems to me that if you have data which is static , the solution would favour one approach , while if it is dynamic , the solution would favour a different approach.
Narayan
Hi Narayan
It seems you haven’t read whole thread, but just jumped to conclusion.
Yes the data was confidential and after request I did send real data but after trimming and again you may missed reading that its solved now, thanks to smallman and rest.I may have used more than one example but just to make it clearer for guys
It seems you haven’t read whole thread, but just jumped to conclusion.
Yes the data was confidential and after request I did send real data but after trimming and again you may missed reading that its solved now, thanks to smallman and rest.I may have used more than one example but just to make it clearer for guys