[Solved] Summarize data monthwise

Discuss the spreadsheet application
Post Reply
gokulwadghule
Posts: 9
Joined: Wed Sep 08, 2021 10:40 am

[Solved] Summarize data monthwise

Post by gokulwadghule »

Question : - I have bank statement with many transactions during the year in every month. I want to sort the data month wise in sub sheets. what formula shall i use? (Open office Calc)
Windows 10 & Open Office Version 3.1
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Sort Data monthwise

Post by Zizi64 »

Please upload your small, ODF type sample file here.

Are the "month" data located in an induvidual column, or only a combined Date-Time column exists?
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Sort Data monthwise

Post by Villeroy »

Simply sort by date, so you have blocks of years, months and days.

If your dates sort alphabetically ...
1/11/2021
1/12/2021
11/2/2021
2/12/2021
.. then these dates are no dates at all because you did not import the bank statement correctly.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
gokulwadghule
Posts: 9
Joined: Wed Sep 08, 2021 10:40 am

Re: Sort Data monthwise

Post by gokulwadghule »

Below is the Sample Raw data
Date Transaction id Cr. Dr.
01/01/2021 1231s861sd 2000 1000
02/01/2021 1231s861sd 5446412 21213
03/01/2021 4654s3216s5d231 4000 151
04/01/2021 65ew65656wsed 5446412 521851
05/01/2021 65ew65656wsed 25000 1215
06/01/2021 65ew65656wsed 12121 251213
07/01/2021 65ew65656wsed 112221 151
08/01/2021 65ew65656wsed 2000 122133
09/01/2021 65ew65656wsed 5446412 1000
======================================
Expected output is :: -
1000+
1000+21213+
1000+21213+151+
1000+21213+151+521851+
1000+21213+151+521851+1215+
1000+21213+151+521851+1215+251213+
1000+21213+151+521851+1215+251213+151+
1000+21213+151+521851+1215+251213+151+122133+
1000+21213+151+521851+1215+251213+151+122133+1000+


Note : I have done this manually as of now using =F2&"+"" and =+G2&F3&"+", but I want a formula which can calculate the above output from the "Cr." column of Raw Data month wise.
Windows 10 & Open Office Version 3.1
User avatar
karolus
Volunteer
Posts: 1158
Joined: Sat Jul 02, 2011 9:47 am

Re: Sort Data monthwise

Post by karolus »

Hallo

For me your """expected Output""" dont have any relation to your initial question.


You should just select the raw Data-source and: →→Data→Pivottable→create…

pull 'Date' into RowField, and the 'values' into Datafield

later select any cell of Date in in the pivot_output and go to →Data→Group and Outline→Group … choose [x]Month
AOO4, Libreoffice 6.1 on Rasbian OS (on ARM)
Libreoffice 7.4 on Debian 12 (Bookworm) (on RaspberryPI4)
Libreoffice 7.6 flatpak on Debian 12 (Bookworm) (on RaspberryPI4)
gokulwadghule
Posts: 9
Joined: Wed Sep 08, 2021 10:40 am

Re: Sort Data monthwise

Post by gokulwadghule »

karolus wrote:Hallo

For me your """expected Output""" dont have any relation to your initial question.


You should just select the raw Data-source and: →→Data→Pivottable→create…

pull 'Date' into RowField, and the 'values' into Datafield

later select any cell of Date in in the pivot_output and go to →Data→Group and Outline→Group … choose [x]Month

Thanks,

However I this is not the expected output as I want to concatenate entries from coloum "Cr." by "+" sign for whole month which the Date belongs to.

Ex. -- for month of jan : --
If -
Date Cr.
01/01/2021 2000
02/01/2021 5446412
03/01/2021 4000
04/01/2021 5446412
05/01/2021 25000
06/01/2021 12121
07/01/2021 112221
08/01/2021 2000
09/01/2021 5446412
10/01/2021 5000
11/01/2021 15121


Then the output should be -----

