## Sort data into column bins in excel

Question!

Suppose one has data arranged vertically like this:

``````Name      Birthday
John      6 June
Mary      12 May
Sue       1 June
Brian     10 August
Carl      24 June
Ted       18 August
Linda     31 January
``````

Is it possible in Excel (without VB) to sort the data into column bins thus?:

``````Jan    Feb    Mar    Apr    May    Jun    Jul    Aug    Sep    Oct    Nov    Dec
Linda                       Mary   John          Brian
Sue           Ted
Carl
``````

(Even better would be to have them sorted within each bin by birth date.)

By : Carboxyl

Without modifying the data or using VBA, the closest I think you'll get is a PivotTable:

2. Insert --> PivotTable
3. OK
4. Drag the fields like this: Name--> Row Labels. Birthday --> Column Labels. Name --> Values
5. Right-click on a date in the column fields --> Group...
6. Select Months --> OK

By : joseph4tw

FYI, this is a bit complicated, so it might be hard to maintain/explain to someone else what you did.

Result:

1. In Cells D1 to O1, put in 1/1, 2/1, 3/1, etc
2. Select those cells, right-click --> Format Cells...
3. Choose Custom and put "mmm" (without quotes)
4. In D2, put this formula in there: `=IFERROR(INDEX(\$A\$2:\$A\$8,SMALL(IF(MONTH(\$B\$2:\$B\$8)=MONTH(D\$1),MATCH("~"&\$A\$2:\$A\$8,\$A\$2:\$A\$8&"",0)),ROWS(\$2:2))),"")`
5. Enter that formula with CTRL+SHIFT+ENTER (array formula)
6. At the bottom right of D2, there is a little black square, hover over this and your mouse will turn into a plus sign. Click this and drag down to cell D8.
7. Cells D2:D8 will be highlighted. Click the little square again found in D8. Drag to the right all the way till Dec

And there you have it.

I'd like to thank Aladin for the help with this formula:

http://www.mrexcel.com/forum/excel-questions/470371-unique-list-based-criteria.html#post2322387

By : joseph4tw