Combine 2 rows that match a unique identifier (first n)

Question

Can someone help me with this challenge? Looking to combine the 2 rows that have different dates but match on first 11 characters in IP Number.  In the result table, I'd like the min. of the two start dates and max. of end dates and sum of amount & days.  Thank you

NameStart DateEnd DateReport DaysSumIP NumberMove Start DateMove End DateTile NumberCodeXYZ1/10/20221/31/20224$2,000WO.12.34567.0011/9/20222/1/20229InternalXYZ1/11/20222/2/20225$3,500WO.12.34567.0021/10/20222/3/202210ExternalABC1/5/20223/31/20226$1,500DP.34.56789.0011/6/20222/28/20223Internal

LOOKING FOR THIS RESULT

NameStart DateEnd DateReport DaysSumIP NumberMove Start DateMove End DateTile NumberCodeXYZ1/10/20222/2/20229$5,500WO.12.34567.0011/9/20222/3/20229InternalABC1/5/20223/31/20226$1,500DP.34.56789.0011/6/20222/28/20223Internal

NeilParrott · Answer

@MROGK

The main line is similar to Matthew's approach; however, this workflow adds a couple other pieces to consider. I always like to see if there is additional info that could be added to help with future considerations.

Note: you could reduce the amount of Date Time Tools by using a transpose prior to the Date Time in order to stack the dates into the "value" column. Then you can use that one column on the dates. A cross tab will then help you revert back to the wider column view.

UniqueID_combine.yxmd

Matthew · Answer

a summarise tool should be able to do it

example.yxmd