Two issues: building a list from an array of data and looking up in an array against list

[ivega29]

I am building a list of customers looking to buy widgets with unique piece #'s. For each customer they are asked to rank the widgets from their top pick to their bottom pick.
I ranked each customer based on his/her importance to the company. I need to reverse this table and have a list of the piece #'s and each customer associated with that piece but in order of how I ranked them and how they ranked the piece. Please see attached.
Avoiding VBA would be ideal here.

[Glenn Kennedy]

You did not show us HOW tyou wanted the results laid out and did not provide any manually calculated expected results. So... a guess.

Sheet1, a3, copied down:
=UNIQUE(FILTERXML("<A><B>"&TEXTJOIN("</B><B>",TRUE,test!$C$2:$L$6)&"</B></A>","//B"))

B3 copied across and down:
IFERROR(INDEX(test!$B:$B,AGGREGATE(15,6,ROW(test!B$2:B$10)/(test!$C$2:$L$10=Sheet1!$A3),COLUMNS($B3:B3))),"")

[ivega29]

Thanks Glen! Sorry if I wasn't clear. It wasn't easy for me to explain but you got it! Thanks!

[Glenn Kennedy]

You're welcome. I know it can be difficult to find the words!! We've all been there...

---

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

Finally, if that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

[ivega29]

Hi! is there anyway to do this without using the "unique" formula? I have someone working on an earlier version of excel and the formula isn't working.