latest value in a series of values.

Copper Contributor

I want to go from Table 1 to Table 2 using pivot table or other automatic ways to summarize information.

What is a good way to accomplish this? I would like to do this in a way that is easy to understand for non-technical people to follow (i.e. no installations of additional tools, or coding if possible!)

 

Table 1      Table 2   
CountryStateRatingAgencyDate  CountryStateRatingDate
USGA1A12020-01-01  USGA12020-01-01
USGA2A12019-01-01  USAL22023-01-01
USAL2A12023-01-01      
USAL2A22022-01-01      
USAL3A12021-01-01      
1 Reply

@avallampati 

Create a pivot table based on Table 1.

Add Country, and then State to the Rows area.

Add Rating, and then Date to the Values area.

Click on Rating in the Values area and select Value Field Settings... from the drop-down menu.

Change the summary function to Min.

Click on Date in the Values area and select Value Field Settings... from the drop-down menu.

Change the summary function to Max.

HansVogelaar_0-1719931839082.png

Excel may display the Date field as numbers. If so, select the dates and format as Short Date.

The pivot table should now look similar to this:

HansVogelaar_1-1719931895158.png

Activate the Design tab of the ribbon, to the right of PivotTable Analyze.

Select 'Do Not Show Subtotals' from the Subtotals drop-down.

Select 'Off for Rows and Columns' from the Grand Totals drop-down.

Select 'Show in Tabular Form' from the Report Layout drop-down.

Also select 'Repeat All Item Labels' from this drop-down.

The pivot table should now look similar to this:

HansVogelaar_2-1719932167907.png