VBA check cell contents against Array and return results if found/not found

[rmfrase]

I had initially attempted to use an =if(or(A2="Name1",A2="name2",A2="Name3",,,,,,,),"Department1","Department2")

However, I had so many names, that the formula was far too large.

Any suggestions on how to set up an Array to see if the Cell Contents (ie: names) is found in the Array, and if so, give results of "department1", "Department2" or "Not Found"


Thank you for your assistance.

 

[dispelthemyth]

I had initially attempted to use an =if(or(A2="Name1",A2="name2",A2="Name3",,,,,,,),"Department1","Department2")

However, I had so many names, that the formula was far too large.

Any suggestions on how to set up an Array to see if the Cell Contents (ie: names) is found in the Array, and if so, give results of "department1", "Department2" or "Not Found"


Thank you for your assistance.

Is it logical in that

Name1 = Department1
Name2 = Department2

etc


Or will they be real names?
if they are can you advise how the deparment is found

 

[hiker95]

rmfrase,

What version of Excel and Windows are you using?

Can you post a screenshot of the actual raw data worksheet?

And, can you post a screenshot of the worksheet results (manually formatted by you) that you are looking for?

To post your data, you can download and install one of the following two programs:
Excel Jeanie
MrExcel HTML Maker

Or, when using Internet Explorer, just put borders around your data in Excel and copy those cells into your post.
See reply #2 the BLUE text in the following link:
http://www.mrexcel.com/forum/about-board/444901-how-create-table-like-aladin.html#post2198045


If you are not able to give us screenshots:
You can upload your workbook to Box Net,
sensitive data changed
mark the workbook for sharing
and provide us with a link to your workbook.

 

[rmfrase]

They are real names, and the department is based on the name.

 

[rmfrase]

Excel version is 2010. I'll have to work on being able to post a screenshot.

 

[rmfrase]

@Dispelthemyth, To answer your question better than I did previously, I would have a list of individuals (about 50 or so) that would be in a department. There would be about 4 other lists of individuals and those (4) groups would be in their own respective departments.

Basically, i'm attempting to check each row in the [A] Column to see if that individual is in any of the (5) groups. If so, I would need to have the macro provide the department for that person. If not found, I would need it to indicate that as well.

Naturally, I'm attempting to solve this myself, but any suggestions is greatly appreciated.

 

[dispelthemyth]

Can you create some dummy data and provide the logic behind how you decide the department

@Dispelthemyth, To answer your question better than I did previously, I would have a list of individuals (about 50 or so) that would be in a department. There would be about 4 other lists of individuals and those (4) groups would be in their own respective departments.

Basically, i'm attempting to check each row in the [A] Column to see if that individual is in any of the (5) groups. If so, I would need to have the macro provide the department for that person. If not found, I would need it to indicate that as well.

Naturally, I'm attempting to solve this myself, but any suggestions is greatly appreciated.

 

[rmfrase]

here's some dummy data:

Names found in the [A] Column
D Jones
S Davis
T Byrd
L Evers
Q Ferrell
A Taylor
D Austin
T Ghalager
D Andrews
H Young
O Emanuel
K Garrett
T Gray
W Hamilton
R Hill
R Jeffries
D Markham
E Milstead
B Mitchell
A Overton
S Smtih
E Vargas
P Vaughan
M York
E Stafford
Z Levesque
N Anthony
B Magno
M Davis
T Adams
S Allen
T Allen
J Allen
J Wheeler
J Hauck
J Mayweather
S Barner
M Barnett
M Billinsgley
D Blount
D Boxell
O Braithwaite
E Brown
V Brown
Q Brubeck
Y Bryant
Y Burrell
H Butler
R Byas
H Byrd
G Carey
N Carr
R Carrigan
W Carter
J Cash
T Chapin
B Christian
O Coe
C Cole
D Davidson
M Dehart
D Doone
H Duron
G Eskra
I Esopa
C Rogers
K Florence
J Gaffney
H Graves
B Gray
C Green
J Greene
A Gunn
S Ferrell
A Coselle
W Harrelson
S Truste
H Smith



Here's the formula I was working with:
'=IF(OR(A2="A Jones",A2="M Davis",A2="S Byrd",A2="K Evers",A2="V Ferrell",A2="S Taylor"),"Department_A",IF(OR(A2="S Austin",A2="R Ghalager",A2="C Andrews",A2="J Young",A2="G Emanuel",A2="J Garrett",A2="S Gray",A2="V Hamilton",A2="T Hill",A2="S Jeffries",A2="P Markham",A2="D Milstead",A2="C Mitchell",A2="D Overton",A2="R Smtih",A2="J Vargas",A2="D Vaughan",A2="L York",A2="R Stafford",A2="E Levesque"),"Department_B",
IF(OR(A2="V Anthony",A2="A Magno",A2="B Davis"),"Department_C",
IF(OR(A2="S Adams",A2="R Allen",A2="S Allen",A2="J Allen",A2="J Wheeler",A2="J Hauck",A2="J Mayweather",A2="S Barner",A2="M Barnett",A2="M Billinsgley",A2="D Blount",A2="D Boxell",A2="O Braithwaite",A2="E Brown",A2="V Brown",A2="P Brubeck",A2="J Bryant",A2="W Burrell",A2="G Butler",A2="Q Byas",A2="E Byrd",A2="P Carey",A2="M Carr",A2="L Carrigan",A2="D Carter",A2="O Cash",A2="W Chapin",A2="L Christian",A2="N Coe",A2="B Cole",A2="D Davidson",A2="T Dehart",A2="A Doome",A2="N Duron",A2="C Eskra",A2="J Esopa",A2="C ",A2="K Florentino",A2="J Gaffney",A2="H Graves",A2="B Gray",A2="C Green",A2="J Greene",A2="A Gunn",A2="S Hainsfurth",A2="A Hanawalt",A2="L Harrelson"),"Department_D",
IF(OR(A2="S Collins",A2="C El"),"Department_E",
IF(A2="S Truste",(IF(A2="H Smith","Department_F")),"Not Found"))))))

I added the ' in the 1st character of the formula as to not mess anything up while posting.
I also removed several hundred names to make it easier to see.

 

[dispelthemyth]

Wow at manually typing the names into the If formula

But seriously, where is the data coing from is it being extracted from a system or have you manually collated each persons department?

 

[rmfrase]

It's coming from a system, and exported out onto an Excel Spreadsheet.