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.

 

[Akashwani]

Hi John, welcome to MrExcel.

Can you post some sample data of your layout?
You can use the link in my signature to post your data.

Ak

 

[john_cloudstrong]

Im sorry i dont know how to use that link you were on about i downloaded it but it didnt work. Is there any way i can send you an attachement ??

 

[Akashwani]

Hi John,

Once you have download the file and assuming you are using 2007...
Click the Excel orb on the top left of your worksheet.
Click Excel Options.
Click Add-Ins
Click Go
Check the box for Ejhtml4_en, click Ok.

On your ribbon you should see a tab called Add-Ins, click this tab.
Highlight the data you want to post.
Click the first box on the left of the 3 boxes that are displayed on your Add-Ins tab.
Click Forum Standard (the Blue box).
You should now be able to paste your sample data.

Ak

 

[john_cloudstrong]

Thanks for your help but this is the way it came out.!! I checked that box forum standard. It did come out in a web browser as html but i cant add that can i.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1	CO_UID	BUSINESS	ADDRESS1	ADDRESS2	ADDRESS3	ADDRESS4	ADDRESS5	COUNTY_CODE	POST_CODE	TELEPHONE	NDD	FAX	FDD	EMPLOYEE	PCs	EMP_UID	TITLE	FNAME	SNAME	POSITION	SOURCE	SIC1	POSGROUP
2	181593	Boramic Limited	Fenagh Road	Borris			Co Carlow	CARL		98273498723		12341234		40	10	151837	Mr	Ken	trwert	Managing Director	wrt	35	CEO/Managing Director
3	181593	Boramic Limited	Fenagh Road	Borris			Co Carlow	CARL		2123123		12341234		40	10	193419	Ms	Carol	wtwrt	Human Resources Manager	wertw	234	Head of IT
4	181593	Boramic Limited	Fenagh Road	Borris			Co Carlow	CARL		123123123		12341234		40	10	216566	Mr	Seamus	wtwt	Financial Controller	wertw	3452	Head of Finance
5	250729	carlow	College Street				Carlow	CARL		1231324		12341234		30	20	174243	Mr	Caoimhin	wtwrt	President	wertw	234	CEO/Managing Director
6	250729	carlow	College Street				Carlow	CARL		1234123		123412341		30	20	174244	Fr	John	trtwert	Bursar	ert	324	Head of Finance
7	250729	carlow	College Street				Carlow	CARL		12341234		12341234		30	20	174245	Mr	Seamus	ertwert	IT Manager	ert	234523	Head of IT
8	48083	Carlow Security Services	Carlow Gateway Business Centre	Athy Road			Carlow	CARL		123412		1234123		40	10	138426	Mr	John	etwert	Managing Director	wertw	34	Head of IT
9	16599	Clogrennane Lime Limited	Clogrennane				Co Carlow	CARL		12341234		412341234		30	12	164226	Mr	Derry	rtwrt	Financial Controller	ertw	5	Head of IT
10	16599	Clogrennane Lime Limited	Clogrennane				Co Carlow	CARL		1234123		12341234		30	12	219295	Mr	Larry	rt	Managing Director	rtwe	234	CEO/Managing Director
11	252759	Commissioning Services Limited	Deerpark Business Complex	Dublin Road			Carlow	CARL		23452345	NDD RECORD	455234532		30	15	178203	Mr	Kieran	wertwert	Managing Director	wertw	5	CEO/Managing Director
12	252759	Commissioning Services Limited	Deerpark Business Complex	Dublin Road			Carlow	CARL		23452345	NDD RECORD	45634563456		30	15	250798	Mr	Dave	ertwret	Financial Controller	rtwe	5	Head of Finance
13	86512	Dooley Motors	Sleaty Roundabout				Carlow	CARL		23452345		23452345234		30	10	22632	Mr	Anthony	rtwrt	Managing Director	wertw	25	CEO/Managing Director
14	86512	Dooley Motors	Sleaty Roundabout				Carlow	CARL		23452345		3463456345		30	10	176151	Ms	Rowena	wt	Financial Controller	wertwe	5511	Head of IT
15	180050	engineering	Pollerton Industrial Estate	Hacketstown Road			Carlow	CARL		23452345	NDD RECORD	634563456	NDD RECORD	30	25	94400	Mr	Seamus	rtwer	Managing Director	rtwert	345	CEO/Managing Director
16	180050	engineering	Pollerton Industrial Estate	Hacketstown Road			Carlow	CARL		234523452	NDD RECORD	345634563	NDD RECORD	30	25	193837	Mr	Brendan	twrt	Finance Manager	wertw	25	Head of Finance
17	180050	engineering	Pollerton Industrial Estate	Hacketstown Road			Carlow	CARL		23452345	NDD RECORD	345634563	NDD RECORD	30	25	193838	Ms	Emer	wrtw	IT Manager	ert	2	Head of IT
18	181694	Kelly	Tullow Industrial Estate	Tullow			Co Carlow	CARL		234523452	NDD RECORD	34563456345		40	10	283724	Ms	Cathy	rtwert	Head of Finance	wert	45	Head of Finance
19	214739	Kellys of Borris	Dublin Road	Borris			Co Carlow	CARL		2345234523	NDD RECORD	45634563456		40	10	7754	Mr	Maurice	rtw	Managing Director	we	5	CEO/Managing Director
20	214739	Kellys of Borris	Dublin Road	Borris			Co Carlow	CARL		4523452345	NDD RECORD	4563456		40	10	218922	Mr	Paddy	rtwert	Financial Controller	rtwe	2	Head of Finance
21	181647	Mediplast Limited	Strawhall Industrial Estate				Carlow	CARL		23452345		56345		25	20	139934	Ms	Regina	wertwert	Managing Director	rt	5	Head of Finance
Sheet1

 

