Hi Guys, Need some assistance on this one. I have the following data: IDMonth RecordDate212025-02-03222025-02-04232025-02-053102025-02-14412025-02-035132025-02-195142025-02-205152025-02-21662025-02-10672025-02-11692025-02-136102025-02-14 Expected Output IDFIRST DATELAST DATE22025-02-032025-02-0532025-02-142025-02-1442025-02-032025-02-0352025-02-192025-02-2162025-02-102025-02-1162025-02-132025-02-14 Much appreciated any assistance Kind Regards, Dan

Date sequence based on duplicated ID

Hi Guys,

Need some assistance on this one.

I have the following data:

ID	Month Record	Date
2	1	2025-02-03
2	2	2025-02-04
2	3	2025-02-05
3	10	2025-02-14
4	1	2025-02-03
5	13	2025-02-19
5	14	2025-02-20
5	15	2025-02-21
6	6	2025-02-10
6	7	2025-02-11
6	9	2025-02-13
6	10	2025-02-14

Expected Output

ID	FIRST DATE	LAST DATE
2	2025-02-03	2025-02-05
3	2025-02-14	2025-02-14
4	2025-02-03	2025-02-03
5	2025-02-19	2025-02-21
6	2025-02-10	2025-02-11
6	2025-02-13	2025-02-14

Much appreciated any assistance

Kind Regards,

Dan

Developer

Accepted answers

Luke_C

Hi @DanielCarro

Thanks for clarifying the logic. I've made some inferences but a multi-row tool to check for consecutive months should get this to work:

The formula checks to see if the month is sequential for each ID otherwise it assigns a new sequence.

New Workflow4.yxmd

All comments

Luke_C

Hi @DanielCarro

The summarize tool should be able to do this for you. Depending on your definition of first/last, you will want to use the min max or first last options in the tool. The former is based on the dates themselves regardless of order, the latter is based on the order they appear in the data.

DanielCarro

Hi @Luke_C ,

There is a logic. For example, ID 6, it has a sequence 6 -7 which needs the first date 10/02/2025 to 11/02/2025 and for ID 6 which needs first date 13/02/2025 - 14/02/2025. The month record is the consecutive day sequence.

In some cases, you just have one day which will be the same day, for example with ID3

Luke_C

Hi @DanielCarro

Thanks for clarifying the logic. I've made some inferences but a multi-row tool to check for consecutive months should get this to work:

The formula checks to see if the month is sequential for each ID otherwise it assigns a new sequence.

New Workflow4.yxmd

DanielCarro

Hi @Luke_C ,

It worked perfectly. Thank you

Quick Links

This months top contributors

AkimasaKajitani 387

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335