Isolating $ Amounts by Month

Re: Isolating $ Amounts by Month
Quote Originally Posted by jrean042 View Post
Hello all,

I want to ultimately be able to create a standard table with the account numbers down the left side, and the 12 months across the top, and the values filled in.
My thought is to make a composite key, something like 500611_January then in the February column say February = 500611_February – 500611_January. But this seem like a lot of room for error,

Any suggestions?
While the above format may look good, it is not a good arrangement for data analysis. Generally, it is better to "go deep" than to "go wide." Instead of spreading the months across columns (or worse yet, across sheets), use a format similar to the picture. With data in this format you can produce a display like the one you describe easily with a pivot table. Also you will be able to create any number of different reports.

This could work, but you would have to translate the month into true dates. Assume all transactions happen on the first or end of the month. E.g. New Date = DateValue( "1 " & [@[Month]] & " 2019").

I agree that for reading purposes stringing the months across the columns is great but this kind of display can be created easily by a pivot table from normalized data.

Now if you want to do anything other than look at this data in exactly this format. it will take a lot of custom formula writing or VB code.

Post #5 has the data in normalized format.

If you want to get any other display of the data, you can most likely get it from normalized data using a pivot table, match / index or SUMIFS formulas.

Also, the reason I was pushing for "real" dates is that they sort in real calendar order and not alphabetical order. Also, with pivot tables you can group dates by month, quarter and year (and other combinations like weeks).