How to select the last month date in a column, per Vin Number

[JohnDella]

Hello All,

I am still fairly new to ALteryx, and I am looking for some guidance on how to select the last day of the month in a column, for each VIN number.

In the example, my data looks like this:

Date	TotalMiles	VIN #
5/6/2020	84020	019243
5/2/2020	84020	019243
4/30/2020	84020	019243
4/28/2020	84020	019243
4/26/2020	84020	019243
5/12/2020	91539	228315
5/10/2020	91505	228315
3/30/2020	91505	228315

From the Above Data, I want to convert it into the Below: 

Vin #	MARCH 2020	March 2020 Miles	April 2020	April 2020 Miles	MAY 2020	May 2020 Miles
019243	NA	NA	04/30/2020	84,020	5/6/2020	84,020
228315	3/30/2020	91,505	NA	NA	5/12/2020	91,539

This is small sample data, I will have a list of 1000+ Vin#s with various dates.

Please let me know if any of this is possible.

Thanks

[Emil_Kos · Accepted solution]

Hi,

I believe my solution requires a little bit more work to make it automated(as the order of columns in the perfect order) but I think it will be something that you can work with.

[PhilipMannering]

Hi John,

This might help get you started,

[JohnDella]

Thank you. This was very helpful