Formula to pick stores with lowest miles?

[bbrimberry]

Hello,

I am hopeful someone will be able to help me with the problem outlined below.

I have 2 tables. One table has sales reps and the number of hours in a week they work.


[TABLE="width: 431"]
<tbody>[TR]
[TD]Rep[/TD]
[TD]Weekly Hours[/TD]
[TD]Capacity How many hours left?[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Mary[/TD]
[TD="align: right"]37[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]Sue[/TD]
[TD="align: right"]39[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]



My second table has stores each rep can drive to.


[TABLE="width: 267"]
<tbody>[TR]
[TD]Rep[/TD]
[TD]Store Number[/TD]
[TD]Miles Away[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]85[/TD]
[/TR]
[TR]
[TD]Mary[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]91[/TD]
[/TR]
[TR]
[TD]Sue[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]45[/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"]31[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]55[/TD]
[/TR]
[TR]
[TD]Mary[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]55[/TD]
[/TR]
[TR]
[TD]Sue[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]99[/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]21[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]13[/TD]
[/TR]
[TR]
[TD]Mary[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]98[/TD]
[/TR]
[TR]
[TD]Sue[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]58[/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]99[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]28[/TD]
[/TR]
[TR]
[TD]Mary[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]90[/TD]
[/TR]
[TR]
[TD]Sue[/TD]
[TD="align: right"]42[/TD]
[TD="align: right"]49[/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD="align: right"]42[/TD]
[TD="align: right"]55[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]30[/TD]
[/TR]
[TR]
[TD]Mary[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]30[/TD]
[/TR]
[TR]
[TD]Sue[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]63[/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]99[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]95[/TD]
[/TR]
[TR]
[TD]Mary[/TD]
[TD="align: right"]47[/TD]
[TD="align: right"]76[/TD]
[/TR]
[TR]
[TD]Sue[/TD]
[TD="align: right"]44[/TD]
[TD="align: right"]56[/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD="align: right"]48[/TD]
[TD="align: right"]33[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]56[/TD]
[/TR]
[TR]
[TD]Mary[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]26[/TD]
[/TR]
[TR]
[TD]Sue[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]64[/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]71[/TD]
[/TR]
[TR]
[TD]Mary[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]32[/TD]
[/TR]
[TR]
[TD]Sue[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD="align: right"]37[/TD]
[TD="align: right"]79[/TD]
[/TR]
</tbody>[/TABLE]

I need to get excel to return the stores that are within each reps capacity of 40 hours.

I would like excel to somehow return the stores with the least number of miles.


if we used rep john as an example john has 2 hours left.
lets also assume that each store is 1 hour of work.
so i would like exel to return the 2 rows below as the answer

[TABLE="width: 267"]
<tbody>[TR]
[TD]Rep[/TD]
[TD]Store Number[/TD]
[TD]Miles Away[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]13[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]28[/TD]
[/TR]
</tbody>[/TABLE]

thanks for any help provided.

 

[Aladin Akyurek]

The stop rule is not clear. That is, what is the reason/condition for stopping after two records for john?

 

[bbrimberry]

thanks for the reply. i would like to stop when the hours = 40. each store in the table is 1 hour. so for john i want to return the closest 2 stores

 

[bbrimberry]

i also realized that i may not have been clear. lets assume each store in the store table is 1 hour.

 

[sheetspread]

A single formula and pivot table can do this:

Excel 2010
	A	B	C	D	E	F	G	H	I
1	Rep	Weekly Hours	Capacity How many hours left?			Rep	Store Number	Miles Away	Index
2	John	38	2			John	18	85	0
3	Mary	37	3			Mary	27	91	0
4	Sue	39	1			Sue	40	45	0
5	Bob	40	0			Bob	34	31	0
6						John	5	55	0
7	Rep	Index	Store Number	Miles Away		Mary	2	55	0
8	John	2	1	13		Sue	6	99	0
9	John	5	11	28		Bob	32	21	0
10	Mary	4	50	26		John	1	13	2
11	Mary	6	5	30		Mary	12	98	0
12	Mary	9	38	32		Sue	11	58	0
13	Sue	1	13	10		Bob	45	99	0
14						John	11	28	5
15						Mary	1	90	0
16						Sue	42	49	0
17						Bob	42	55	0
18						John	4	30	0
19						Mary	5	30	6
20						Sue	25	63	0
21						Bob	15	99	0
22						John	22	95	0
23						Mary	47	76	0
24						Sue	44	56	0
25						Bob	48	33	0
26						John	50	56	0
27						Mary	50	26	4
28						Sue	7	64	0
29						Bob	23	100	0
30						John	14	71	0
31						Mary	38	32	9
32						Sue	13	10	1
33						Bob	37	79	0
Sheet7 (2)

Cell Formulas
Range	Formula
I2	{=IFERROR(IF(H2<=SMALL(IF($F$2:$F$33=F2,$H$2:$H$33),VLOOKUP(F2,$A$2:$C$5,3,0)),RANK(H2,$H$2:$H$33,1),0),0)}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[bbrimberry]

this is what i needed!! thank you very much!!!