Trying to consolidate 4 columns of data which may include duplicates

[Groovicles]

Hi everyone,

I'm working on a problem and I can't seem to find an suitable solution. I have four columns of data (in column B) across 4 worksheets within the same workbook. The rows in this columns contain text. In some cases the text in various rows of each columns are identical, and in other cases they are unique. There is a percentage in column C for each text in column B.

What I'm trying to do is as follows:

Create a consolidated list of all four columns into a single column (say column A in a separate sheet). Therefore, each row in this consolidated column will have a unique text based value
Have the percentage value for each unique text based entry in column B of the separate sheet. if the text based contents in the column A row is one of those that had the same entry in the original four columns, then the average of the percentages will appear in column B.

I've been scratching my head on this for a while now and can't seem to come to any logical process. Any ideas?

Thanks guys!

[haripopuri]

Possible to upload a workbook with data and desired results?