Help compiling vertical list from mulitple columns of data

Hi there

I'm doing a group purchase of metal coins and I have a horizontal list requests that I want to output into lists on another sheet.

So for example in row 9 I've marked a 2 in column O and 1 in column Q

What I would like to do is on another sheet be able to write a name of the buyer and then a list is generated of the set names and how many he wants of each

So if I type Person 10 it would return
Seven Wonders 1
Seven wonders duel 1
Seven Wonders Armada 1
Seven wonders leaders 1
Seven wonders cities 1



I've set it all up myself and done a lot of basic formulas, but this next step is befuddling me.

If I need to adjust the title or explain details clearer, please let me know.

Thank you in advance for anyhelp you are able to give.

Please read the yellow banner (top) and post a sample sheet.

Maybe this to return the header:
=IFERROR(INDEX(TEST!$C$1:$H$1,AGGREGATE(15,6,COLUMN(TEST!$C$1:$H$1)-COLUMN(TEST!$C$1)+1/(INDEX(TEST!$C$4:$H$13,MATCH($A5,TEST!$A$4:$A$13,0),)>0),COLUMNS($B4:B4))),"")

and this to return the value:
=IFERROR(INDEX(INDEX(TEST!$C$4:$H$13,MATCH($A5,TEST!$A$4:$A$13,0),),AGGREGATE(15,6,COLUMN(TEST!$C$1:$H$1)-COLUMN(TEST!$C$1)+1/(INDEX(TEST!$C$4:$H$13,MATCH($A5,TEST!$A$4:$A$13,0),)>0),COLUMNS($B5:B5))),"")

both copied across. But... it's a guess as you did not shiow us your expected answers... as requested.

Originally Posted by Glenn Kennedy

Maybe this to return the header:
=IFERROR(INDEX(TEST!$C$1:$H$1,AGGREGATE(15,6,COLUMN(TEST!$C$1:$H$1)-COLUMN(TEST!$C$1)+1/(INDEX(TEST!$C$4:$H$13,MATCH($A5,TEST!$A$4:$A$13,0),)>0),COLUMNS($B4:B4))),"")

and this to return the value:
=IFERROR(INDEX(INDEX(TEST!$C$4:$H$13,MATCH($A5,TEST!$A$4:$A$13,0),),AGGREGATE(15,6,COLUMN(TEST!$C$1:$H$1)-COLUMN(TEST!$C$1)+1/(INDEX(TEST!$C$4:$H$13,MATCH($A5,TEST!$A$4:$A$13,0),)>0),COLUMNS($B5:B5))),"")

both copied across. But... it's a guess as you did not shiow us your expected answers... as requested.

Sorry for not being clear on what output I was after. I was hoping to type (on a seperate sheet) a name of the buyer (the one unblurred is me) and it return below, the name of the game and the amount of sets that have been ordered. There are 150 different names (starting at 9) and the columns range from C to BM

Cleaer.jpg

Attachment 759528

Same formula, oriented in columns.

Are you still using Excel 2011 and are you still operating on a MAC? These are important as they may offer alternative solutions based upon which version you are using and which operating system. Not all versions of Excel are the same. Newer versions offer more functionality.

Attached is a Windows version that includes Power Query which is not available in a MAC operating environment. I created this solution without looking at the version you had posted.