Need help

[ashaik1]

Hello - Taking the Impact column as a base in consideration with the Account/Department/Intercompany i need to derive the Journal type column. 

Note:  The intercompany column plays an important role only in the case of 6 and 7 where Accounts and departments are same but one side the intercompany value is blank and other side is populated.

The below are the different kind of scenarios of journal postings happen in the system.

For example: Journal entry 1, 8, 5, 6 and 7 are B/S Only  but having different scenarios.

• Journal entry 1 is B/S only with different accounts( Nature is only Assets) and same departments (It may have or may not have intercompany values) - Then Journal type is B/S reclass
• Journal entry 8 is B/S only with different accounts( Nature is Liability/ Assets) and same departments (It may have or may not have intercompany values) - Then Journal type is GND reclass
• Journal entry 5 is B/S only with same accounts,  but different departments (It may have or may not have intercompany values) - Then Journal type is DEPT transfer
• Journal entry 6 and 7 -  same accounts, same departments but one line is having intercompany and other line does not - Then journal type is Intercompany transfer

Journal Entry	Account	Department	Intercompany	Nature	Impact	Journal type
1	110000	12345	DEF	Asset	B/S Only	B/S Reclass
1	120000	12345	DEF	Asset	B/S Only	B/S Reclass
8	230000	54321	LMN	Liability	B/S Only	GND Reclass
8	130000	54321	LMN	Asset	B/S Only	GND Reclass
2	410000	12134		Revenue	IS Only	I/S reclass
2	420000	12134		Revenue	IS Only	I/S reclass
9	440000	54321		Revenue	IS Only	GND Reclass
9	540000	54321		Expense	IS Only	GND Reclass
3	100000	21223	GHI	Asset	B/s and IS	MAN PL ADJ
3	400000	21223		Revenue	B/s and IS	MAN PL ADJ
4	220000	31234		Liability	B/s and IS	MAN PL ADJ
4	500000	31234		Expense	B/s and IS	MAN PL ADJ
5	100000	99999		Asset	B/S Only	Book to Book transfer
5	100000	88888		Asset	B/S Only	Book to Book transfer
6	100000	12345	ABC	Asset	B/S Only	Intercompany transfer
6	100000	12345		Asset	B/S Only	Intercompany transfer
7	200000	12345	DEF	Liability	B/S Only	Intercompany transfer
7	200000	12345		Liability	B/S Only	Intercompany transfer

This is how i need to define the Journal type. Please help

[aatalai]

@ashaik1 based on this logic

 same accounts, same departments but one line is having intercompany and other line does not - Then journal type is Intercompany transfer

then why isn't 3 also intercompany - is i t because of the differnet accounts? 

Also what is the logic for MAN PL ADJ classification?

Also will there always be 2 lines?

[ScottLewis]

A couple of ideas. As the previous comment noted, we're missing some of the logic to just write it. I assume Man PL ADJ is some form of manual JE and that those are a pain to classify by nature and also that the reason JE 3 isn't Intercompany is because the manual code overrides Intercompany.

1. Multi row formulas. The mult row tool lets you write the logic in place but you're going to have to nest some conditionals. I would likely approach it as writing a formula that works for the second row in each JE (which you can find by checking for blanks in row +1 on one of the data columns) and then a second formula that backfilled that into the first row of each.

2. Transform the data so that each JE is a single row with the information from both sides. This will let you use regular formulas and not worry about the conditionals. Assuming your entries are all two sided the attached workflow does the transformation.

[ashaik1]

For MAN PNL ADJ - journal entry is posted between Asset and Revenue or Liability and Expense or vice versa. journal entry 3 cannot be intercompany transfer as it is not same account and same department. there could be more lines in a journal entry.Thanks

[ScottLewis]

Added the formula tool to the transform example and put in the logic to transform back to JE format. 

The nested IF is a monster and may not contain all your cases even so.

That said, it classifies the example rows properly. 

Should just be tuning the logic and the output formats from here.

You also might want to do some amount of data prep on the incoming fields in case you get different versions of capitalization, spacing, etc. As written it is going to be sensitive to that stuff.

[ashaik1]

For MAN PNL ADJ - journal entry is posted between Asset and Revenue or Liability and Expense or vice versa. journal entry 3 cannot be intercompany transfer as it is not same account and same department. there could be more lines in a journal entry.Thanks