Analyzing Transactional Data

[daninikunj]

I am using the data dump form our SAP system to analyze cycle times. The first table below is how I am receiving my dataset. The 2nd table is what I would like, basically trying to calculate the date difference between the release and the receipt of materials.

While the majority of transactions are straightforward, sometimes, there are multiple receipts and releases and I am trying to figure out if there is a way to separate those line items.

In my sample set below, Blue, green, and gray rows represent straightforward transactions with just one receipt and one release. However, the last PO has two receipts and two releases. Also, PO1 has reversal transaction # 102, which cancels the 101 transactions.

Not sure if I can still accomplish calculations with this information. I'd appreciate if anyone can suggest any ideas. The Excel file is attached.

Thank you in advance.Transactions Analysis.jpg

[Bo_Ry]

Please try at
A2:C2
=IF($E2="","",INDEX(Data!$B:$I,AGGREGATE(15,6,ROW(Data!$E$2:$E$17)/(Data!$E$2:$E$17="Release"),ROWS(A$2:A2)),MATCH(A$1,Data!$B$1:$I$1,)))

D2
=IF(E2="","",INDEX(Data!$G$2:$G$17,MATCH(1,INDEX((B2=Data!$F$2:$F$17)*(Data!$H$2:$H$17=F2)*(Data!$D$2:$D$17=101),),)))

E2:G2
=IFERROR(INDEX(Data!G:G,AGGREGATE(15,6,ROW(Data!$E$2:$E$17)/(Data!$E$2:$E$17="Release"),ROWS(E$2:E2))),"")

[daninikunj]

Thanks Bo_Ry. Appreciate the quick response.

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.