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


Answers

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

enter image description here

  1. Click in your data
  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

Tada

By : joseph4tw


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

Result:

enter image description here

  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


This video can help you solving your question :)
By: admin