sorting question

Okay, I am very new at any Forums, so if I am doing this wrong, I apologize in advance.

I am not new to Excel, but am just now discovering the vast number of uses it actually has, and am a bit overwhelmed.

My current project seemed simple at first, but I have become stumped.

I have two columns of data: Hits and Wins (column A and Column B).

ie:

Hits Wins
6 Name(2), Name2(3)
5 Name(1), Name2(4)
4 Name(3), Name2(1)
3 Name(1), Name2(2)

This is the results of, obviously, a random generated game (Keno, to be specific). The 'Hits' column is how many numbers out of 20 that a player guesses correctly. The 'Wins' column is a summary of the player's name, and how many times they have 'hit' each number combination (ie: 3/20, 4/20, etc), in a 20 game run.

My file returns the information thusly:

Name Hits Wins
Bear11 6 Bear11 ( 1 )
Bear11 5 Bear11 ( 2 )
boogies3 5 boogies3 ( 2 )
bamkay 4 bamkay ( 4 )
Bear11 4 Bear11 ( 3 )
boogies3 4 boogies3 ( 1 )
Ndisguise 4 Ndisguise ( 2 )
wiffle 4 wiffle ( 2 )
bamkay 3 bamkay ( 3 )
Bear11 3 Bear11 ( 3 )
boogies3 3 boogies3 ( 3 )
Ndisguise 3 Ndisguise ( 7 )
wiffle 3 wiffle ( 7 )

The problem is, each time I run a game, the number of hits changes, and so does the corresponding number of wins. And I need to create a separation such as this:

3/10 amusinsky(6), AngelDust53(6), BADCOMPANY(11), bamkay(4)
Bonnie(3), boogies3(5), chris216(3), DaleJr88(4), Darter(3),
Dolores(7), EC300(7), Fire39skid(7), fire39swife(7), Florence36(9)
fricknnuts(4), GoodVinnie(4), ibBubbles(3), iBChami(6), jay12148(3)
kidddkoala(4), linnie121(5), MAinLA(5), mamabearvp(5), mamap1(5)
Muffinplus(4), nightowl67(8), Pen(7), PolarIce(6), pollydoodle(3)
Sallylo2(4), shady69(8), SingleMom(2), steveamus(7), Tncute(7)
WheresMyLeppies(4)

4/10 amusinsky(4), AngelDust53(4), BADCOMPANY(3), bamkay(5),
Bonnie(2), boogies3(1), chris216(2), DaleJr88(6), Darter(4),
Dolores(3), EC300(3), Fire39skid(4), Florence36(2), fricknnuts(2)
GoodVinnie(2), ibBubbles(1), iBChami(1), jay12148(5), kidddkoala(4)
linnie121(3), MAinLA(4), mamabearvp(5), mamap1(5), Muffinplus(3),
nightowl67(2), Pen(1), PolarIce(4), pollydoodle(4), Sallylo2(4),
shady69(2), SingleMom(8), steveamus(1), Tncute(3),
WheresMyLeppies(5)

Thereby showing the breakdown by number of hits per game.

I have tried using the concatenate formula to no consistent success, due to the limits imposed on the number of items that can be combined. And I am not ashamed to admit that I would not know where to start trying to do this using VBA. And I am totally lost trying to use a pivot table (it keeps totaling when I need a list, LOL)

Any suggestions, pointers, tips or directions to similar problem in forums would be greatly appreciated. Pointed in the right direction, I am fairly capable of hashing out the fine tuning.

Thanks in advance,

bamkay

Bamkay, welcome to the forum.

I'd suggest posting a copy of your workbook so we can see your exact data layout. It would be helpful in this file for you to show a "before" and "after" sheet.

Thank you for the Welcome, pjoaquin

Okay, the file itself is just under 2mb, so I dont think I will attach it. LOL I have created a stand alone sheet with the data format. Hope this helps. If really needed, I can attach the original file, but am sure I would be breaking a size limit rule somewhere

Why do you need to concatenate the list? Can you put the results in separate cells next to each other?

See sheet attached..

I just used formula in I2:

=IF(COLUMNS($A$1:A$1)>COUNTIF($B$2:$B$28,LEFT($H2,FIND("/",$H2)-1)+0),"",INDEX($C$2:$C$28,SMALL(IF($B$2:$B$28=LEFT($H2,FIND("/",$H2)-1)+0,ROW($B$2:$B$28)-ROW($B$2)+1),COLUMNS($A$1:A$1))))

confirmed with CTRL+SHIFT+ENTER not just ENTER.. the { } brackets will appear. Then copy the formula down and across as far as you need. Make sure to go further than your list is long... only the relevant ones will appear, the remaining cells will be blank until another person falls into that category.

Note: Adjust ranges to suit and reconfirm with CSE keys before copying down and across.

Thank you NBVC, hadn't thought of separating the list by cell. The idea and the formula work great.

Thank you very much for your help.

bamkay