Data Preparation

[Ragini1]

Hi All,

I am trying to build logic for the below scenario :

Input>>

Account	Type	Value
1000	A	20
2000	B	-30
3000	A	10
4000	A	10
5000	B	-10

Output>>

Account	Type	Value	Comments
1000	A	20
2000	B	-30	Offset
3000	A	10
4000	A	10
5000	B	-10	Offset

Can someone please help with this?

Regards

[Luke_C]

Hi @Ragini1 

You can use a formula tool with an if statement for this

[Ragini1]

My question was, if Type =B and if sum of any combination of Type A +type B = 0 then in comments column for Type B , I should have comment as offset entry

[Luke_C]

Hi @Ragini1 

Thanks for laying out the logic. Try this:

[Ragini1]

Thank you for the help.

Input>>

Account	Type	Value
1000	A	20
2000	B	-30
3000	A	10
4000	A	10
6000	B	30
5000	B	-10

Output>>

Account	Type	Value	Comment
1000	A	20
2000	B	-30	Offset
3000	A	10
4000	A	10
6000	B	30
5000	B	-10	Offset

The logic doesn't work if we have the above input. Can you please provide your feedback?

[Luke_C]

I think you'll need to provide some more requirements. How are we grouping the totals of A & B? In this case doing total of A minus total of B does not = 0, so nothing is flagged. Its not clear to me how we would systematically get to the answer you're looking for. If you're doing it in excel now can you share the formulas you're using?

[mattnason1 · Accepted solution]

Here's how I would handle if I understand what you're trying to do.

Just a quick rundown of my process.

1) Sort records by dollar low to high.

2) Create a running total of the values.

3) Join on the dollar if they zero out.

4) Take the highest dollar value from Group A that gets offset.

5) Add the comment offset to every record from Group B that adds up to the Group A record.

I might be misunderstanding what you're trying.  But it might work for your purposes.  It did work for both examples.