Transaction Booking Output

[JSheppard98]

Hi there, Just wondering if anyone may assist with the below transaction data query. I am looking to read transactions and just ideally pull the below 3 columns for example. 

To then output into a sheet like below, using the original transaction data to create 3 new columns (highlighted below yellow). These columns consist of an excel formula;

"=LEFT($K$4,7)&$A5&RIGHT($K$4,8)" and

"=-L$4*$G5" and

the third would just copy the template description the same.

Is this possible or easy to do? I've attached the excel file with mock data so you may be able to see/visualise the input template data and desired output data of where the formula would go/drag down to based off of column A.

Thank you!

[KimLamNg]

@JSheppard98 This is possible. The general idea is to create a column for output file path that contains the range you want to write the data to in the output file. Just a follow-up question - Will the number of entities will change? 

[JSheppard98]

Amazing. The number of entities may change so ideally would be dynamic based on the input transaction data, so if there were 6 rows instead of the 2 rows of transaction data, it would use the same logic for the first 3 columns in the output. If that makes sense? 

[KimLamNg]

Oh sorry I didn't read the data carefully. I actually meant do you always output the transactions to either column K-M or O -Q? How do you determine which transaction should show up in K-M vs. O-Q?

[JSheppard98]

No worries my mistake!! Yeah so the 1st row transaction would go to K-M, then row 2 would go O-Q and row 3 would go to S-U etc etc. Just by order of the transaction data if it can be translated into the column basis. 1st goes K-M, 2nd to O-Q etc just leaving a gap in between, no specific requirement just ease of working through transaction data order ideally as there will be more than just 2 but simplified for the example. Thanks!

[KimLamNg · Accepted solution]

@JSheppard98 Attached is a draft workflow to demonstrate the idea. It only output the transaction data to the range specified in the formula tool. The output file is created as a new file but you can make adjustments to write data to an existing file with headers etc.