Create JE transaction IDs based on entries

I am trying to create transaction IDs for journal entries in my output. Non-InterCompany are two line entries and Intercompany are four line entries. I am able to get the transaction ID for Non-IC by using the formula FLOOR(([RecordID]-1)/2)+1. However, I do not know how to get transaction IDs for four line entries. I am highlighting my expected output in the picture below. Thanks!

Reporting

Output

Optimization

Data Investigation

Accepted answers

Watermark

Here is a rework using the InterCo column and it's working with this data set.

I'm also including the test data spreadsheet so you can point the input to it, and it should work.

Let me know if you have questions.

InterCoJE.yxmd

Inter Co JE.xlsx

All comments

Watermark

You can use a multi row formula.

I took on assumption that you can rely on the 'abc' in project, but if that isn't the trigger, than we can talk and identify a couple other things I see, but hard to be sure if you can rely on without really knowing your data. This works with the sample provided.

Inter Co JE.jpg

Please mark as solution if this works. If you have questions, let me know.

InterCoJE.yxmd

Mariamfatimamirza

Hi, thanks for taking a look, but this doesn't seem to be working at my end. Project code 'abc' is not dependable. I can tell that where interco? = "Yes", that's where the four line entries begin.

Watermark

Here is a rework using the InterCo column and it's working with this data set.

I'm also including the test data spreadsheet so you can point the input to it, and it should work.

Let me know if you have questions.

InterCoJE.yxmd

Inter Co JE.xlsx

Mariamfatimamirza

Thank you! This worked 😀

Quick Links

This months top contributors

SophiaF 432

AkimasaKajitani 390

mceleavey 388

mbarone 337

michael_weaver 335