Data arrangement (for Pivot output)

[JackDanIce]

Hi,

I have a spreadsheet where each row represents a single person.

Column headers (row 4) are: "Days", "Fee", "Invoice amount"

However, above each column header is a date (row 3), e.g. 1 Jan 2018, 8 Jan 2018 etc

Data is updated weekly and then entered onto this sheet. Where 1st of a month is a weekday, the dates above are e.g. 29 Jan 2018 (Monday) and 1 Feb 2018 (Thursday)

This allows weekly data to be accurately updated, but with additional dates (1st of a month, if a weekday) to allow for month-end change

The pivot data needs to show monthly values

How can I "group?" rows 3 and 4 so that the pivot table can show aggregate sums of monthly data? The overall data is in range A5:IR168

Only answer I can think of is for each row 4 header, add a pre or postfix for the month, so that I collapse row 3 into row 4

Any suggestions thank you in advance,
Jack

 

[Kamolga]

If you transpose your data wit paste special, You get a column A with date (Write date as title), Column B with Category (Days/fee/amount/…) and one column per employee. If you do a pivot on that and put Date in rows or columns, you will get them by month.

​

 

[SpillerBD]

Unpivot! Though one of my reads currently is Gil Raviv's book on "Power Query" you can watch Mike doing some of the process.
ExcelIsFun YouTube Trick#10

 

[JackDanIce]

I ended up grouping <date>_<field> into a single row and pivot table unfortunately has too many fields, but does give the output User wants, so they'll have to deal with selecting/unselecting lots of fields, but problem resolved.

Thank you for the replies and suggestions.