How to use Index, Match & Rows correctly.. Need Help Please???

[Worzul]

Hello to all on this board,

Firstly may I say what a great help you guys have been over the last few months reading your forum, i'm a bit of an excel fanatic and love the puzzles it can give you, but i have come accross an absolute stand still over this past week and really could do with your help.

I have been tasked to summarise an ongoing tracker that has been going for some time at work, I have been able to import all the data from 5 sheet into one using VBA, now i require a way to select the following.
[TABLE="width: 144"]
<tbody>[TR]
[TD="width: 64, bgcolor: transparent"]Name
[/TD]
[TD="width: 64, bgcolor: transparent"]Dept
[/TD]
[TD="width: 64, bgcolor: transparent"]Crew
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]a
[/TD]
[TD="bgcolor: transparent"]z
[/TD]
[TD="bgcolor: transparent, align: right"]1
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]b
[/TD]
[TD="bgcolor: transparent"]z
[/TD]
[TD="bgcolor: transparent, align: right"]1
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]c
[/TD]
[TD="bgcolor: transparent"]z
[/TD]
[TD="bgcolor: transparent, align: right"]2
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]d
[/TD]
[TD="bgcolor: transparent"]z
[/TD]
[TD="bgcolor: transparent, align: right"]3
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]e
[/TD]
[TD="bgcolor: transparent"]y
[/TD]
[TD="bgcolor: transparent, align: right"]2
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]f
[/TD]
[TD="bgcolor: transparent"]y
[/TD]
[TD="bgcolor: transparent, align: right"]2
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]g
[/TD]
[TD="bgcolor: transparent"]y
[/TD]
[TD="bgcolor: transparent, align: right"]1
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]h
[/TD]
[TD="bgcolor: transparent"]y
[/TD]
[TD="bgcolor: transparent, align: right"]1
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]i
[/TD]
[TD="bgcolor: transparent"]x
[/TD]
[TD="bgcolor: transparent, align: right"]1
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]j
[/TD]
[TD="bgcolor: transparent"]x
[/TD]
[TD="bgcolor: transparent, align: right"]2
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]k
[/TD]
[TD="bgcolor: transparent"]x
[/TD]
[TD="bgcolor: transparent, align: right"]3
[/TD]
[/TR]
</tbody>[/TABLE]


As you can see from above the data is a bit mixed, I need a way so in a seperate sheet("Sheet1") in say d1=dept d2 = crew this would would give me just ie the name, dept 7 Crew of dept z and crew 1 starting in cell a2 on this("Sheet1"). I have been trying to use index & match, row numbers and large but it's just getting me down with a hurdle every time. i realise i may need to add row numbers of some sort as say Dept Z & crew 1 can be repeated over 100 times.

Finished ideal result in seperate sheet..

[TABLE="width: 288"]
<tbody>[TR]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"]Dept
[/TD]
[TD="width: 64, bgcolor: transparent"]z
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Name
[/TD]
[TD="bgcolor: transparent"]Dept
[/TD]
[TD="bgcolor: transparent"]Crew
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]crew
[/TD]
[TD="bgcolor: transparent, align: right"]1
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]a
[/TD]
[TD="bgcolor: transparent"]z
[/TD]
[TD="bgcolor: transparent, align: right"]1
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]b
[/TD]
[TD="bgcolor: transparent"]z
[/TD]
[TD="bgcolor: transparent, align: right"]1
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]c
[/TD]
[TD="bgcolor: transparent"]z
[/TD]
[TD="bgcolor: transparent, align: right"]1
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]d
[/TD]
[TD="bgcolor: transparent"]z
[/TD]
[TD="bgcolor: transparent, align: right"]1
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]e
[/TD]
[TD="bgcolor: transparent"]z
[/TD]
[TD="bgcolor: transparent, align: right"]1
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]f
[/TD]
[TD="bgcolor: transparent"]z
[/TD]
[TD="bgcolor: transparent, align: right"]1
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]
Thanks for your help in advance guys, it would really be appreaciated as i have been up many early mornings trying to figure this out.

 

[PaddyD]

Pivot table?

 

[Worzul]

Pivot table?

Hi Paddy. Thanks for your response but this will updated regularly on a shared drive and from this data it will give top and bottom performers. I did think of pivot table but I need to make it all automated so management can use it who are not so great with pc knowledge.
But thanks again. I just tried another tinker with it using index and large but got shot down. I'm doing some thing wrong but not sure what.
Thanks for replying so soon though Paddy.

 

[AhoyNC]

Maybe something like this.
If you set up the ranges properly you can enter in the first cell and copy over and down.

This is an array formula that must be entered with

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L
1	Name	Dept	Crew
2	a	z	1							Dept	z
3	b	z	1			Name	Dept	Crew		crew	1
4	c	z	2			a	z	1
5	d	z	3			b	z	1
6	e	y	2
7	f	y	2
8	g	y	1
9	h	y	1
10	i	x	1
11	j	x	2
12	k	x	3
13
CTRL-SHIFT-ENTER.

 

[Worzul]

Oh nice ahoync
Works like a charm. Yo be honest looking at the formulas I have been very close with it. But how you got that answer in a matter of an hour is something I'm not quite there yet. It's took me a week. Mid you sometimes you can be looking to hard I suppose.

