help manipulating data to produce desired pivot table results...

Hi. I would like help figuring out how to manipulate some data so that I can "feed" them to a pivot table that will then produce the results I need. I have attached a file that contains both the data and the desired pivot table results on a single worksheet.

Here is what I can't figure out how to achieve:

1. Find each row that has identical values in columns A, B, C, and D ( ID, Group, and DataWaveNo., and ProblemNo)

2. For those rows with identical values in columns A, B, C, and D produce the average of the values in columns F, G, and H (Goal Established, Person Assigned, and TimelineSet), expressed as a percentage, and place the result in the corresponding row in column I (ActionPlan).

Now here's the tricky part. I want the results that are placed in the ActionPlan column to be the average "across all SolutionNos. associated with a given ProblemNo.," and to show that result in each row that has identical content in columns A, B, C, and D.

For example, take a look at rows 4 and 5. These rows contain identical values in columns A, B, C, and D. The average for row 4 would "ordinarily" be 0%, and the average for row 5 would ordinarily be 100%. What I want to do is show the average of these two values (50%) in cell I4 and NA in cell I5 (just as the data in my attached worksheet show).

This produces the desired values in the pivot table, which is an average of 87.50% across all ProblemNos. for DataWaveNo. 3. (The "Table Options" tab for the pivot table has been set up so that the word "excluded" is shown for error values.)

Because of the way raw data must be structured to best accomdate use of a pivot table, this is the only way I can see to produce the results I need. And, for other reasons, it is important to me to use pivot tables if possible.

Any and all help would be appreciated. I have fiddled with various array formulas to try to produce what I need, but I just can't seem to figure out how to do it.

Many thanks,

64rad