[Solved] Summarize data monthwise
-
- Posts: 9
- Joined: Wed Sep 08, 2021 10:40 am
[Solved] Summarize data monthwise
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
Re: Sort Data monthwise
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?
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.
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.
Re: Sort Data monthwise
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.
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
-
- Posts: 9
- Joined: Wed Sep 08, 2021 10:40 am
Re: Sort Data monthwise
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.
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
Re: Sort Data monthwise
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
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
Libreoffice 7.4 on Debian 12 (Bookworm) (on RaspberryPI4)
Libreoffice 7.6 flatpak on Debian 12 (Bookworm) (on RaspberryPI4)
-
- Posts: 9
- Joined: Wed Sep 08, 2021 10:40 am
Re: Sort Data monthwise
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
Re: Sort Data monthwise
See also: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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Sort Data monthwise
To sum or concatenate? It's damn hard to guess... @Villeroy added.gokulwadghule wrote:I want to concatenate entries
2000+5446412+4000+5446412+25000+12121+112221+2000+5446412+5000+15121+
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
Re: Sort Data monthwise
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
-
- Posts: 9
- Joined: Wed Sep 08, 2021 10:40 am
Re: Sort Data monthwise
Thanks for your response...eeigor wrote:To sum or concatenate? It's damn hard to guess... @Villeroy added.gokulwadghule wrote:I want to concatenate entries
2000+5446412+4000+5446412+25000+12121+112221+2000+5446412+5000+15121+
The pivot table needs to be updated. Consider using 'Multiple operations', auto updated. The sort order doesn't matter.
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
- JohnSUN-Pensioner
- Volunteer
- Posts: 876
- Joined: Fri Jan 14, 2011 1:21 pm
- Location: Kyiv, Ukraine
Re: Sort Data monthwise
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
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
Re: Sort Data monthwise
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.
{=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
-
- 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
- JohnSUN-Pensioner
- Volunteer
- Posts: 876
- Joined: Fri Jan 14, 2011 1:21 pm
- Location: Kyiv, Ukraine
Re: Sort Data monthwise
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.eeigor wrote: {=TEXTJOIN("+";1;IF((YEAR(A2:A143)=$H$21)*(MONTH(A2:A143)=I21);D2:D143);"")}
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
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
Re: Sort Data monthwise
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
Re: Sort Data monthwise
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)}
{=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
- JohnSUN-Pensioner
- Volunteer
- Posts: 876
- Joined: Fri Jan 14, 2011 1:21 pm
- Location: Kyiv, Ukraine
Re: Sort Data monthwise
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())
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
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
Re: Sort Data monthwise
It's time to start upgrading the software. But the questioner also asked for a macro. Therefore, the issue is resolved.
NOTE:
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.
NOTE:
All this end of the formulaeeigor 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)}
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
- JohnSUN-Pensioner
- Volunteer
- Posts: 876
- Joined: Fri Jan 14, 2011 1:21 pm
- Location: Kyiv, Ukraine
Re: Sort Data monthwise
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 )eeigor wrote:It's time to start upgrading the software.
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
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
Re: Sort Data monthwise
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
- JohnSUN-Pensioner
- Volunteer
- Posts: 876
- Joined: Fri Jan 14, 2011 1:21 pm
- Location: Kyiv, Ukraine
Re: Sort Data monthwise
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
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
Re: Sort Data monthwise
=MID(CONCATINCOLUMN(IF((YEAR(A2:A143)=$H$53)*(MONTH(A2:A143)=I53);"+"&D2:D143;""));2;999)eeigor wrote:A combined method is also possible: multiple operations plus the simplest implementation of the CONCAT function.
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
-
- Posts: 9
- Joined: Wed Sep 08, 2021 10:40 am
Re: Sort Data monthwise
Thank you for the reply.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
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
Re: Sort Data monthwise
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.
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
- JohnSUN-Pensioner
- Volunteer
- Posts: 876
- Joined: Fri Jan 14, 2011 1:21 pm
- Location: Kyiv, Ukraine
Re: Sort Data monthwise
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
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
- MrProgrammer
- Moderator
- Posts: 4895
- Joined: Fri Jun 04, 2010 7:57 pm
- Location: Wisconsin, USA
Re: Sort Data monthwise
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,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.
Edit: New attachment below with the summary of months in a separate sheet |
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).
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).
Re: Summarize data monthwise
@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
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
-
- Posts: 9
- Joined: Wed Sep 08, 2021 10:40 am
Re: Sort Data monthwise
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