2000+
2000+5446412+
2000+5446412+4000+
2000+5446412+4000+5446412+
2000+5446412+4000+5446412+25000+
2000+5446412+4000+5446412+25000+12121+
2000+5446412+4000+5446412+25000+12121+112221+
2000+5446412+4000+5446412+25000+12121+112221+2000+
2000+5446412+4000+5446412+25000+12121+112221+2000+5446412+
2000+5446412+4000+5446412+25000+12121+112221+2000+5446412+5000+
2000+5446412+4000+5446412+25000+12121+112221+2000+5446412+5000+15121+
Windows 10 & Open Office Version 3.1
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Sort Data monthwise

Post by Villeroy »

t106099.ods
(19.11 KiB) Downloaded 230 times
See also: download/file.php?id=38070
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
eeigor
Posts: 214
Joined: Sun Apr 12, 2020 10:56 pm

Re: Sort Data monthwise

Post by eeigor »

gokulwadghule wrote:I want to concatenate entries
2000+5446412+4000+5446412+25000+12121+112221+2000+5446412+5000+15121+
To sum or concatenate? It's damn hard to guess... @Villeroy added.
The pivot table needs to be updated. Consider using 'Multiple operations', autoupdated. The sort order doesn't matter.
Attachments
t106099.ods
(20.2 KiB) Downloaded 219 times
Last edited by eeigor on Tue Sep 14, 2021 1:09 pm, edited 2 times in total.
Ubuntu 18.04 LTS • LibreOffice 7.5.3.2 Community
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Sort Data monthwise

Post by Villeroy »

Let's introduce "arithmetic concatenation".
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
gokulwadghule
Posts: 9
Joined: Wed Sep 08, 2021 10:40 am

Re: Sort Data monthwise

Post by gokulwadghule »

eeigor wrote:
gokulwadghule wrote:I want to concatenate entries
2000+5446412+4000+5446412+25000+12121+112221+2000+5446412+5000+15121+
To sum or concatenate? It's damn hard to guess... @Villeroy added.
The pivot table needs to be updated. Consider using 'Multiple operations', auto updated. The sort order doesn't matter.
Thanks for your response...
Further clarification is this that I don't need Sum of "Cr" column, I just need to concatenate all the values present in "Cr" column depending on the month. Like, for all the dates available from Jan month, I need to concatenate "Cr" column values. See below,

Raw Data of Jan. Month:
Date Transaction id Dr. Cr.
01/01/2021 1231s861sd 2000 1000
02/01/2021 1231s861sd 5446412 21213
03/01/2021 4654s3216s5d231 4000 151
04/01/2021 65ew65656wsed 5446412 521851
05/01/2021 65ew65656wsed 25000 1215
06/01/2021 65ew65656wsed 12121 251213
07/01/2021 65ew65656wsed 112221 151
08/01/2021 65ew65656wsed 2000 122133
09/01/2021 65ew65656wsed 5446412 1000
10/01/2021 4654s3216s5d231 5000 122133
11/01/2021 4654s3216s5d231 15121 1000


And now expected output is:
1000+21213+151+521851+1215+251213+151+122133+1000+122133+1000+

So you can see in the above output that I have concatenated all the values from "Cr" column for Jan. month. For now, I have done it manually, but I need a formula or Macro to get this done as the data which I have is large and does not have fix no. of entries for a month.

I have also attached the sample data file, please look into it for more clarification. If needed.
Attachments
data.ods
(18.92 KiB) Downloaded 228 times
Windows 10 & Open Office Version 3.1
User avatar
JohnSUN-Pensioner
Volunteer
Posts: 876
Joined: Fri Jan 14, 2011 1:21 pm
Location: Kyiv, Ukraine

Re: Sort Data monthwise

Post by JohnSUN-Pensioner »

