Sorting or arranging data

I have a sheet with 2 columns. One is labeled Players the other is labeled points. This has been gathered over several years and as a result, I have the same player name listed multiple times in the player column, but obviously their point total is different each year. What I am wanting to do, is instead of having duplicate names and their scores, I want to keep the sort of players but span the points horizontally across the sheet. so that I can total the players points for some history. I'll list a brief example below of what I current have then what I want so it makes more sense...

abigail87 45
abrnfanatc 561
abrnfanatc 671
abrnfanatc 257
AcctTiger 595
AcctTiger 426
AcctTiger 647
AcctTiger 569
AcctTiger 700
AcctTiger 338
AcctTiger 258
achamb7 254
achamb7 13
adsfr 838
adsfr 566
aed14 315
AG4AU 288
AirForceforAU 73
alabamapaper 120
ALTiger 797
ALTiger 757
ALTiger 569
ALTiger 573


What I want instead is: (when there are duplicate names)

abrnfanatc 561 671 257
AcctTiger 595 426 647 569 700 338 258
etc..

Can I do this using a vlookup or is there something else I need to use for that? I also wouldn't be opposed to having it group by name something like this

abrnfanatc AcctTiger

561 595
674 426
251 etc...

With data in A & B

Create list of (non-duplicate) players in C (copy A into C and use "DATA" ==> Remove Duplicates)

in D1

=IFERROR(INDEX($B$1:$B$23,SMALL(IF($A$1:$A$23=$C1,ROW($A$1:$A$23)-ROW($A$1)+1,""),COLUMNS($A:A))),"")

enter with Ctrl+Shift+enter

Copy across and down

Thanks a lot guys. I guess I should have noted that I have the players in column B (starting at B5 with the points in column C starting at C5. Can either of you adjust the formulas for me to begin with those cells? I've sorted the list and removed duplicates. The list is in column H beginning with H5

try

=IFERROR(INDEX($C$5:$C$27,SMALL(IF($B$5:$B$27=$H5,ROW($B$5:$B$27)-ROW($B$5)+1,""),COLUMNS($B:B))),"")

Thanks a bunch folks... Now, I've decided I need to organize it better and by year. I have sheet1 with data for multiple years arranged in columns. For example..2006 is listing players in column B with their totals in column C, 2007 has players in column E and totals in column F (skipping a column between years) the pattern continues through 2015 with players in column AC and totals in column AD.

I have a new sheet in the workbook with a list of the players and duplicates removed so the player only lists one time. I have one row (row 3 with years listed, 2006, 2007, 2008, etc.. Players are listed on this sheet in Column A. What I want to do is pull the totals from sheet1 for the player in each year. I can't figure out how to have it find that player for that year only and return that value to the appropriate column. So, I need it to search only that player for only that year and return the values in the appropriate cell on sheet6. Thanks a bunch for all that have helped. You have all saved me hours of work.

If you require help, post a version of your new file showing what is required.

See the attached which I hope is what you want (or close to).

The formula is to extract the data is Sheet2 B4:

=IFERROR(INDEX(Sheet1!$B$3:$P$200,MATCH($A4,INDEX(Sheet1!$B$3:$P$200,,MATCH(B$3,Sheet1!$B$3:$P$3,0)),0),MATCH(B$3,Sheet1!$B$3:$P$3,0)+1),"")

Copy across and down as required.

I'm attaching a spreadsheet to show better what I'm trying to do. On sheet 1 is a list of people and numbers. Those lists are sorted by year. What I want to do is sort it by player on sheet 2. So if they didn't show up on a list for a particular year then the total would be zero.

in C6

=IFERROR(INDEX(Sheet1!$B$3:$AD$200,MATCH($B6,INDEX(Sheet1!$B$3:$AD$200,,MATCH(C$3,Sheet1!$B$3:$AD$3,0)),0),MATCH(C$3,Sheet1!$B$3:$AD$3,0)+1),"")

Copy across and down

"No shows" are blank.

If you want zero

=IFERROR(INDEX(Sheet1!$B$3:$AD$200,MATCH($B6,INDEX(Sheet1!$B$3:$AD$200,,MATCH(C$3,Sheet1!$B$3:$AD$3,0)),0),MATCH(C$3,Sheet1!$B$3:$AD$3,0)+1),0)

Put the formula in cell C4 and pull down and to the right.

Formula:

Please Login or Register  to view this content.

These are giving me exactly what I need. Thanks everybody for the help. It's much appreciated.