Tough One: Finding Match Based on Criteria, Returning Value

Sheet 1 is a 3,000 row worksheet with 60 columns. Column A represents Part #s. The 60 columns represent Manufacturers, ONE Manufacturer's quote per row on this spreadsheet. So, if all 60 manufacturers provided a quote for the same part, that part would appear in 60 different rows on Sheet 1, with each row having One quote, which would be contained in different columns of the 60, named for each manufacturer that is quoting. XYX ABC DEF etc. --- 60 in all, all different.

Here's the problem I've been unable to solve.

Sheet 2 (Collate), a different sheet, is 2300 part numbers in Column A, since I've eliminated the Duplicate Part numbers from Sheet 1. So, instead of a part with 60 quotes appearing 60 times, it appears ONCE on Sheet 2 (Collate). Sheet 2 has the 60 columns of course.

**I now need each single part number on Sheet 2 (i.e. duplicates removed on Sheet 2) to have all 60 quotes in the one row. I've tried Vlookup, Match, Index, etc. but the problems with each are keeping me from getting it done.

For example, I want Cell BD2 on Sheet 2 to compare the Part # (Sheet2!A2) to Sheet 1!A2:A3000 see if ANY of the matching Part #s in Sheet 1 have a PRICE in column BD on Sheet 1 -- and then *return the value*. If there is no value, then I need it to keep looking across the rest of the (up to) 60 matches. If none found, then I would like it to return "no quote". Many of the cells in the range on Sheet 1 have "" (no value) since 59 of the columns are empty, since there is only one quote per row.

I can then copy the formula from BD to the rest of the columns to do the same thing for all 60 columns.

FYI: I've kept the Column Names the Same in Sheet 1 and 2. So, all columns (Column BD for example) on both Sheet 1 and Sheet 2 is "XYC Corp Price".

I would prefer to avoid using VBA and use a formula, but if you suggest VBA, then I can give it a try. Thanks!!!!!!!!!!!! Lots of time on this one and up against a deadline. . . .Chris