[Akashwani]

Hi John,

The sample data is fine, now that we have it, what would you like to do?

Ak

 

[john_cloudstrong]

Hi Akashwani,

It was all code i didnt think it would work but its great it did. I need to take out of column position (IT Manager and Head of IT) . To get them by formula also i need to get every one from those companies that are not in IT as well.
This is so i can pull out these contacts out of over 2000 rows.

Thanks
John

 

[Akashwani]

Hi John,

I think I understand what you require.
You will have to change the Sheet Names and cell references to suit.

Does this work for you?....

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L
1	Head of IT	6					Other	13
2	TITLE	FNAME	SNAME	POSITION	SOURCE		TITLE	FNAME	SNAME	POSITION	SOURCE
3	Ms	Carol	wtwrt	Human Resources Manager	wertw		Mr	Ken	trwert	Managing Director	wrt
4	Mr	Seamus	ertwert	IT Manager	ert		Mr	Seamus	wtwt	Financial Controller	wertw
5	Mr	John	etwert	Managing Director	wertw		Mr	Caoimhin	wtwrt	President	wertw
6	Mr	Derry	rtwrt	Financial Controller	ertw		Fr	John	trtwert	Bursar	ert
7	Ms	Rowena	wt	Financial Controller	wertwe		Mr	Larry	rt	Managing Director	rtwe
8	Ms	Emer	wrtw	IT Manager	ert		Mr	Kieran	wertwert	Managing Director	wertw
9							Mr	Dave	ertwret	Financial Controller	rtwe
10							Mr	Anthony	rtwrt	Managing Director	wertw
11							Mr	Seamus	rtwer	Managing Director	rtwert
12							Mr	Brendan	twrt	Finance Manager	wertw
13							Ms	Cathy	rtwert	Head of Finance	wert
14							Mr	Maurice	rtw	Managing Director	we
15							Mr	Paddy	rtwert	Financial Controller	rtwe
16
John Result

The formulas in A3 and G3 need entering with ctrl shift enter NOT just enter, they can then be copied across and down.

I hope that helps.

Good luck.

Ak

 

[john_cloudstrong]

Hi Akashwani,

Hi again. Im Akashwani Im sorry I worded it wrong i think we have it the wrong way around. Thanks though.

Ok so I will word this as best i can.

The end result that i need is a sheet with all the companies who have

• an IT Manager or , Head of IT in the column with POSITION on top
• Also along with those. I need all the other contacts from the companies that meet the above requirements.

