Group Records based on Type/Category with Repetition in Category Column

[SanthoshRajoo]

Hi All,

I am faced with a scenario where I have different Categories/Types and each Category starts at a particular No/ID and ends at a particular ID. I want to summarize the Categories with the Start ID and End ID. I tried the summarize tool but the issue is it summarizes the Category just once even though the Category is present at different intervals of IDs. 

The Sample Input and Output are below:

Sample Input

Category/Type	ID
Cat1	1
Cat1	2
Cat1	3
Cat1	4
Cat1	5
Cat1	6
Cat1	7
Cat1	8
Cat1	9
Cat1	10
Cat2	11
Cat2	12
Cat2	13
Cat3	14
Cat3	15
Cat1	16
Cat1	17
Cat2	18
Cat2	19
Cat2	20
Cat5	21
Cat5	22

Sample Output needed:

Category/ID	Start	End
Cat1	1	10
Cat2	11	13
Cat3	14	15
Cat1	16	17
Cat2	18	20
Cat5	21	22

Thanks and regards,

Santhosh Rajoo

[Jean-Balteryx]

Hi @SanthoshRajoo ,

Here is a workflow doing what you need. Basically, it assign an ID to each category with a different ID if the category is present at a different interval.

[JoaoLeiteV]

Hey there @SanthoshRajoo,

I've set up an Example using a Formula to create the columns with some starter numbers, then two multi-row formulas to get the values and finally a Summarize to group data and get the Max ending value.

The only thing that happened was that the Sorting of Summarize changed things a bit, but the results should be the way you want.

Hope this helps!

[LukeG · Accepted solution]

Multi-row formula to create grouping for consecutive items

Summarize to get min and max

Sort to.... sort

Select to choose and rename fields

Hope this helps!