Transpose? Cross Tab? Not sure.

Question

I want to transform my data so that each instance of effective data is in a new field. I've attached test data below.

Start:

Rule TypeJurisdiction TypeStateDate RuleEffective DateDefaultStateKansasBeginning01/01/2021DefaultStateKansasBeginning01/01/2020DefaultStateAlaskaBeginning01/01/2020DefaultStateAlabamaBeginning01/01/2021DefaultStateAlabamaBeginning01/01/2020

End:

Rule TypeJurisdiction TypeStateDate RuleEffective Date 1Effective Date 2DefaultStateKansasBeginning01/01/202001/01/2021DefaultStateAlaskaBeginning01/01/2020 DefaultStateAlabamaBeginning01/01/202001/01/2021

Test Data.xlsx

binu_acs · Answer

@bsolove123  I thought of add another way of doing this - using Multi-Row formula

Combine3.yxmd

JarekSkudrzyk · Answer

@bsolove123 @Qiu

just added sort tool to @Qiu 's solution to show earlier dates first (will work ok in this case and other cases where the date is in Date format)

0408-bsolove123_js.yxmd

Qiu · Answer

@bsolove123 
I usually consider this, use Cross Tab for vertical to horizontal, Transpose for Horizontal to Vertical.

0408-bsolove123.yxmd