Sam asks:

S/N RailCorp Ref Number Date In
77203 HRC mod program 10377 24/05/2011
77204 HRC mod program 10285 20/04/2011
77697 HRC mod program 10489 5/07/2011
77698 HRC mod program 10554 8/08/2011
77699 HRC mod program 10408 8/06/2011
77700 HRC mod program 10553 8/08/2011
77701 HRC mod program 10441 23/06/2011
77702 HRC mod program 10442 23/06/2011
77703 HRC mod program 10318 11/05/2011
77717 HRC mod program 10286 20/04/2011
77718 HRC mod program 10490 5/07/2011
79224 HRC mod program 10409 8/06/2011
79225 HRC mod program 10376 24/05/2011
79226 HRC mod program 10210 17/02/2011
79227 HRC mod program 10317 11/05/2011
I don't want to input the S/N in Table 2 manually. I need a formula for it as this is only part of the data that I need to categorise.

I need the S/N listed by the quarter they came in (Date In).

Yealy Quarter No Of Units S/N

Q1-2011 1
Q2-2011 10
Q3-2011 4
Q4-2011 0
Q1-2012 0
If someone can please help.

this is the result I am after but it should be done using formulas

Yearly Quarter No Of Units S/N

Q1-2011 1 79226
Q2-2011 10 77203, 77204, 77699, 77701, 77702, 77703, 77717, 79224, 79225, 79227
Q3-2011 4 77697, 77698, 77700, 77718
Q4-2011 0 NA
Q1-2012 0 NA

Thanks in Advance

Answer

copy table1

Formula in cell C19:

=COUNTIFS($D$2:$D$16, ">="&DATE(A19, CHOOSE(RIGHT(B19,1), 1,4,7,10), 1), $D$2:$D$16, "<="&DATE(A19, CHOOSE(RIGHT(B19,1), 4,7,10,1), 1)-1)

Array formula in cell D19:

=IFERROR(INDEX($A$2:$A$16, SMALL(IF(($D$2:$D$16>=(DATE($A19, CHOOSE(RIGHT($B19,1), 1,4,7,10), 1)))*($D$2:$D$16<=(DATE($A19, CHOOSE(RIGHT($B19,1), 4,7,10,1), 1)-1)), MATCH(ROW($D$2:$D$16), ROW($D$2:$D$16)), ""),COLUMN(A1))), "")

How to enter an array formula

  1. Double click cell D19
  2. Copy above formula and paste to cell D19
  3. Press and hold CTRL + SHIFT
  4. Press Enter

If you did it right the formula now has curly brackets before and after the formula, like this ={formula}. Don´t enter these characters yourself.

Explaining formula in cell C19

Step 1 - Calculate start month

CHOOSE(RIGHT(B19,1), 1,4,7,10)

RIGHT function takes the first character from the right. Q1 becomes 1.

CHOOSE(1, 1,4,7,10) returns month 1.

CHOOSE function returns a value to perform from a list of values, based on the index number.

Step 2 - Create a start date

DATE(A19, CHOOSE(RIGHT(B19,1), 1,4,7,10), 1)

becomes

DATE(2011, 1, 1) and returns 2011-01-01

Step 3- Calculate end date

DATE($A19, CHOOSE(RIGHT($B19,1), 4,7,10,1), 1)-1

becomes

DATE($A19, 4, 1)-1

becomes

(2011-04-01)-1 returns 2011-03-31

Step 4 - Count records in date range

The COUNTIFS function allows you to use multiple conditions. Dates larger than the start date and dates smaller than the end date.

COUNTIFS($D$2:$D$16, ">="&DATE(A19, CHOOSE(RIGHT(B19,1), 1,4,7,10), 1), $D$2:$D$16, "<="&DATE(A19, CHOOSE(RIGHT(B19,1), 4,7,10,1), 1)-1)

becomes

COUNTIFS($D$2:$D$16, ">="&2011-01-01, $D$2:$D$16, "<="&"2011-03-31")

and returns 1.

Explaining formula in cell D19

Step 1 - Find out which values are in a quarter and return their row number

I am not going to explain how to calculate the date ranges again, see step 1 - 3 above.

IF(($D$2:$D$16>=(DATE($A19, CHOOSE(RIGHT($B19,1), 1,4,7,10), 1)))*($D$2:$D$16<=(DATE($A19, CHOOSE(RIGHT($B19,1), 4,7,10,1), 1)-1)), MATCH(ROW($D$2:$D$16), ROW($D$2:$D$16)), "")

becomes

IF(($D$2:$D$16>="2011-01-01")*($D$2:$D$16<="2011-03-31", MATCH(ROW($D$2:$D$16), ROW($D$2:$D$16)), "")

becomes

IF(({FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE;FALSE; FALSE;FALSE; TRUE;FALSE; FALSE;FALSE; FALSE})*({TRUE; TRUE;TRUE;TRUE; TRUE;TRUE; TRUE;TRUE; TRUE;TRUE; TRUE;TRUE; TRUE;TRUE; TRUE}), MATCH(ROW($D$2:$D$16), ROW($D$2:$D$16)), "")

becomes

IF({0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 1; 0}, MATCH(ROW($D$2:$D$16), ROW($D$2:$D$16)), "")

and returns {"";"";"";"";"";"";"";"";"";"";"";"";"";14;""}

Step 2 - Extract row number

SMALL(IF(($D$2:$D$16>=(DATE($A19, CHOOSE(RIGHT($B19, 1), 1, 4, 7, 10), 1)))*($D$2:$D$16<=(DATE($A19, CHOOSE(RIGHT($B19, 1), 4, 7, 10, 1), 1)-1)), MATCH(ROW($D$2:$D$16), ROW($D$2:$D$16)), ""), COLUMN(A1))

becomes

SMALL({"";"";"";"";"";"";"";"";"";"";"";"";"";14;""}, COLUMN(A1)) and returns 14.

Step 3 - Return value

INDEX($A$2:$A$16, SMALL(IF(($D$2:$D$16>=(DATE($A19, CHOOSE(RIGHT($B19,1), 1,4,7,10), 1)))*($D$2:$D$16<=(DATE($A19, CHOOSE(RIGHT($B19,1), 4,7,10,1), 1)-1)), MATCH(ROW($D$2:$D$16), ROW($D$2:$D$16)), ""),COLUMN(A1)))

becomes

INDEX($A$2:$A$16, 14)

becomes

INDEX({77203; 77204; 77697; 77698; 77699; 77700; 77701; 77702; 77703; 77717; 77718; 79224; 79225; 79226; 79227}, 14)

 and returns 79226 in cell D19.

Read more

Group data by dates in a pivot table - Analyze trends using pivot tables

Group data in chart - Highlight a group of chart bars (Go to the bottom of this post)

Group data using vba - Categorize values into multiple columns using vba in excel

Group data to sheets (vba) - Split data across multiple sheets in excel (vba)

If you want to learn more about array formulas join Advanced excel course.

Download excel *.xlsx file

Categorize-data-by-dates.xlsx