Also in the formula 'John Data'!$W$2: where 'john data' is do i put the name of the excel spread sheet there ?

Thanks for your help so far.

Kind Regards

John

Hi John,

I think I understand what you require.
You will have to change the Sheet Names and cell references to suit.

Does this work for you?....

John Result

*

A

B

C

D

E

F

G

H

I

J

K

L

*

*

*

*

Other

*

*

*

*

*

*

Ms

Carol

wtwrt

Human Resources Manager

wertw

*

Mr

Ken

trwert

Managing Director

wrt

*

Mr

Seamus

ertwert

IT Manager

ert

*

Mr

Seamus

wtwt

Financial Controller

wertw

*

Mr

John

etwert

Managing Director

wertw

*

Mr

Caoimhin

wtwrt

President

wertw

*

Mr

Derry

rtwrt

Financial Controller

ertw

*

Fr

John

trtwert

Bursar

ert

*

Ms

Rowena

wt

Financial Controller

wertwe

*

Mr

Larry

rt

Managing Director

rtwe

*

Ms

Emer

wrtw

IT Manager

ert

*

Mr

Kieran

wertwert

Managing Director

wertw

*

*

*

*

*

*

*

Mr

Dave

ertwret

Financial Controller

rtwe

*

*

*

*

*

*

*

Mr

Anthony

rtwrt

Managing Director

wertw

*

*

*

*

*

*

*

Mr

Seamus

rtwer

Managing Director

rtwert

*

*

*

*

*

*

*

Mr

Brendan

twrt

Finance Manager

wertw

*

*

*

*

*

*

*

Ms

Cathy

rtwert

Head of Finance

wert

*

*

*

*

*

*

*

Mr

Maurice

rtw

Managing Director

we

*

*

*

*

*

*

*

Mr

Paddy

rtwert

Financial Controller

rtwe

*

*

*

*

*

*

*

*

*

*

*

*

*

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:69px;"><col style="width:61px;"><col style="width:62px;"><col style="width:177px;"><col style="width:64px;"><col style="width:20px;"><col style="width:64px;"><col style="width:66px;"><col style="width:65px;"><col style="width:130px;"><col style="width:47px;"><col style="width:20px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="bgcolor: #ffff00"]Head of IT[/TD]
[TD="align: right"]6[/TD]

[TD="align: right"]13[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: center"]TITLE[/TD]
[TD="align: center"]FNAME[/TD]
[TD="align: center"]SNAME[/TD]
[TD="align: center"]POSITION[/TD]
[TD="align: center"]SOURCE[/TD]

[TD="align: center"]TITLE[/TD]
[TD="align: center"]FNAME[/TD]
[TD="align: center"]SNAME[/TD]
[TD="align: center"]POSITION[/TD]
[TD="align: center"]SOURCE[/TD]

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

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

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

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

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

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

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

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

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

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

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

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

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

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

</tbody>

Spreadsheet Formulas

Cell Formula B1 =COUNTIF('John Data'!$W$2:$W$20,'John Result'!A1) H1 =COUNTIF('John Data'!$W$2:$W$20,"<>*Head of IT*") A3 {=IF(ROWS(A$3:A3)<=$B$1,INDEX('John Data'!Q$2:Q$20,SMALL(IF('John Data'!$W$2:$W$20=$A$1,ROW('John Data'!Q$2:Q$20)),ROWS(A$3:A3))-ROW('John Data'!$A$2)+1),"")} G3 {=IF(ROWS(G$3:G3)<=$H$1,INDEX('John Data'!Q$2:Q$20,SMALL(IF('John Data'!$W$2:$W$20<>$A$1,ROW('John Data'!Q$2:Q$20)),ROWS(G$3:G3))-ROW('John Data'!$A$2)+1),"")} <tbody> </tbody>

Formula Array: Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

The formulas in A3 and G3 need entering with ctrl shift enter NOT just enter, they can then be copied across and down.

I hope that helps.

Good luck.

Ak

 

[Akashwani]

Hi John,

Can you post a sample of your expected results please?
This will help me to try and understand what you require.

Wherever there is John Data within the formula, you need to change that to sheet1 or whatever your sheet name is.

Ak