How to re-arrange the data from rows containing dates to Quarterly columns in Excel automatically either by using formulas or Macros/VBA

Question

I want to re-arrange the data in same excel sheet. The data have a column Period Date with dates in it and corresponding ROAA Values. I want to rearrange the ROAA values on the basis of Quarters columns picked from Period Date Column.

So that i can have ROAA values on the basis of Quarter and can delete duplicate rows and can have data of one bank in one row only.

I tried using formulas and managed to populate data quarterly, but need help in populating the same bank data in one row only and deleting the rest duplicate rows.

Answer 1

Use Pivot Tables to resume your data:

Create a PivotTable to analyze worksheet data

I took field Issuer Name into rows section, field Value into Values section and Date into Columns Section.

Note I created a group in the date field, grouping by year and quarter.

Group or ungroup data in a PivotTable

Pivot Tables is your best option here, because if you add new rows to your table, you just need to update your Pivot Table and it will rearrange everything.