Converting a Column to row with multiple citeria

Question

Please see attached.

Sheet 1 is the data and Sheet 1(2) is the desired output.

I have 2 columns one with event and one with date, I need to show how event moves from 1 loc to another and days it takes to move from one event to another.

Sort should be based on column called Probability which can go from 0 -  100 in multiples of 5.I tried to use Transpose and then cross tab, but i need all the data and not aggregate. Also tried multirow formula.

Thankyou in Advance.

Sample.xlsx

Bren_Spill2 · Accepted Answer

@mehbpar - see attached. Thanks!

gawa · Accepted Answer

hi @mehbpar

While @Bren_Spill2 suggested WF by Multi Row Formula tools, I did in different way: Tile and Join. You can use whichever you feel more familiar and comfortable since there is no big difference.

In both cases, as a general recommendation, do not forget to put Sort tool right after Input tool to be sure of data being sorted by ID and Date so that Multi Row formula( way of @Bren_Spill2)/Tile(may way) would work properly as intended.