Hi i need a formula please

[john_cloudstrong]

Hi all,

I need a formula for this please it took a while to narrow it down to this version i don't think i can word it any better. I have had Akashwani help me in this post but he is busy now so he said to re post it.http://www.mrexcel.com/forum/excel-questions/685321-i-need-formula-please-2.html#post3392878


So here is example one. So if POSITION is Either IT Manager or Head of IT (marked in green). Then they fit the first criteria.

So if they fit the first criteria then in BLUE the second criteria we need is everyone else who works under that company name (TITLE) that matches the first criteria i hope this makes it clear.

Sheet1

[TABLE="class: cms_table"]
<tbody>[TR="bgcolor: #cacaca"]
[TD]*[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD]TITLE[/TD]
[TD]POSITION[/TD]
[TD]POSGROUP[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="bgcolor: #0066cc"]FAS Shannon Training Centre & Employment Services[/TD]
[TD="bgcolor: #99cc00"]IT Manager[/TD]
[TD]Head of IT[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="bgcolor: #0066cc"]Finsa Forest Products Limited[/TD]
[TD]Managing Director[/TD]
[TD]CEO/Managing Director[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="bgcolor: #0066cc"]Finsa Forest Products Limited[/TD]
[TD="bgcolor: #99cc00"]IT Manager[/TD]
[TD]Head of IT[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD]Hertz Rent a Car[/TD]
[TD]Head of Personnel[/TD]
[TD]Head of IT[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD]Hogan Motors Limited[/TD]
[TD]Dealer Principal[/TD]
[TD]CEO/Managing Director[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]7[/TD]
[TD="bgcolor: #0066cc"]Michael Houlihan & Partners[/TD]
[TD="bgcolor: #99cc00"]Head of IT[/TD]
[TD]Head of IT[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]8[/TD]
[TD="bgcolor: #0066cc"]Michael Houlihan & Partners[/TD]
[TD]Financial Controller[/TD]
[TD]Head of Finance[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]9[/TD]
[TD]Munster Group Insurance (PayLess Direct)[/TD]
[TD]Managing Director[/TD]
[TD]CEO/Managing Director[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]10[/TD]
[TD="bgcolor: #0066cc"]O'Sullivan & Hansbury Motors Limited[/TD]
[TD]Director[/TD]
[TD]CEO/Managing Director[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]11[/TD]
[TD="bgcolor: #0066cc"]O'Sullivan & Hansbury Motors Limited[/TD]
[TD]General Manager[/TD]
[TD]Head of Finance[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]12[/TD]
[TD="bgcolor: #0066cc"]O'Sullivan & Hansbury Motors Limited[/TD]
[TD="bgcolor: #99cc00"]Head of IT[/TD]
[TD]Head of IT[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]13[/TD]
[TD="bgcolor: #0066cc"]Shannon College of Hotel Management[/TD]
[TD]Director[/TD]
[TD]CEO/Managing Director[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]14[/TD]
[TD="bgcolor: #0066cc"]Shannon College of Hotel Management[/TD]
[TD="bgcolor: #99cc00"]Head of IT[/TD]
[TD]Head of IT[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]15[/TD]
[TD]T Shiels & Company Limited[/TD]
[TD]Managing Director[/TD]
[TD]CEO/Managing Director[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]16[/TD]
[TD]A R Brownlow Limited[/TD]
[TD]Managing Director[/TD]
[TD]Head of Finance[/TD]
[/TR]
</tbody>[/TABLE]



Excel tables to the web >> Excel Jeanie HTML 4

So here is the end result. That should make the formula smaller and easier

Sheet1

[TABLE="class: cms_table"]
<tbody>[TR="bgcolor: #cacaca"]
[TD]*[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD]TITLE[/TD]
[TD]POSITION[/TD]
[TD]POSGROUP[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="bgcolor: #0066cc"]FAS Shannon Training Centre & Employment Services[/TD]
[TD="bgcolor: #99cc00"]IT Manager[/TD]
[TD]Head of IT[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="bgcolor: #0066cc"]Finsa Forest Products Limited[/TD]
[TD]Managing Director[/TD]
[TD]CEO/Managing Director[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="bgcolor: #0066cc"]Finsa Forest Products Limited[/TD]
[TD="bgcolor: #99cc00"]IT Manager[/TD]
[TD]Head of IT[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="bgcolor: #0066cc"]Michael Houlihan & Partners[/TD]
[TD="bgcolor: #99cc00"]Head of IT[/TD]
[TD]Head of IT[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="bgcolor: #0066cc"]Michael Houlihan & Partners[/TD]
[TD]Financial Controller[/TD]
[TD]Head of Finance[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]7[/TD]
[TD="bgcolor: #0066cc"]O'Sullivan & Hansbury Motors Limited[/TD]
[TD]Director[/TD]
[TD]CEO/Managing Director[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]8[/TD]
[TD="bgcolor: #0066cc"]O'Sullivan & Hansbury Motors Limited[/TD]
[TD]General Manager[/TD]
[TD]Head of Finance[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]9[/TD]
[TD="bgcolor: #0066cc"]O'Sullivan & Hansbury Motors Limited[/TD]
[TD="bgcolor: #99cc00"]Head of IT[/TD]
[TD]Head of IT[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]10[/TD]
[TD="bgcolor: #0066cc"]Shannon College of Hotel Management[/TD]
[TD]Director[/TD]
[TD]CEO/Managing Director[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]11[/TD]
[TD="bgcolor: #0066cc"]Shannon College of Hotel Management[/TD]
[TD="bgcolor: #99cc00"]Head of IT[/TD]
[TD]Head of IT[/TD]
[/TR]
</tbody>[/TABLE]