I am often reproached for using macros when it is enough to think a little and use ready-made built-in tools. What can I do, I'm lazy, I don't always want to think, so I take some old macro and slightly rewrite it for a new task
Attachments
data.ods
Try this (this "pivot table" no need refresh)
(17.17 KiB) Downloaded 225 times
I may not have a lot to give but what I got I'll give to you...
Apache OpenOffice 4.1.5, LibreOffice 6.4.4.2 (x64) on Windows 7
If you think that I did not answer your question, make allowances for my imperfect English
eeigor
Posts: 214
Joined: Sun Apr 12, 2020 10:56 pm

Re: Sort Data monthwise

Post by eeigor »

No macros. Array formula + multiple operations. The year as a filter.
{=TEXTJOIN("+";1;IF((YEAR(A2:A143)=$H$21)*(MONTH(A2:A143)=I21);D2:D143);"")}
If the order of concatenation is important, then the data should be sorted first. This can be done at any time. The output of the results will change automatically.
File attached.
Attachments
Снимок экрана от 2021-09-15 19-01-42.png
data (1).ods
(33.46 KiB) Downloaded 235 times
Last edited by eeigor on Wed Sep 15, 2021 6:12 pm, edited 1 time in total.
Ubuntu 18.04 LTS • LibreOffice 7.5.3.2 Community
User avatar
JohnSUN-Pensioner
Volunteer
Posts: 876
Joined: Fri Jan 14, 2011 1:21 pm
Location: Kyiv, Ukraine

Re: Sort Data monthwise

Post by JohnSUN-Pensioner »

eeigor wrote: {=TEXTJOIN("+";1;IF((YEAR(A2:A143)=$H$21)*(MONTH(A2:A143)=I21);D2:D143);"")}
Believe it or not, I was too lazy to write a macro and first of all I thought about TEXTJOIN(). But then I read the signature of @gokulwadghule "Windows 10 & Open Office Version 3.1" and realized that he would not get to know the TEXTJOIN() function soon.
I may not have a lot to give but what I got I'll give to you...
Apache OpenOffice 4.1.5, LibreOffice 6.4.4.2 (x64) on Windows 7
If you think that I did not answer your question, make allowances for my imperfect English
eeigor
Posts: 214
Joined: Sun Apr 12, 2020 10:56 pm

Re: Sort Data monthwise

Post by eeigor »

Vladyslav, let TS decide for himself what is easier for him. Macros, array formulas, and multiple operations are all complex topics. Some of this will have to be dealt with... And update the spreadsheets at the same time
Ubuntu 18.04 LTS • LibreOffice 7.5.3.2 Community
eeigor
Posts: 214
Joined: Sun Apr 12, 2020 10:56 pm

Re: Sort Data monthwise

Post by eeigor »

New formula w/o TEXTJOIN()
{=LEFT(CONCAT(IF((YEAR(A2:A143)=$H$37)*(MONTH(A2:A143)=I37);D2:D143&"+";""));LEN(CONCAT(IF((YEAR(A2:A143)=$H$37)*(MONTH(A2:A143)=I37);D2:D143&"+";"")))-1)}
Attachments
data (2).ods
(33.77 KiB) Downloaded 252 times
Ubuntu 18.04 LTS • LibreOffice 7.5.3.2 Community
User avatar
JohnSUN-Pensioner
Volunteer
Posts: 876
Joined: Fri Jan 14, 2011 1:21 pm
Location: Kyiv, Ukraine

Re: Sort Data monthwise

Post by JohnSUN-Pensioner »

Yes, for LibreOffice it would work.
However, for OpenOffice 4.1.9 (let alone 3.1) you will get =MULTIPLE.OPERATIONS(G$37;$I$37;$I38) with result #NAME? - CONCAT() function is not available here (as well as TEXTJOIN())
I may not have a lot to give but what I got I'll give to you...
Apache OpenOffice 4.1.5, LibreOffice 6.4.4.2 (x64) on Windows 7
If you think that I did not answer your question, make allowances for my imperfect English
eeigor
Posts: 214
Joined: Sun Apr 12, 2020 10:56 pm

