Macro user case?

[xariet]

Hi!

I am really confused with how to approach this task, and I guess Macro will be an answer, but I can't figure out how to use it. I have the below information. I need to calculate total and property value, related to account 702684. Total and property value should be related to account 702684 through common properties

Property related to account 702684: 6,4,3,5. Logic explanation: we bring properties related to account 702684 , which are 6 and 4. Then we look which other accounts have either of those properties. Account 7012342 has property 4, but it also has property 3. As we already have property 4, we just add property 3 to our list. Now we look for accounts who have properties 6,4 or 3. Account 5207175 and account 56789-01 have property 3, also account 56789-03 has property 4. Account 56789-03 also has property 5, which we bring on our list. Now we need to sum up all property values which relate to account 702684 = 6+4+3+5=1326000+1800000+700000+210000.

And we need to sum up Totals for the same accounts: 70+550+500+230+100

I would really appreciate some directions for the workflow process to resolve this task.

PS. I can't to this task manually ( using filters, summarizing, ect) as this is just an example from the huge dataset, where those totals should be calculated automatically per each account in that dataset.

Asset customer	Customer	account	property	property value	Total
1324	A	6930911	1	690000	300
1324	A	699229	2	310000	200
1324	A	699229	1	690000	200
1324	A	5207175	3	700000	500
1324	A	7012342	3	700000	550
1324	A	7012342	4	1800000	550
1324	A	702684	6	1326000	70
1324	A	702684	4	1800000	70
1324	B	56789-01	3	700000	230
56789	B	56789-03	5	210000	100
1324	B	56789-03	4	1800000	100

[Yoshiro_Fujimori · Accepted solution]

@xariet ,

Here is my solution.

I do not fully understand your calculation for accounts {6,4,3,5} below, as there are other values with the same account on the table.

1326000+1800000+700000+210000

70+550+500+230+100

But I believe the critical part is how to find all the related accounts. I hope this helps.

Macro

This iterative macro will stop when the number of records found is the same as the last iteration.

Main workflow

The main workflow receives the related accounts from the macro and summarizes the original table.

Output

property	Max_property value	Max_Total
4	1800000	550
5	210000	100
6	1326000	70
3	700000	550

Sum_Max_property value	Sum_Max_Total
4036000	1270

[OTrieger]

@xariet 

You mentioned that your database is much bigger.
In the above description you started from one specific account, will this account always will be the first to start with, or this is only a generic explanation? This is important as based on your first selection you then check the relating to the other accounts.

As if this is only a generic description then you will need to concatenating the properties type based on the accounts so you will be able to see how the different accounts relating to the different properties, then you will be able to assign the value and total and sum the up.