The only issue I have now is I have had to copy the formulas down 2000 lines so every time I change a value it's taking a good minute or so. This could be with going through nearly 10,000 tows of data to look through. I'm looking now at a message box that comes up with calculating when whenaI value has changed and goes of when finished calculating. Oh how I love a challenge. This will probably go onto onto a different thread unless anyone can help.

I have sorted the worksheet change reference to a cell when I can add a macro. Just need to work out calculating calculating starts to show either a message box or user for and the to worn out when calculating had ended to take off the message box or user from.

I'm getting very close to it though.

Thanks for your help again all.

I love excel because it gives you a fresh puzzle everyday.

 

[Worzul]

** sorry double post *** phone on a bit of a mission.

Oh nice ahoync
Works like a charm. Yo be honest looking at the formulas I have been very close with it. But how you got that answer in a matter of an hour is something I'm not quite there yet. It's took me a week. Mid you sometimes you can be looking to hard I suppose.

The only issue I have now is I have had to copy the formulas down 2000 lines so every time I change a value it's taking a good minute or so. This could be with going through nearly 10,000 tows of data to look through. I'm looking now at a message box that comes up with calculating when whenaI value has changed and goes of when finished calculating. Oh how I love a challenge. This will probably go onto onto a different thread unless anyone can help.

I have sorted the worksheet change reference to a cell when I can add a macro. Just need to work out calculating calculating starts to show either a message box or user for and the to worn out when calculating had ended to take off the message box or user from.

I'm getting very close to it though.

Thanks for your help again all.

I love excel because it gives you a fresh puzzle everyday.

 

[AhoyNC]

Glad that it worked for you.
Not sure of your layout and you may already be doing this, but since you know the Dept. and Crew you only need to search for the names. So, if you just showed the Dept. & Crew once taken from the criteria you would cut down number of calculations by 2/3 rds.

 

[Worzul]

Yes Thabks AhoyNC, I have changed it a bit so I am only using it one row now.

I have however justt hit another snag which I'm hoping you guys could help me with.

In sheet "Tracker" I have Cell
[TABLE="width: 567"]
<tbody>[TR]
[TD][/TD]
[TD]Crew[/TD]
[TD="colspan: 2"]2[/TD]
[TD][/TD]
[TD]Area[/TD]
[TD="colspan: 2"]A[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Week[/TD]
[TD="colspan: 2"]44[/TD]
[TD][/TD]
[TD]Percent[/TD]
[TD="colspan: 2"]90[/TD]
[TD][/TD]
[/TR]
</tbody><colgroup><col><col span="8"></colgroup>[/TABLE]

C5 = 2
G5 = A
G7 = 90

not worried about the week no as that is done in the date sheet using AhoyNC's formula (Thanks)
[TABLE="width: 416"]
<tbody>[TR]
[TD]Row/ Coulum[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Position[/TD]
[TD]Name[/TD]
[TD]Dept[/TD]
[TD]Crew[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]99.2501[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]100.25[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]98.5001[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]99.5[/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]99.5001[/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD]12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]99.5002[/TD]
[/TR]
[TR]
[TD]22[/TD]
[TD]13[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]23[/TD]
[TD]14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]24[/TD]
[TD]15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]96.5001[/TD]
[/TR]
[TR]
[TD]25[/TD]
[TD]16[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]26[/TD]
[TD]17[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]27[/TD]
[TD]18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]28[/TD]
[TD]19[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]29[/TD]
[TD]20[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]30[/TD]
[TD]21[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]31[/TD]
[TD]22[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]32[/TD]
[TD]23[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]33[/TD]
[TD]24[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]34[/TD]
[TD]25[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]35[/TD]
[TD]26[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]36[/TD]
[TD]27[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]37[/TD]
[TD]28[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]38[/TD]
[TD]29[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]39[/TD]
[TD]30[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody><colgroup><col><col span="5"></colgroup>[/TABLE]

So Basically I am trying to put it in order from smallest to large bassed on the criteria, the percentage must be any one under that percentage I.E under 90%.

I have so far come up with this but is only giving me what it says above, as you can see it's not correct and i can't even get the percentage in it yet. Please help me.
=(INDEX('Data Sheet'!$BJ$3:$BJ$90000,SMALL(IF(('Data Sheet'!$B$3:$B$90000=$G$5)*('Data Sheet'!$C$3:$C$90000=$C$5),ROW('Data Sheet'!$A$3:$A$10000)-ROW('Data Sheet'!$A$3)+1),ROWS('Data Sheet'!$A$3:'Data Sheet'!A3))))

I am unsure if this can even be done, but if any one can, I know you guys can.

It's a good puzzle that's for sure.

Thanks in advance guys, i'll keep chipping away with in and see if I can come up with something.

Also thanks for your help already it's really appreciated.

 

[Worzul]

Forgot to add

In my Data Sheet I Have
Column A = Name
Column B = Dept
Column B = Crew
Then Columns D - BC are the weeks date.
Column BI = average over 4 weeks based on the week selected in "Tracker" Sheet (This what the percentage is based on)
Column BJ = Formula =IF(BI3=0,"",(IFERROR(IF(BI3="","",(COUNTIF($BI$3:BI3,BI3)-1)*0.0001+BI3),""))) which counter acts any duplicate values.


Cheers