Ask Your Question
1

Accounting Journal Problem [closed]

asked 2012-11-15 06:03:49 +0100

leviticus8908 gravatar image

updated 2012-11-15 06:07:41 +0100

I am trying to keep an accounting journal in LibreOffice Calc. The format was originally a .xls that was sent from my Accounting professor which I am now attempting to use for my personal use. Basically my goal is to be able to input my transactions in any order and then sort by date. This is so that I will not have to worry about sorting the physical receipts by date and can just let them build up and then dump them all the data into Calc and then sort it out. However, my problem is that each journal entry takes up three rows and I don't know how to sort it without having the journal entries broken up. I've searched around the internet for some solutions on Excel, and they all seem to come down to using the IF/THEN function to generate the appropriate identification for each row and then sorting it by the new row. Every time I try in Calc however, all I can generate is the same number for every row.

This makes more sense if you see it so I have attached an example. If you know of any easier way I could format it, please let me know.

Thanks in advance for your help.

P.S. Please note that Column A is not part of the journal but rather my attempt of coming up with a way to sort the entries.

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2015-11-02 04:59:44.428036

Comments

Perhaps I didn't make myself clear or I misunderstood your answer. Basically I just need to know how to sort data entries with multiple rows without breaking up the entry.

leviticus8908 gravatar imageleviticus8908 ( 2012-11-15 21:55:56 +0100 )edit

Also, apparently I can't upload a sample because I don't have enough "karma" yet.

leviticus8908 gravatar imageleviticus8908 ( 2012-11-15 21:56:45 +0100 )edit

How do I get more "karma?" I think I need to show an example of what I'm trying to do. I can't get the "=IF(THEN)" function to operate like I want it to. It seems that the formula is correct but it doesn't yield the correct value.

leviticus8908 gravatar imageleviticus8908 ( 2012-11-20 00:59:04 +0100 )edit

I can't figure out how to change the numbers I had to "date" formula without losing the right numbers. For example, I put in "0820" for August 20 and when I attempt to convert it it goes to "03-30" [?!]. Also, why would it make a difference what format the numbers are in for the purpose of sorting?

leviticus8908 gravatar imageleviticus8908 ( 2012-11-20 01:06:40 +0100 )edit

If you enter 0820 Calc counts 820 days since Dec 31, 1899 what gives you 03-30-1902. You have to enter it as 08-20 then it is converted to a date serial number.

horst gravatar imagehorst ( 2012-11-20 02:28:14 +0100 )edit

The three separate rows almost works but I need to tab after each line.

leviticus8908 gravatar imageleviticus8908 ( 2012-11-20 03:03:54 +0100 )edit

Sorry don't understand what you mean with "tab after each line"

horst gravatar imagehorst ( 2012-11-21 00:26:19 +0100 )edit

Sorry. I mean that row needs to have an indentation (similar to an outline).

leviticus8908 gravatar imageleviticus8908 ( 2012-11-25 15:30:13 +0100 )edit

4 Answers

Sort by » oldest newest most voted
0

answered 2013-06-12 12:19:23 +0100

Paijo gravatar image

Suggestion:

  1. Add one more column, and fill that column with series from 1 to N (equal to total rows used for journal). Give the title for that column "ORDER" or something else you like.
  2. Sort the table, and use first sort key "Date" and second sort key is that new column.

Based on my experience, it will preserve row order after you sort or making some mess with sorting. It can sort the table back as original before you sort by date, by value, etc (use the new column as sort key, and everything will revert back)

edit flag offensive delete link more
0

answered 2012-11-19 06:37:50 +0100

Suggestion: First column must be real date, as suggested already. In the second column number the lines for each journal entry. For example a 3 line journal entry with comment, will be numbered 1 to 4. Then sort first on the date column and secondly on the line number column.

edit flag offensive delete link more
0

answered 2012-11-20 02:34:02 +0100

horst gravatar image

Ok I didn't read right for my last answer. Sorry ;).

Do the new entries need to be in rows? If you can arrange in columns (what I saw mostly for journals) my answer would work. Or can you enter the 3 rows in one cell separated by Ctrl-Enter (soft CR)?

edit flag offensive delete link more
0

answered 2012-11-15 16:55:01 +0100

horst gravatar image

Sounds straight forward to me.

1) Your date column has to be a real date not text.

2) Now select ALL 3 columns and all the rows you want to sort.

3) Menu:Data->sort.. shows 3 sort options. The first is normally the leftmost column. If it is ok click Ok and done.

You might also check the help. Description might be better than mine ;)

edit flag offensive delete link more

Question Tools

Stats

Asked: 2012-11-15 06:03:49 +0100

Seen: 647 times

Last updated: Jun 12 '13