VBA Data Validation for multiple values

[Krazy]

Hi all,

Thank you very much for your help in advance!

(Thanks a lot for hiker95's help in another thread for building the database, http://www.mrexcel.com/forum/excel-...cations-transpose-column-row.html#post4430791)

Everyday, my colleagues will input raw data and recently my boss assigned me to check those raw data entered to see whether they are in our database and detect if there are abnormalities (those not in the database)

I was using Match and Vlookup functions for other tasks, but it doesn't work for the new one as there could be more than one value (up to 40values so far) for a single item.


Column A&B are the database, Column D&E are data entered by colleagues and I have to validate them in Column F&G, but match function doesn't work for cases like 2 with values B/C/D/E/F, even if the colleagues entered value C, it can't judge whether it's correct or wrong. Is there any way I could check that the value entered is in the range with reference to column A&B?

Other than that, if the colleague entered item is not on my database, I have to mark a warning message, e.g. my database only covers item 1-5, and the colleague entered 6, then it could not be found. Vlookup function could do, but I want to automate the whole process (VBA) as I am doing it routinely for very large amount of items.

Thank you very much!!

[TABLE="class: grid, width: 1000, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Database (Item)[/TD]
[TD]Database (Value)[/TD]
[TD][/TD]
[TD]Raw Data entered by colleagues
(Need to check) (Item)[/TD]
[TD]Value Entered
(Need to be verify with the database)[/TD]
[TD]Get Valid list[/TD]
[TD]Matching
(only work for those with one value now, how could I make it work for several values?)[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1[/TD]
[TD]A[/TD]
[TD][/TD]
[TD]2[/TD]
[TD]C[/TD]
[TD]=VLOOKUP(D1,A:B,2,0)[/TD]
[TD]=MATCH(E1,F1,-1) //<--This one failed, even though C is among the values of item 2//
[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]2[/TD]
[TD]B C D E F[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]A[/TD]
[TD]=VLOOKUP(D2,A:B,2,0)
[/TD]
[TD]=MATCH(E2,F2,0) //<--This one works//
[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]3[/TD]
[TD][/TD]
[TD]this item without value, or "empty" is its value[/TD]
[TD]3
[/TD]
[TD][/TD]
[TD]=VLOOKUP(D3,A:B,2,0)
[/TD]
[TD]=MATCH(E3,F3,0) //<--This one works//
[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]4[/TD]
[TD]A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[DILIPandey]

=MATCH(E1,F1,-1) //<--This one failed, even though C is among the values of item 2//

I guess instead of Match function, you should use search or find function as you are looking for C in a text string of B C D E F.


Regards,
DILIPandey

 

[Krazy]

Omg, thanks a lot, didn't know there's "find" function. You saved my day!

 

[DILIPandey]

You are welcome Krazy..... cheers



Regards,
DILIPandey

 

[Krazy]

Hey Pandey,

Sorry to bother you again, I found a bug with find function which may hinders my checking

if I need to find 8 in the whitelist, see below -

[TABLE="class: grid, width: 319"]
<tbody>[TR]
[TD]Whitelist[/TD]
[TD]Value need to be checked[/TD]
[TD]Find result[/TD]
[/TR]
[TR]
[TD]18 28 38 8[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]2[/TD]
[/TR]
</tbody>[/TABLE]

It treated 18 as 8 and said it found it at char "2", which it should not treat 18 as 8, how could this be solved?

Thank you very much in advance.

 

[mubashiraziz]

Hey Pandey,

Sorry to bother you again, I found a bug with find function which may hinders my checking

if I need to find 8 in the whitelist, see below -

[TABLE="class: grid, width: 319"]
<tbody>[TR]
[TD]Whitelist[/TD]
[TD]Value need to be checked[/TD]
[TD]Find result[/TD]
[/TR]
[TR]
[TD]18 28 38 8[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]2[/TD]
[/TR]
</tbody>[/TABLE]

It treated 18 as 8 and said it found it at char "2", which it should not treat 18 as 8, how could this be solved?

Thank you very much in advance.

Try : =ISNUMBER(SEARCH(" "&E2&" "," "&F2&" "))

 

[DILIPandey]

thanks mubashiraziz,

Finding the number with spaces in beginning and end is the smart way to get the full number


Regards,
DILIPandey

 

[Krazy]

Thanks mubashiraziz, that's brilliant!!!!

Also. Pandey, thank you again!!!

 

[DILIPandey]

You are welcome Krazy.

thanks to mubashiraziz for showing the smart way



Regards,
DILIPandey

 

[mubashiraziz]

Thanks Dilipandey for the appreciation and welcome Krazy