Cross Tab or Arrays? 1

[Crystalyzer]

have some data relative to investors who have made purchases over the last five years as follows:

Investor    Date        Deal       MktValue
Inv #1    1/1/2003     Deal #1          500
Inv #3    2/29/2004    Deal #1          250
Inv #1    3/31/2001    Deal #2          375
Inv #3    6/30/2002    Deal #2          450
Inv #1    5/30/2000    Deal #3          150
Inv #3    7/4/2001     Deal #3           75
Inv #3    1/22/2003    Deal #4          183
Inv #3    3/21/2004    Deal #5          217
Inv #1    4/21/2001    Deal #1          575
Inv #1    7/21/2002    Deal #2          621 
Inv #2    6/20/2000    Deal #3          287 
Inv #2    7/25/2001    Deal #4          873
Inv #2    2/12/2003    Deal #5          140
Inv #2    4/11/2004    Deal #1          900
Inv #2    5/12/2001    Deal #2          150

I need to show the top 5 purchases by Market value for each investor by year as follows:

Investor    2000      2001      2002      2003      2004
Inv #1    Deal #1    Deal #1    Deal #1    Deal #1    Deal #1
Inv #1    Deal #2    Deal #2    Deal #2    Deal #2    Deal #2
Inv #1    Deal #3    Deal #3    Deal #3    Deal #3    Deal #3
Inv #1    Deal #4 Deal #4 Deal #4    Deal #4
Inv #1            Deal #5    Deal #5    Deal #5
Inv #2        Deal #1    Deal #1    Deal #1    Deal #1
Inv #2        Deal #2    Deal #2    Deal #2    Deal #2
Inv #2            Deal #3    Deal #3    Deal #3
Inv #2                Deal #4    Deal #4
Inv #2                Deal #5    Deal #5
Inv #3    Deal #1    Deal #1    Deal #1        
Inv #3    Deal #2    Deal #2    Deal #2        
Inv #3    Deal #3    Deal #3            
Inv #3    Deal #4    Deal #4    
Inv #3    Deal #5    Deal #5

The results are hard to follow above but I couldn't sit and try to space them out anymore it was driving me insane. The resulting "table" should list the investors down the first column and the years in the subsequent 5 columns. The data items in the table should be the Deal Names (NOT $$) ranked from highest to lowest market value within each year. The Investors should be sorted alphabetically and should contain 5 rows EVEN IF THERE IS NO DATA. I've tried a cross tab with no success and I thought perhaps a few arrays would be in order but again I'm not sure where to start. Can anyone offer some guidance?

Thanks so much!

Lloyd

 

[lbass]

I responded in the CR "Other Topics" forum. It's better not to create multiple posts--most respondents participate in all the CR forums anyway.

-LB

 

[Crystalyzer]

lbass you are a CR GOD!

Thanks for the speedy reply I'll get crackin on it and let you know how I make out! I see you replied to me in both forumns, I guess there's no need to duplicate my questions I just thought I would try to cover all bases.

Thanks so much for taking the time to consider my dilema and help me out!

Star for you! (in both places)

Thanks again,
Lloyd