Re: Sort Data monthwise

Post by eeigor »

It's time to start upgrading the software. But the questioner also asked for a macro. Therefore, the issue is resolved.

NOTE:
eeigor wrote:New formula w/o TEXTJOIN()
{=LEFT(CONCAT(IF((YEAR(A2:A143)=$H$37)*(MONTH(A2:A143)=I37);D2:D143&"+";""));LEN(CONCAT(IF((YEAR(A2:A143)=$H$37)*(MONTH(A2:A143)=I37);D2:D143&"+";"")))-1)}
All this end of the formula
LEN(CONCAT(IF((YEAR(A2:A143)=$H$37)*(MONTH(A2:A143)=I37);D2:D143&"+";"")))-1
is needed only to substract the tailing + sign (see: -1)
and the formula itself is quite simple.
Ubuntu 18.04 LTS • LibreOffice 7.5.3.2 Community
User avatar
JohnSUN-Pensioner
Volunteer
Posts: 876
Joined: Fri Jan 14, 2011 1:21 pm
Location: Kyiv, Ukraine

Re: Sort Data monthwise

Post by JohnSUN-Pensioner »

eeigor wrote:It's time to start upgrading the software.
We do not know what circumstances the topikstarter has. Perhaps he does not have the ability to change the composition of the software. (I came across organizations - for example, banks - where they take security so seriously that any innovation is coordinated with special services for 6-8 months. And it does not always get approval. Therefore, today they work with MS OFFICE 2003 ;-))
I may not have a lot to give but what I got I'll give to you...
Apache OpenOffice 4.1.5, LibreOffice 6.4.4.2 (x64) on Windows 7
If you think that I did not answer your question, make allowances for my imperfect English
eeigor
Posts: 214
Joined: Sun Apr 12, 2020 10:56 pm

Re: Sort Data monthwise

Post by eeigor »

A combined method is also possible: multiple operations plus the simplest implementation of the CONCAT function.
Ubuntu 18.04 LTS • LibreOffice 7.5.3.2 Community
User avatar
JohnSUN-Pensioner
Volunteer
Posts: 876
Joined: Fri Jan 14, 2011 1:21 pm
Location: Kyiv, Ukraine

Re: Sort Data monthwise

Post by JohnSUN-Pensioner »

Of course, you are right - there are many solutions to this problem. Even manually reviewing all the data and writing out a long string of values through the plus sign is also a solution. Accountants have been doing this for many years on paper, long before the advent of computers (and have done it for many years after that).
I may not have a lot to give but what I got I'll give to you...
Apache OpenOffice 4.1.5, LibreOffice 6.4.4.2 (x64) on Windows 7
If you think that I did not answer your question, make allowances for my imperfect English
eeigor
Posts: 214
Joined: Sun Apr 12, 2020 10:56 pm

Re: Sort Data monthwise

Post by eeigor »

eeigor wrote:A combined method is also possible: multiple operations plus the simplest implementation of the CONCAT function.
=MID(CONCATINCOLUMN(IF((YEAR(A2:A143)=$H$53)*(MONTH(A2:A143)=I53);"+"&D2:D143;""));2;999)

Code: Select all

Function ConcatInColumn$(Text)
	If IsArray(Text) Then
		Dim i&, out$
		For i = 1 To UBound(Text)  'Text(1 To Count, 1 To 1)
			out = out & Text(i, 1)
		Next
	Else
		out = Text
	End If
	ConcatInColumn = out
End Function
Attachments
data (2).ods
(35.26 KiB) Downloaded 240 times
Ubuntu 18.04 LTS • LibreOffice 7.5.3.2 Community
gokulwadghule
Posts: 9
Joined: Wed Sep 08, 2021 10:40 am

Re: Sort Data monthwise

Post by gokulwadghule »

