Sorting data in new sheet.

[sam]

Hi everyone,

Here is my issue. --Sheet1-- below is the data set and --Sheet 2-- is what
result I am chasing. I need $B$2:$C$13 to be populated with the results (*)
using the data in --Sheet1--. I have absolutly no idea whether or not you use
RANK and something else. Any help would be appreciated and thanks in advance.

--Sheet1--
A B C
1 #Name# #Date# #Result#
2 Sam 12/12/08 Orange
3 Sam 3/01/09 Apple
4 Danny 30/12/08 Plum
5 Sam 10/01/09 Plum
6 Danny 10/01/09 Mango

--Sheet2--
A B C
1 #Date# #Sam# #Danny#
2 1/01/09 *Orange *Plum
3 2/01/09 *Orange *Plum
4 3/01/09 *Apple *Plum
5 4/01/09 *Apple *Plum
6 5/01/09 *Apple *Plum
7 6/01/09 *Apple *Plum
8 7/01/09 *Apple *Plum
9 8/01/09 *Apple *Plum
10 9/01/09 *Apple *Plum
11 10/01/09 *Plum *Mango
12 11/01/09 *Plum *Mango
13 12/01/09 *Plum *Mango

 

[Max]

Here's one formulas play which delivers the results sought ..

In Sheet2,
Assume B1:C1 contains the names: Sam, Danny
(names must be consistent with that listed in Sheet1's A2:A6)

Put in B2, normal ENTER
=INDEX(Sheet1!$C$2:$C$6,MATCH(1,INDEX((Sheet1!$A$2:$A$6=B$1)*(Sheet1!$B$2:$B$6<=$A2),),0))

Put in B3, normal ENTER
=IF(ISNA(MATCH(1,INDEX((Sheet1!$A$2:$A$6=B$1)*(Sheet1!$B$2:$B$6>=$A2)*(Sheet1!$B$2:$B$6<=$A3),),0)),B2,INDEX(Sheet1!$C$2:$C$6,MATCH(1,INDEX((Sheet1!$A$2:$A$6=B$1)*(Sheet1!$B$2:$B$6>=$A2)*(Sheet1!$B$2:$B$6<=$A3),),0)))
Copy B3 to B13. Then select B2:B13, copy to C2:C13. And that drives out the
exact results that you seek/indicate.

Celebrate success, hit YES below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik

 

[sam]

Max,

You are an absolute legend.

Cheers

Sam

Here's one formulas play which delivers the results sought ..

In Sheet2,
Assume B1:C1 contains the names: Sam, Danny
(names must be consistent with that listed in Sheet1's A2:A6)

Put in B2, normal ENTER:
=INDEX(Sheet1!$C$2:$C$6,MATCH(1,INDEX((Sheet1!$A$2:$A$6=B$1)*(Sheet1!$B$2:$B$6<=$A2),),0))

Put in B3, normal ENTER:
=IF(ISNA(MATCH(1,INDEX((Sheet1!$A$2:$A$6=B$1)*(Sheet1!$B$2:$B$6>=$A2)*(Sheet1!$B$2:$B$6<=$A3),),0)),B2,INDEX(Sheet1!$C$2:$C$6,MATCH(1,INDEX((Sheet1!$A$2:$A$6=B$1)*(Sheet1!$B$2:$B$6>=$A2)*(Sheet1!$B$2:$B$6<=$A3),),0)))
Copy B3 to B13. Then select B2:B13, copy to C2:C13. And that drives out the
exact results that you seek/indicate.

Celebrate success, hit YES below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik

 

[Max]

Welcome, glad it worked, Sam
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---