PivotTable Tools, Design, Report Layout, Show in Tabular Form and sort rows and columns A>Z. Adjust cell references accordingly (in D3 =IF(AND(A3=A2,C3=C2),D2+1,1). add a blank row at the very top and move A2:D2 Happy to explain further/try again (I've not really tested this) if does not suit.ĮDIT (To avoid second block of steps above and facilitate updating for source data changes) Hopefully result should be as highlighted in yellow. Delete blank rows in copied range with shift cells up (may best via adding a column that counts all 12 months).Delete contents of L2:L(last selected cell).Filter copied range and for ColumnL, select Row Labels and numeric values.Delete top row of copied range with shift cells up.Copy pivot table and Paste Special/Values to, say, L1.If not (you hard taskmaster), continue but beware that the following steps would need to be repeated each time the source data changes. I’m hoping this would be adequate for your needs because pivot table should automatically update (provided range is appropriate) in response to additional data with refresh. Create a pivot table from A1:D(last occupied row no.).Enter in D2 =IF(AND(A2=A1,C2=C1),D1+1,1) (One way to deal with what is the tricky issue of multiple entries for the same person for the same month).I fear this might turn out to BE the long way round but could depend on how big your data set is – presumably more than four months for example.Īssuming your data is in ColumnA:C and has column labels in Row 1, also that Month is formatted mmm(this last for ease of sorting):
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |