Group every N record

[ish894]

Here's a problem I'm trying to solve where I need to allocate each 7 Ticket_ID's to a group and need to loop it through the incoming records. I've to use multi-row formula and tile but I was not successful in creating output below.

Output Expected:

Ticket_ID	Group
REQ001	1
REQ002	1
REQ003	1
REQ004	1
REQ005	1
REQ006	1
REQ007	1
REQ008	2
REQ009	2
REQ010	2
REQ011	2
REQ012	2
REQ013	2
REQ014	2
REQ015	3
REQ016	3
REQ017	3
REQ018	3
REQ019	3
REQ020	3

[JohnJPS · Accepted solution]

Hi @ish894,

For this, I would add a RecordID, and then a Formula assigning [Group] to be

CEIL([RecordID]/7)

Hope that helps!

John

[ish894]

Thanks. It worked @JohnJPS . I've added a new column Link for category. And here's the expected output:

Link	Ticket_ID	Group
A	REQ001	1
A	REQ002	1
A	REQ003	1
A	REQ004	1
A	REQ005	1
A	REQ006	1
A	REQ007	1
B	REQ008	2
B	REQ009	2
B	REQ010	2
B	REQ011	2
B	REQ012	2
B	REQ013	2
B	REQ014	2
B	REQ015	3
B	REQ016	3
B	REQ017	3
B	REQ018	3
B	REQ019	3
B	REQ020	3

How I can categorize the data using Link column?

[danilang]

Hi @ish894 

What do you mean by "Categorize the data".  A sample output table would be the best description, avoiding any language induced ambiguity.

Dan

[ish894]

@danilang I haven't updated my last post and haven't been thorough enough to explain how the changes for this post has gone through. Now, I've updated the post with my initial problem and it's subsequent changes. I've updated the expected outcome in my previous reply to @JohnJPS .

[danilang]

@ish894 

By Categorize do you mean that you want the groups to restart at 1 when then Link changes?  i.e. A would have group 1 and B would have Group 1 and Group 2.

If the outcome that you've posted, A has group 1 and B has Group 2 and Group 3,  is what you're looking for, @JohnJPS's formula still works.

If @JohnJPS's solution is correct, then consider marking the solution as accepted.

Dan