I've been struggling to get a solution to this business related problem for a couple of days now.. Can someone please help with finding the right approach / formula to get the desired solution?
Two tables:
Table 1.. (where the results are to be displayed in Attribute Count column) Attribute Count column currently has the "desired result", manually derived.
<tbody>
</tbody>
Table 2.. (Data Source)
<tbody>
</tbody>
I want to write a formula that helps me get the "Attribute Count" column automatically populated with the count. The count should represent:
How many "unique" accounts with 'IB100' attribute have had a meeting with us in each city?
For example: For Tokyo, the data source says a meeting happened with Mathew of AA7 and Company Attribute is IB100. Also, a meeting also happened with John of AA1 based in Tokyo, but its Company Attribute is null / nothing. So, the count in Table 1 is shown as 1.
Hope I have been able to explain the query.
Two tables:
Table 1.. (where the results are to be displayed in Attribute Count column) Attribute Count column currently has the "desired result", manually derived.
City | Attribute Count |
Beijing | 0 |
New York | 2 |
Tokyo | 1 |
Seoul | 0 |
Paris | 0 |
Mumbai | 0 |
London | 1 |
<tbody>
</tbody>
Table 2.. (Data Source)
Account | Executive | Account Attribute | City | Meeting Date |
AA1 | John | Tokyo | Feb-12 | |
AA2 | Mike | IB100 | London | Mar-12 |
AA2 | Tom | IB100 | London | |
AA2 | Harry | IB100 | London | Aug-12 |
AA3 | Shaun | IB100 | Mumbai | |
AA4 | Greg | Beijing | Jun-12 | |
AA5 | Sandy | IB100 | New York | Nov-12 |
AA5 | Frank | IB100 | New York | Nov-12 |
AA6 | Terry | IB100 | New York | Jan-12 |
AA6 | Tony | IB100 | New York | |
AA7 | Mathew | IB100 | Tokyo | Feb-12 |
<tbody>
</tbody>
I want to write a formula that helps me get the "Attribute Count" column automatically populated with the count. The count should represent:
How many "unique" accounts with 'IB100' attribute have had a meeting with us in each city?
For example: For Tokyo, the data source says a meeting happened with Mathew of AA7 and Company Attribute is IB100. Also, a meeting also happened with John of AA1 based in Tokyo, but its Company Attribute is null / nothing. So, the count in Table 1 is shown as 1.
Hope I have been able to explain the query.