Grouping column containing Number values into specific subgroups

[Sonny Thind]

i have a dynamic column with ageing number of Days listed, I am trying to Group these according to specific criteria
i.e
Less than 30 days
31 to 45 Days
46 to 70 Days
71 to 99 Days
>100 Days
this criteria will remain Fixed throughout
A pivot doesn't provide specific criteria as the group criteria are not fixed .. is there another method i could use to do this?

[p45cal]

Less than 30 days
31 to 45 Days
46 to 70 Days
71 to 99 Days
>100 Days
I've assumed:
Less than 31 days
31 to 45 Days
46 to 70 Days
71 to 99 Days
>99 Days

since 30 days and 100 days would not fit in any category. You'll have to tweak if I've guessed wrongly.

Add a column with the formula in row 2:
=INDEX({"<31";"31-45";"46-70";"71-99";">99"},MATCH(B2,{0;31;46;71;100},1))
and copy down. Give the column a header, say Group.

Then add a pivot table including this new column as part of its source data, include Group in the (top of the) Row Labels section of the pivot.

See attached.
I imagine there are more columns than you're showing in your real data; just add the columns you want to see to the Row Labels section of the pivot.

[Sonny Thind]

hi p45cal
. I really like the array formula.

This really helps thank you very much for this..