Combine two rows which have the same first N characters same

Question

Hi,

Can someone help me with this challenge in combing the two rows that have the same first 11 characters (identifier) in IP Number? I'm looking to create a single row in the output that gives me Min. Start Date and Max. End Date from the 2 rows. The sum & days will be cumulative. I posted the desired result table below

Student NameDM Start DateDM End Date# of daysSumIP NumberBase Start DateBase End DateTag CodeXYZ1/1/20221/31/20224$3,500WO.12.34567.0011/4/20222/2/20228InternalXYZ12/31/20212/1/20225$1,500WO.12.34567.0021/2/20221/31/20229ExternalABC1/5/20223/1/20226$1,500DP.34.34567.0011/4/20222/28/202213InternalDBC2/14/20223/6/20223$2,500WO.45.45634.0012/14/20223/5/202215External

LOOKING FOR THE BELOW OUTPUT

Student NameDM Start DateDM End Date# of daysSumIP NumberBase Start DateBase End DateTag CodeXYZ12/31/20222/1/20229$5,000WO.12.34567.0011/2/20222/2/20228InternalABC1/5/20223/1/20226$1,500DP.34.34567.0011/4/20222/28/202213InternalDBC2/14/20223/6/20223$2,500WO.45.45634.0012/14/20223/5/202215External

Qiu · Answer

@MROGK

I also use the Group in the Summarize Tool.
We need to do some date cleasing, such as the format of Sum, the format for Date.

At the last, you can revert the format back if you prefer.

0406-MROGK.yxmd

Matthew · Answer

it's certainly doable, i imagine a summarise tool will get you 90% of the way there.

group by Left( [IP Number] ,11 )