I need a formula please.

[john_cloudstrong]

Hi

I havnt used excel in a while i was doing formula in college but that was three years ago so i forget how to do it.

I am using excel 2007 . I need to get from an excel sheet from position ( IT Manager or Head of IT) which falls under T . Then (Head of IT ) which falls under the letter W on the top of the column.

Then I also need every one that works in these companies which are outside of IT as well. So the name of the companies falls under the letter B.


I thank you very much in advance for any help.

If t(IT manager, Head of IT )and w(Head of IT)
get B ???? Thats a really crap guess

How do i go about putting this into excel for it to be answered as well.
If i havn't worded this correctly then please let me know

Thanks again.

 

[john_cloudstrong]

HI Akashwani

I have managed to narrow it down a bit. So here is example one. So if position is Either IT Manageror Head of IT. Then they fit the first criteria.

So if they fit that then in blue the second criteria we need everyone from the company that matches the first criteria i hope this makes it clearer.

Sheet1

*	A	B	C
TITLE	POSITION	POSGROUP
Head of IT
Managing Director	CEO/Managing Director
Head of IT
Hertz Rent a Car	Head of Personnel	Head of IT
Hogan Motors Limited	Dealer Principal	CEO/Managing Director
Head of IT
Financial Controller	Head of Finance
Munster Group Insurance (PayLess Direct)	Managing Director	CEO/Managing Director
Director	CEO/Managing Director
General Manager	Head of Finance
Head of IT
Director	CEO/Managing Director
Head of IT
T Shiels & Company Limited	Managing Director	CEO/Managing Director
A R Brownlow Limited	Managing Director	Head of Finance

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:363px;"><col style="width:147px;"><col style="width:225px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="bgcolor: #0066cc"]FAS Shannon Training Centre & Employment Services[/TD]
[TD="bgcolor: #99cc00"]IT Manager[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="bgcolor: #0066cc"]Finsa Forest Products Limited[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="bgcolor: #0066cc"]Finsa Forest Products Limited[/TD]
[TD="bgcolor: #99cc00"]IT Manager[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]
[TD="bgcolor: #0066cc"]Michael Houlihan & Partners[/TD]
[TD="bgcolor: #99cc00"]Head of IT[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]
[TD="bgcolor: #0066cc"]Michael Houlihan & Partners[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]
[TD="bgcolor: #0066cc"]O'Sullivan & Hansbury Motors Limited[/TD]

[TD="bgcolor: #cacaca, align: center"]11[/TD]
[TD="bgcolor: #0066cc"]O'Sullivan & Hansbury Motors Limited[/TD]

[TD="bgcolor: #cacaca, align: center"]12[/TD]
[TD="bgcolor: #0066cc"]O'Sullivan & Hansbury Motors Limited[/TD]
[TD="bgcolor: #99cc00"]Head of IT[/TD]

[TD="bgcolor: #cacaca, align: center"]13[/TD]
[TD="bgcolor: #0066cc"]Shannon College of Hotel Management[/TD]

[TD="bgcolor: #cacaca, align: center"]14[/TD]
[TD="bgcolor: #0066cc"]Shannon College of Hotel Management[/TD]
[TD="bgcolor: #99cc00"]Head of IT[/TD]

[TD="bgcolor: #cacaca, align: center"]15[/TD]

[TD="bgcolor: #cacaca, align: center"]16[/TD]

</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

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

Sheet1

*	A	B	C
TITLE	POSITION	POSGROUP
Head of IT
Managing Director	CEO/Managing Director
Head of IT
Head of IT
Financial Controller	Head of Finance
Director	CEO/Managing Director
General Manager	Head of Finance
Head of IT
Director	CEO/Managing Director
Head of IT

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:363px;"><col style="width:147px;"><col style="width:225px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="bgcolor: #0066cc"]FAS Shannon Training Centre & Employment Services[/TD]
[TD="bgcolor: #99cc00"]IT Manager[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="bgcolor: #0066cc"]Finsa Forest Products Limited[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="bgcolor: #0066cc"]Finsa Forest Products Limited[/TD]
[TD="bgcolor: #99cc00"]IT Manager[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="bgcolor: #0066cc"]Michael Houlihan & Partners[/TD]
[TD="bgcolor: #99cc00"]Head of IT[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="bgcolor: #0066cc"]Michael Houlihan & Partners[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]
[TD="bgcolor: #0066cc"]O'Sullivan & Hansbury Motors Limited[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]
[TD="bgcolor: #0066cc"]O'Sullivan & Hansbury Motors Limited[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]
[TD="bgcolor: #0066cc"]O'Sullivan & Hansbury Motors Limited[/TD]
[TD="bgcolor: #99cc00"]Head of IT[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]
[TD="bgcolor: #0066cc"]Shannon College of Hotel Management[/TD]

