Array Formula needed

[pagrender]

Hello,

I've been able to accomplish this before with a pivot table and vlookup, but think this can be done with an array formula.

Here is my data:
Cells in range A1:AN3218 have values in them
Column AO looks to see if someone has a "JEA" or "SCA" classification from column R.
I would like a formula in column AP to look and see if each employee has JEA or SCA anywhere in column AO. BAscially, column AP shows you want I want to see.

Can this be done?

Thanks!

Sheet1

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 11pt; FONT-FAMILY: Calibri,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 250px"><COL style="WIDTH: 145px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD></TD><TD>D</TD><TD>R</TD><TD>AO</TD><TD>AP</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="FONT-WEIGHT: bold">Name - Full Name (LFM)</TD><TD style="FONT-WEIGHT: bold">Matrix Code</TD><TD style="FONT-WEIGHT: bold; COLOR: #ff0000">Union1</TD><TD style="FONT-WEIGHT: bold; COLOR: #ff0000">Union2</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">18</TD><TD>ADLER, JODI L</TD><TD>1011JEA</TD><TD>JEA</TD><TD style="BACKGROUND-COLOR: #ffff00">JEA</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">19</TD><TD>ADLER, JODI L</TD><TD>CURR</TD><TD></TD><TD style="BACKGROUND-COLOR: #ffff00">JEA</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">20</TD><TD>ADLER, JODI L</TD><TD>CURR</TD><TD></TD><TD style="BACKGROUND-COLOR: #ffff00">JEA</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">21</TD><TD>BLOCK, SHELLEY M</TD><TD>CURR</TD><TD></TD><TD style="BACKGROUND-COLOR: #ffff00">JEA</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">22</TD><TD>CAMPBELL, LAURIE C</TD><TD>1011JEA</TD><TD>JEA</TD><TD style="BACKGROUND-COLOR: #ffff00">JEA</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">23</TD><TD>CAMPBELL, LAURIE C</TD><TD>CURR</TD><TD></TD><TD style="BACKGROUND-COLOR: #ffff00">JEA</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3219</TD><TD></TD><TD></TD><TD></TD><TD></TD></TR></TBODY></TABLE>
<TABLE style="FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00; BORDER-BOTTOM-COLOR: #00ff00; COLOR: #000000; BORDER-TOP-STYLE: groove; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-RIGHT-STYLE: groove; BORDER-LEFT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-RIGHT-COLOR: #00ff00; BORDER-BOTTOM-STYLE: groove"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-SIZE: 9pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=2 border=1><TBODY><TR style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #cacaca"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>AO18</TD><TD>=IF(RIGHT(R18,3)="SCA","SCA",IF(RIGHT(R18,3)="JEA","JEA",""))</TD></TR><TR><TD>AO19</TD><TD>=IF(RIGHT(R19,3)="SCA","SCA",IF(RIGHT(R19,3)="JEA","JEA",""))</TD></TR><TR><TD>AO20</TD><TD>=IF(RIGHT(R20,3)="SCA","SCA",IF(RIGHT(R20,3)="JEA","JEA",""))</TD></TR><TR><TD>AO21</TD><TD>=IF(RIGHT(R21,3)="SCA","SCA",IF(RIGHT(R21,3)="JEA","JEA",""))</TD></TR><TR><TD>AO22</TD><TD>=IF(RIGHT(R22,3)="SCA","SCA",IF(RIGHT(R22,3)="JEA","JEA",""))</TD></TR><TR><TD>AO23</TD><TD>=IF(RIGHT(R23,3)="SCA","SCA",IF(RIGHT(R23,3)="JEA","JEA",""))</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

 

[pagrender]

My bad... cell AP21 should NOT have had a "JEA" in it.

