How to convert rows to column and do a groupby in Excel

Question

My data in Excel looks like below

Date,     value
2017/1/1, 5
2017/4/1, 6
2017/4/5, 12
2017/7/2, 15
2018/4/1, 50
2018/7/7, 11
2017/1/1, 5

I would like to convert this data to the following format where the columns are the year/month ad the values are in the rows

  2017/1   2017/4  2017/7  2018/1  2018/4
   5           6     15      5       50
               12

How can I perform this data transformation in Microsoft Excel 2017?

Why have you excluded two of the values from your data in your desired results? — Ron Rosenfeld, Aug 25 '18 at 17:21
This cannot be done with a pivot table as a pivot table wants to aggregate the values in some way. Formulas would require the use of array type formulas and will work if the data set is not too large. VBA will be your best choice. — Scott Craner, Aug 25 '18 at 17:31

score 1 · Answer 1 · answered Aug 25 '18 at 17:41

1

You can do this by counting the frequency of the months after sorting the records and do grouping based on that.

1 Answers1