[TD="bgcolor: #cacaca, align: center"]11[/TD]
[TD="bgcolor: #0066cc"]Shannon College of Hotel Management[/TD]
[TD="bgcolor: #99cc00"]Head of IT[/TD]

</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[Akashwani]

Hi John,

To be honest with you, I have no idea how to solve this, sorry.
I am unable to spend the time on this problem at the moment, but I will have a look at it later.
In the mean time, seeing as you can now post sample data and results, I would suggest you start a new post, as you may get the answer quicker than I can supply one.
Make sure that you refer to this post in your new one, otherwise it may get "locked" for being a duplicate post!

Good luck.

Ak

 

[john_cloudstrong]

Hi Akashwani

Thanks for your help anyway. I reposted it again. Thanks for taking the time to help me.

Kind Regards
John

 

[Akashwani]

Hi John.

If you can add a "Helper" column to your first sheet, then try this.....

Excel Workbook
	A	B	C	D	E
1	TITLE	POSITION	POSGROUP	Helper
2	A R Brownlow Limited	Managing Director	Head of Finance	0
3	FAS Shannon Training Centre & Employment Services	IT Manager	Head of IT	1
4	Finsa Forest Products Limited	Managing Director	CEO/Managing Director	1
5	Finsa Forest Products Limited	IT Manager	Head of IT	1
6	Hertz Rent a Car	Head of Personnel	Head of IT	0
7	Hogan Motors Limited	Dealer Principal	CEO/Managing Director	0
8	Michael Houlihan & Partners	Head of IT	Head of IT	1
9	Michael Houlihan & Partners	Financial Controller	Head of Finance	1
10	Munster Group Insurance (PayLess Direct)	Managing Director	CEO/Managing Director	0
11	O'Sullivan & Hansbury Motors Limited	Director	CEO/Managing Director	1
12	O'Sullivan & Hansbury Motors Limited	General Manager	Head of Finance	1
13	O'Sullivan & Hansbury Motors Limited	Head of IT	Head of IT	1
14	Shannon College of Hotel Management	Director	CEO/Managing Director	1
15	Shannon College of Hotel Management	Head of IT	Head of IT	1
16	T Shiels & Company Limited	Managing Director	CEO/Managing Director	0
17
John Data

The formula in D2 needs to be copied down.

Result that you require.....

Excel Workbook
	A	B	C	D	E	F	G
1	TITLE	POSITION	POSGROUP		1	10
2	FAS Shannon Training Centre & Employment Services	IT Manager	Head of IT
3	Finsa Forest Products Limited	Managing Director	CEO/Managing Director
4	Finsa Forest Products Limited	IT Manager	Head of IT
5	Michael Houlihan & Partners	Head of IT	Head of IT
6	Michael Houlihan & Partners	Financial Controller	Head of Finance
7	O'Sullivan & Hansbury Motors Limited	Director	CEO/Managing Director
8	O'Sullivan & Hansbury Motors Limited	General Manager	Head of Finance
9	O'Sullivan & Hansbury Motors Limited	Head of IT	Head of IT
10	Shannon College of Hotel Management	Director	CEO/Managing Director
11	Shannon College of Hotel Management	Head of IT	Head of IT
12
John Result

The formula in A2 needs entering with ctrl shift enter NOT just enter, it can then be copied across and down.

I hope that works for you John, if it does, please comment on your other post that the problem has been resolved.

Good luck.

Ak