GROUPING AT THE ACCOUNT LEVEL

[nick22]

Hello,

I'm working on a formula to dynamically count the number of rows and group them together based up a condition:

My data looks like this

account #               product indicator              dollar value

1                                        c                                   100

1                                        n                                   150

1                                        n                                   0  

1                                        n                                   0 

1                                        n                                   0

2                                        n                                    250

The formula has assign if the account is "active" or "non active" based up the Product indicator = "c" and dollar value > 0.

I can do this but only at the line level with a basic formula. I need the formula to say if for the account in question if the criteria is met  at least once then "active" else "non active"

any suggestions are greatly appreciated,

Thank you,

Nick

[estherb47 · Accepted solution]

Hi @nick22 

I think you're almost there!! A filter to find those that meet the active criteria, and then a Find Replace to bring in the status you want to use for any of the rows that are tagged as active. That will create a second status column. Empty values in that column indicate a status of inactive. Another formula tool copies over the inactive value.

Let me know if that helps.

Cheers!

Esther

[nick22]

Hi EstherB47,

Thank you for getting back to me so quickly, I think this will work but I'm having trouble configuring the find and replace tool. Not all of my fields are available, so I cannot set the find and replace to look at account #.

Any suggestions?

[nick22]

Hi @estherb47 

Thank you for getting back to me so quickly, I think this will work but I'm having trouble configuring the find and replace tool. Not all of my fields are available, so I cannot set the find and replace to look at account #.

Any suggestions?

[estherb47]

@nick22 

Hmm. Find and Replace only works with string fields. So, if your account number is a number field, the tool won't map it. Could you change the account # to a text field?

Cheers,

Esther

[nick22]

@estherb47 

THANK YOU SO MUCH, WORKS PERFECT!