filter out data based on multiples of information in one column

[maygross]

Hi,

I'm currently building a report to track the billing cycles for my business. We have multiple customers with more than one billing cycle, and I would like to only capture the customers that have all their billing cycles closed, and exclude the ones that have 1 or more open cycles. The data looks like this:

bill cycle	customer	status
1	abc	closed
2	abc	open
3	abc	closed
1	xyz	closed
1	lmno	closed
3	xyz	Closed

I want to get it to exclude any customers that still have open cycles.

bill cycle	customer	status
1	xyz	closed
3	xyz	closed
1	lmno	closed

What would be the best approach here? I know that cross tab wouldnt be helpful because the values I need (status) arent numerical. I cant really filter out the closed status's because that would still include the customers with open cycles, and I only want the ones who have all of their cycles closed.

Thanks,

May

[binuacs]

@maygross One way of doing this with the multi-row tool

[Prometheus · Accepted solution]

There may be another way to do it, but what I did was first, normalize the data so "closed" was the same as "Closed." Then I used a Summarize tool to group by customer and status then another Summarize tool to group by customer and concatenate statuses. Next I used a Filter tool to only allow records to pass if they do not have the word "Open" in the concatenated status. Last thing is I joined back to the original data on customer and got what you're looking for.