Summarize with null entries

fferraribr

Fellow friends! 

I'm trying to solve this question, but unfortunately I didn't figured out how to do that.

I have this input:

Customer Name	Purchase Order	Invoice Date
Mike	1	08-28-2018
Mike	1	08-25-2018
Mike	1	08-31-2018
Smith	2	08-27-2018
Smith	2
Alice	3
Alice	3
Alice	3	08-31-2018

Note that I have blank cells and they are my information that purchase order still pending, or in other name, they're "open".

Rules:

If they are blank cells in invoice date, purchase order grouping should be blank, this shows me that we didn't send all the purchase orders items, and the status will be Open

If there are dates in invoice date, should bring the maximum/last date and the status will be Closed.

This is the output that I'm expecting:

Customer Name	Purchase Order	Invoice Date	Status
Mike	1	08-31-2018	Closed
Smith	2		Open
Alice	3		Open

I have tried using summarize tool and grouping by purchase order and using max invoice date, but if I have both blank cells and date in a purchase order group, I got the max date when should be blank.

File attached. If someone has a solution/tip for this case I will be glad!

Thanks. 

example.yxzp

bbak

Here you go!

First I created a new column called Nulls with a formula that set the value to one or zero based on if the invoice date existed. Then I used a summarize to group by Customer Name and Purchase order and then summed this null column. By doing this, I was able to see which purchase orders had null values that existed. Then by using another formula tool I could easily say if the sum of the nulls was greater than zero, set the status to Open. 

I used another summarize tool to get the max invoice date of each purchase order and then joined this back into the stream. Then I used one final formula tool that said if the status was open, set the invoice date to null. Finally I sorted by purchase order number and everything looks good to go!

solution.yxzp