[Solved] Counting merged cells in Calc

[holmes]

I am using a simple time tracker for tasks in calc (openoffice 3.4). The task names are entered from a list, and the cells are merged for continuous durations. In order to calculate the total time per task, I need to know a count of rows spanned by each task. A simple COUNTIF() would only return the number of occurrences of the task string, whereas I am looking for a metric that counts the number of cells in the merged ones. (Attachment, with simple example)

I searched a lot, but cannot figure out how to implement this. I know it is not advised to use merged cells for calculations etc. but having to enter text for every entry makes it more tedious. Hence the merged cells.

Thank you for any input!

[Villeroy]

Spreadsheets do not work like this.

You must not organize your data entry in cross tables. You may do so on paper sheets. With a computer you give up all advantages of your software tools. Simple flat tables, so called "normalized tables", are much easier to maintain and there is plenty of software to analyze normalized tables. Calc provides dozends of formulas, wizards and dialogs to deal with normalized data.

Never overload any formatting attribute with meaning. Particularly you must not merge any cells. Simply abstain completely from this "feature".

Call me arrogant, but this is the one and only way how to keep data in a spreadsheet: http://www.mediafire.com/file/9qzzfqgfp ... _Pilot.ods
Insert rows, delete rows, edit rows, select repetetive data from other lists, filter the list, sort the list, get aggregated results in pivot tables.

[holmes]

Villeroy, Thank you for the reply!

Yes, I had read about avoiding merging etc. which is why I put that in the question itself. Imagine, you have lots of 'Category' columns in the sheet that you shared. And also assume several category names are the same on adjacent days. - Just like several day columns in my sheet, and the same task done over several hours. The whole sheet becomes quite unreadable in my experience. Whereas if I group them (like as in merge and have only one string for the whole group), it is easy to go through.

The merging which I want to do is not just for aesthetic reasons, but it is tedious otherwise. If I have the same task performed for continuous hours, for the whole day - will end up repetitively entering the same thing over and again if I don't do merging.

I perfectly understand what you've meant in your post. But I think it is a tradeoff between simpler operations and the usability.

I know this should be possible with macros (perhaps XMergeableCell, getRowSpan(), isMerged() etc. - never tried it myself). But was looking for a simpler solution.

[jrkrideau]

I am using a simple time tracker for tasks in calc (openoffice 3.4). The task names are entered from a list, and the cells are merged for continuous durations. In order to calculate the total time per task, I need to know a count of rows spanned by each task. A simple COUNTIF() would only return the number of occurrences of the task string, whereas I am looking for a metric that counts the number of cells in the merged ones. (Attachment, with simple example)

I searched a lot, but cannot figure out how to implement this. I know it is not advised to use merged cells for calculations etc. but having to enter text for every entry makes it more tedious. Hence the merged cells.

Thank you for any input!

It sounds to me as if you are using the spreadsheet as a gantt chart. I've done so myself and it is a terriblly awkward way to do things. I'd recomment looking into some project management software. IIRC http://www.ganttproject.biz/ is not to difficult to use and might save you a lot of time and effort.

I don't use Base so does anyone know if it does Gantt Charts?

@ Villeroy
Well yes you may be arrogant but you are completely correct here.

[holmes]

jrkrideau, Thanks. I will look into some task management s/w.
The one reason I havn't had done that is they won't be very flexible like a spreadsheet.

[MrProgrammer]

I am looking for a metric that counts the number of cells in the merged ones

This is not possible with built-in Calc functions. Read section 9. Using functions and cell ranges in Ten concepts that every Calc user should know where it says "Calc's functions and formulas operate on cell values, so do not expect to perform calculations based on cell formats …". Cell merging is formatting, and although it can be attractive, cell merging often makes spreadsheets difficult to use.

The merging which I want to do is not just for aesthetic reasons, but it is tedious otherwise. If I have the same task performed for continuous hours, for the whole day - will end up repetitively entering the same thing over and again if I don't do merging.

It's at least as tedious to merge the cells as it is to copy the first task to the remaining cells since all you have to do is Ctrl+drag the Fill handle of the first cell (⌘Drag on a Mac). I entered A2 then dragged that to A3:A7, entered A8, dragged it to A9:A11, entered A12, and dragged to A13:A17.

If you don't want to see the repeated entries, use Format > Conditional Formatting to suppress their display in the following cells (number Format Code ;;;). Or you could put a ditto mark in their place. In the example, I used Format > Style Catalog to create a Ditto style where the number Format Code is General;General;General;\" and then used conditional format formula "cell = previous cell" to apply that style. If you don't understand the "cell = previous cell" concept read about relative references in section 8. Using formulas and cell references.

You must not organize your data entry in cross tables.

Yes. It's better to enter data in normal form and then use Data Pilot to build cross tables, expecially since Calc provides no simple way to do the reverse (Cross table → Normal Form) and because it can be difficult to use functions to analyze data in a cross table.

[holmes]

Thanks MrProgrammer.

It's at least as tedious to merge the cells as it is to copy the first task to the remaining cells

Well, not exactly. I have keyboard shortcuts to merge and split, so isn't that of a problem. But ctrl+drag option is okay as well. What I wanted was a calendar-like (where we drag and create an entry) intuitive interface, at the same time do calc operations which I wish to.

Yes, agree with rest of what you said. I will experiment with the conditional formatting options.

[JohnSUN-Pensioner]

In your example tasks are not defined for lines 7, 12 and 15. What does this mean? Maybe it makes sense to fill it special value "No tasks"?

[jrkrideau]

Thanks MrProgrammer.

It's at least as tedious to merge the cells as it is to copy the first task to the remaining cells

Well, not exactly. I have keyboard shortcuts to merge and split, so isn't that of a problem. But ctrl+drag option is okay as well. What I wanted was a calendar-like (where we drag and create an entry) intuitive interface, at the same time do calc operations which I wish to.

Yes, agree with rest of what you said. I will experiment with the conditional formatting options.

It looks to me like you could do something like the colour coding in the attached file. As long as you have standard time units you just put the task numbers in each cell and format. This gives you the desired visual layout and you can get times by using a set of countif() functions.

I am sure that the layout and formatting is very sub-optimal but it gives an idea of what I am talking about.

[holmes]

@JohnSUN-Pensioner. Wow! That was clever. Never thought about that.. Thanks a bunch!
Those empty cells in my sheet were idle times. I see that your logic requires every cell to be filled in. Still okay with me. Quite nice!

@jrkrideau, The color coding is nice too. Problem is that I have plenty of tasks (15-20) and more get added and removed. Having to color format from a palette is a bit of work. On the other hand, I already have cell coloring as a conditional format which works very well with merged cells.

Thanks a lot to all the people! I will mark this as solved.