I have a data-set that is made up of "accounts" and the dates at which various states of implementation are completed. I am trying to aggregate them so that each account only has 1 row per account. The data can have the same account undergoing 2 different implementations to the unique key and/or grouping is Account ID and Start date. If any of the rows in a column of the grouping have a non-null value, then all null fields should be changed to that value. I was toying with various multi-row formulas, but I can't quite get it. The data looks like this Account IDStart DateImplementationDevelopmentStagingProductionABC11/1/2020NullNullNullNullABC11/1/2020NullNull7/1/2020NullABC11/1/2020Null6/1/2020NullNullABC11/1/20205/1/2020NullNullNullABC9/1/2020NullNullNull6/1/2020ABC9/1/2020NullNull5/1/2020NullABC9/1/2020Null4/1/2020NullNullABC9/1/20203/1/2020NullNullNull I'm trying to get it o look like this Account IDStart DateImplementationDevelopmentStagingProductionABC11/1/20205/1/20206/1/20207/1/2020NullABC9/1/20203/1/20204/1/20205/1/20206/1/2020 I've attached the same sample data-set as noted if needed. Thanks for any help! Craig Sample.csv

Aggregate Non-Nulls to overwrite nulls

I have a data-set that is made up of "accounts" and the dates at which various states of implementation are completed.

I am trying to aggregate them so that each account only has 1 row per account.

The data can have the same account undergoing 2 different implementations to the unique key and/or grouping is Account ID and Start date. If any of the rows in a column of the grouping have a non-null value, then all null fields should be changed to that value.

I was toying with various multi-row formulas, but I can't quite get it.

The data looks like this

Account ID	Start Date	Implementation	Development	Staging	Production
ABC	11/1/2020	Null	Null	Null	Null
ABC	11/1/2020	Null	Null	7/1/2020	Null
ABC	11/1/2020	Null	6/1/2020	Null	Null
ABC	11/1/2020	5/1/2020	Null	Null	Null
ABC	9/1/2020	Null	Null	Null	6/1/2020
ABC	9/1/2020	Null	Null	5/1/2020	Null
ABC	9/1/2020	Null	4/1/2020	Null	Null
ABC	9/1/2020	3/1/2020	Null	Null	Null

I'm trying to get it o look like this

Account ID	Start Date	Implementation	Development	Staging	Production
ABC	11/1/2020	5/1/2020	6/1/2020	7/1/2020	Null
ABC	9/1/2020	3/1/2020	4/1/2020	5/1/2020	6/1/2020

I've attached the same sample data-set as noted if needed.

Thanks for any help!

Craig

Sample.csv

Datasets

Preparation

Accepted answers

BradWerner

Hi @csh8428,

You can turn all of your date columns into dates and use the Summarize tool to grab the latest dates. See the attached workflow.

This assumes that you will mainly have each row communicate only one stage date (Implementation, development, etc) for each Account ID/Start Date combination. If you have more than one row per stage date, this will always take the latest one. Just mentioning in case this occurs at all in your data.