Sheet1

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 11pt; FONT-FAMILY: Calibri,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 250px"><COL style="WIDTH: 145px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>D</TD><TD>R</TD><TD>AO</TD><TD>AP</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="FONT-WEIGHT: bold">Name - Full Name (LFM)</TD><TD style="FONT-WEIGHT: bold">Matrix Code</TD><TD style="FONT-WEIGHT: bold; COLOR: #ff0000">Union1</TD><TD style="FONT-WEIGHT: bold; COLOR: #ff0000">Union2</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">18</TD><TD>ADLER, JODI L</TD><TD>1011JEA</TD><TD>JEA</TD><TD style="BACKGROUND-COLOR: #ffff00">JEA</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">19</TD><TD>ADLER, JODI L</TD><TD>CURR</TD><TD> </TD><TD style="BACKGROUND-COLOR: #ffff00">JEA</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">20</TD><TD>ADLER, JODI L</TD><TD>CURR</TD><TD> </TD><TD style="BACKGROUND-COLOR: #ffff00">JEA</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">21</TD><TD>BLOCK, SHELLEY M</TD><TD>CURR</TD><TD> </TD><TD style="BACKGROUND-COLOR: #ffff00"> </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">22</TD><TD>CAMPBELL, LAURIE C</TD><TD>1011JEA</TD><TD>JEA</TD><TD style="BACKGROUND-COLOR: #ffff00">JEA</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">23</TD><TD>CAMPBELL, LAURIE C</TD><TD>CURR</TD><TD> </TD><TD style="BACKGROUND-COLOR: #ffff00">JEA</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3219</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR></TBODY></TABLE>
<TABLE style="FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00; BORDER-BOTTOM-COLOR: #00ff00; COLOR: #000000; BORDER-TOP-STYLE: groove; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-RIGHT-STYLE: groove; BORDER-LEFT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-RIGHT-COLOR: #00ff00; BORDER-BOTTOM-STYLE: groove"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-SIZE: 9pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=2 border=1><TBODY><TR style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #cacaca"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>AO18</TD><TD>=IF(RIGHT(R18,3)="SCA","SCA",IF(RIGHT(R18,3)="JEA","JEA",""))</TD></TR><TR><TD>AO19</TD><TD>=IF(RIGHT(R19,3)="SCA","SCA",IF(RIGHT(R19,3)="JEA","JEA",""))</TD></TR><TR><TD>AO20</TD><TD>=IF(RIGHT(R20,3)="SCA","SCA",IF(RIGHT(R20,3)="JEA","JEA",""))</TD></TR><TR><TD>AO21</TD><TD>=IF(RIGHT(R21,3)="SCA","SCA",IF(RIGHT(R21,3)="JEA","JEA",""))</TD></TR><TR><TD>AO22</TD><TD>=IF(RIGHT(R22,3)="SCA","SCA",IF(RIGHT(R22,3)="JEA","JEA",""))</TD></TR><TR><TD>AO23</TD><TD>=IF(RIGHT(R23,3)="SCA","SCA",IF(RIGHT(R23,3)="JEA","JEA",""))</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

 

[GlennUK]

How about:

=IF(RIGHT(R18,3)="SCA","SCA",IF(RIGHT(R18,3)="JEA","JEA",IF(D18=D17,AO17,"")))

?

 

[markmzz]

If column A is sorted, try this:

<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD>Name - Full Name (LFM)</TD><TD>Matrix Code</TD><TD>Union1</TD><TD>Union2</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD>ADLER, JODI L</TD><TD>1011JEA</TD><TD>JEA</TD><TD>JEA</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD>ADLER, JODI L</TD><TD>CURR</TD><TD style="TEXT-ALIGN: right"></TD><TD>JEA</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD>ADLER, JODI L</TD><TD>CURR</TD><TD style="TEXT-ALIGN: right"></TD><TD>JEA</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD>BLOCK, SHELLEY M</TD><TD>CURR</TD><TD style="TEXT-ALIGN: right"></TD><TD></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">6</TD><TD>CAMPBELL, LAURIE C</TD><TD>1011JEA</TD><TD>JEA</TD><TD>JEA</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">7</TD><TD>CAMPBELL, LAURIE C</TD><TD>CURR</TD><TD style="TEXT-ALIGN: right"></TD><TD>JEA</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">8</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">9</TD><TD>**********************</TD><TD>***********</TD><TD>*******</TD><TD>*******</TD></TR></TBODY></TABLE>


<TABLE style="BORDER-BOTTOM: black 2px solid; BORDER-LEFT: black 2px solid; PADDING-BOTTOM: 0.4em; BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 0.4em; PADDING-RIGHT: 0.4em; BORDER-COLLAPSE: collapse; BORDER-TOP: black 2px solid; BORDER-RIGHT: black 2px solid; PADDING-TOP: 0.4em" rules=all cellPadding=2 width="85%"><TBODY><TR><TD style="PADDING-BOTTOM: 6px; PADDING-LEFT: 6px; PADDING-RIGHT: 6px; PADDING-TOP: 6px">Worksheet Formulas<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; TEXT-ALIGN: center; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2 width="100%"><THEAD><TR style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH width=10>Cell</TH><TH style="TEXT-ALIGN: left; PADDING-LEFT: 5px">Formula</TH></TR></THEAD><TBODY><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>D2</TH><TD style="TEXT-ALIGN: left">=IF(A2=A1,D1,IF(C2="","",C2))</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>


Markmzz

 

[pagrender]

I'm testing the sorting method right now.... thanks for the suggestion!


Done testing... As far as I can tell... it worked! Thanks!

 

[markmzz]

I'm testing the sorting method right now.... thanks for the suggestion!


Done testing... As far as I can tell... it worked! Thanks!

Thanks for the feedback.

Markmzz