JohnSUN-Pensioner wrote:I am often reproached for using macros when it is enough to think a little and use ready-made built-in tools. What can I do, I'm lazy, I don't always want to think, so I take some old macro and slightly rewrite it for a new task
Thank you for the reply.

This is working, however, if you could tell how do I change the data range. i.e =GROUP_BY_MONTH(A2:A143;D2:D143) here only till "A2:A143" & ;"D2:D143" row, how do I change to "A2:A10000" & "D2:D10000" ? so that it will include my entire data.
Windows 10 & Open Office Version 3.1
eeigor
Posts: 214
Joined: Sun Apr 12, 2020 10:56 pm

Re: Sort Data monthwise

Post by eeigor »

Resize the range manually as you need.
It is a pity that you did not accept the method I proposed and did not want to switch to LibreOffice. No macros.
Ubuntu 18.04 LTS • LibreOffice 7.5.3.2 Community
User avatar
JohnSUN-Pensioner
Volunteer
Posts: 876
Joined: Fri Jan 14, 2011 1:21 pm
Location: Kyiv, Ukraine

Re: Sort Data monthwise

Post by JohnSUN-Pensioner »

Are you asking how to change the array formula? Select any cell and press CTRL+/. This will highlight all the cells with the formula. After that press F2, change the ranges and finish editing with the keyboard shortcut Ctrl + Shift + Enter.
I may not have a lot to give but what I got I'll give to you...
Apache OpenOffice 4.1.5, LibreOffice 6.4.4.2 (x64) on Windows 7
If you think that I did not answer your question, make allowances for my imperfect English
User avatar
MrProgrammer
Moderator
Posts: 4895
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Sort Data monthwise

Post by MrProgrammer »

gokulwadghule wrote:I just need to concatenate all the values present in "Cr" column depending on the month. Like, for all the dates available from Jan month, I need to concatenate "Cr" column values.
An IF function and conditional formatting (to hide cell content) can do this. No array formula. No macro. No MULTIPLE.OPERATIONS. Nothing complicated. Uses 3 functions: IF. MONTHS, and TEXT. The same solution works with all versions of OpenOffice and LibreOffice for the last 15 years. The data should be sorted in ascending order by date,
202110021015.ods
One sheet with summary at the end of the month
(31.37 KiB) Downloaded 167 times
 Edit: New attachment below with the summary of months in a separate sheet 
In that sheet, column D, DATE(An;Bn+1;0) is the last day of month Bn in year An. MATCH finds the rows where the months end so the sheet can copy the summary data.
202110021411.ods
Two sheets: original and separate sheet with the summary
(31.58 KiB) Downloaded 177 times
[Tutorial] Ten concepts that every Calc user should know
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel.   The locale for any menus or Calc formulas in my posts is English (USA).
eeigor
Posts: 214
Joined: Sun Apr 12, 2020 10:56 pm

Re: Summarize data monthwise

Post by eeigor »

@MrProgrammer, well, that's a good example. I even had to deal with it, because I hadn't used the MONTHS formula before. At first I was confused by the cells hidden by the conditional formatting. All ingenious is simple.

Edit:
If the values for days are out of bounds (day:=0), they are carried over to the next digit (month:=month - 1). It wasn't obvious.
A2: =2021
B2: = 1
D2: =DATE(A2;B2+1;0) returns 31.01.2021
Last edited by eeigor on Sun Oct 03, 2021 11:34 am, edited 2 times in total.
Ubuntu 18.04 LTS • LibreOffice 7.5.3.2 Community
gokulwadghule
Posts: 9
Joined: Wed Sep 08, 2021 10:40 am

Re: Sort Data monthwise

Post by gokulwadghule »

JohnSUN-Pensioner wrote:Are you asking how to change the array formula? Select any cell and press CTRL+/. This will highlight all the cells with the formula. After that press F2, change the ranges and finish editing with the keyboard shortcut Ctrl + Shift + Enter.

Perfect !! Thank you so much ..
Windows 10 & Open Office Version 3.1
Post Reply