[Solved] Autonumbering based off criteria in 3 other columns

[nasch]

Hello again; apologies to ask another question but in continuing to work on updating a legacy form I am encountering another issue when translating excel to OpenOffice.

The entire worksheet working hinges on the ability of the main data table to apply numbering to entries automatically dependent on 3 other criteria. It is to first look at what kind of data-entry the row is, then for what department it is, and then what date it falls on to autonumber accordingly. The data is sorted by date.

In re-creating this for OpenOffice I have "simplified" what one would enter in this formula as instead of evaluating for 9+ "kinds", there are only 6,. this is possible because certain kinds can be grouped together for entry on the final form. (Please keep this in mind when looking at the input for Excel.)

In Excel, I was using:

Code: Select all

=IFERROR(IFS((OR(M2="Z",M2="J",M2="DR",M2="ES",M2="R")),COUNTIFS(D$2:D2,D2,L$2:L2,L2),(OR(M2="W",M2="HG",M2="CD",M2="D",M2="M")),COUNTIFS(D$2:D2,D2,B$2:B2,B2,L$2:L2,L2)),"")

I have begun to build this in OpenOffice with:

Code: Select all

=IF(AND($M2="A";$L2="A");COUNTIFS(D$2:D2;D2))

The above works correctly. It only effects rows where column M and L equal "A", and since the data is already sorted by date it numbers sequentially as I would hope. However this is not nearly the final product I need, as there are 3 options for column M and 6 options for column L. In effect I will be creating 18 different IF statements to make this work... though not my actual question, is there a better way to format this so that it is not as long?

To take a step further In OpenOffice, I have attempted:

Code: Select all

=IF(AND($M4="A";$L4="A");COUNTIFS(D$2:D4;D4;L$2:L4;L4);IF(AND($M4="A";$L4="B");COUNTIFS(D$2:D4;D4;L$2:L4;L4)))

In this case I would expect the first IF statement to be treated separately from the second, but somehow they are crossing paths, as the numbering completely flops upon the addition of this second "kind" and "department".

Is there a way to achieve this same auto-numbering in OpenOffice?

[RusselB]

I'm going to make a suggestion that may make things a lot easier... namely to switch to using LibreOffice rather than OpenOffice.
LibreOffice has a higher compatibility with Excel, and some functions, like the IFERROR, exist in LibreOffice, but don't in OpenOffice.
You may find that you need to re-write less of your code due to the additional compatibility.

[MrProgrammer]

Is there a way to achieve this same auto-numbering in OpenOffice?

Of course, but attach a document demonstrating the situation (remove confidential information then use Post Reply, not Quick Reply, and don't attach a picture instead of the document itself). If you want me to look at it, make sure it is ODS format; I won't open an XLSX document, though others might.

there are 3 options for column M and 6 options for column L. In effect I will be creating 18 different …

Explain in more detail what the 18 options are. It is frequently a good idea to use more than one cell to evaulate a complex expression. In this situation you might want 18 cells. Don't be concerned about using multiple cells instead of one — you won't run out of them. For this many conditions a VLOOKUP table may be helpful.

In this case I would expect the first IF statement …

Calc has no IF statements; it has IF functions.

… to be treated separately from the second, but somehow they are crossing paths, as the numbering completely flops upon the addition of this second "kind" and "department".

Provide an attachment to demonstrate the "flop" and explain what the desired result would be and how that differs from what you've achieved thus far.

=IF(AND($M2="A";$L2="A");COUNTIFS(D$2:D2;D2))

Your IF function is bogus because it does not have an otherwise clause. The function should specify the result when AND($M2="A";$L2="A") is false.

=IF(AND($M4="A";$L4="A");COUNTIFS(D$2:D4;D4;L$2:L4;L4);IF(AND($M4="A";$L4="B");COUNTIFS(D$2:D4;D4;L$2:L4;L4)))
… somehow they are crossing paths …

The two expressions in pink are identical so it doesn't matter if L4 is A or B. Perhaps this is not what you want. Your goal is not clear to me without an attachment.

[keme]

Based on your formulas I guess that you are numbering, for each date, each occurrence of a combination of "data entry kind" and "department". That would mean that the first repetition of a department/kind set for a given date will be number 2. So you may have 18 different number series, restarting on 1 for each date change. Is that correct?

[Lupp]

As far as I can see, the original "Excel" formula makes the sequential numbering depend on four criteria, namely the contents of the columns B, D, L, and M. I really would like to get assosciated the terms "kind of data-entry", "department" and "date" to these columns, and to get told what kind of field the forth column not yet addressed should be used for.

The probable ways to "translate" and/or to simplify/clarify the formula depend on whether AOO or LibO shall be used, and on whether the introduction of one or a few helper columns is accepted.

[nasch]

Apologies for my slow reply... been fighting a cold.

I'm going to make a suggestion that may make things a lot easier... namely to switch to using LibreOffice rather than OpenOffice.
LibreOffice has a higher compatibility with Excel, and some functions, like the IFERROR, exist in LibreOffice, but don't in OpenOffice.
You may find that you need to re-write less of your code due to the additional compatibility.

I appreciate your suggestion but I am limited to what is accessible on work devices,. namely OpenOffice. It is not possible to change at this time.

Is there a way to achieve this same auto-numbering in OpenOffice?

Of course, but attach a document demonstrating the situation (remove confidential information then use Post Reply, not Quick Reply, and don't attach a picture instead of the document itself). If you want me to look at it, make sure it is ODS format; I won't open an XLSX document, though others might.

Attached. Please disregard dollar/percentage figures as I've replaced these numbers.

there are 3 options for column M and 6 options for column L. In effect I will be creating 18 different …

Explain in more detail what the 18 options are. It is frequently a good idea to use more than one cell to evaulate a complex expression. In this situation you might want 18 cells. Don't be concerned about using multiple cells instead of one — you won't run out of them. For this many conditions a VLOOKUP table may be helpful.

Apologies as I said these backward.. There are 6 options for column M and 3 options for column L. Column M is the "LineItemGroup", it evaluates column N, which includes 12 potential options, and groups them into 6 options, as multiple can be grouped together. (This is visible in a rough table outline in sheet "Info" of my attachment.) Column L evaluates column B for what department the entry is for. Column B could contain up to 8 options presently, and Column L reduces these to 3, as again, some can be grouped together based off the "parent" department. In the future, if this works out, I may well add additional department options to this worksheet which will increase this number further.

… to be treated separately from the second, but somehow they are crossing paths, as the numbering completely flops upon the addition of this second "kind" and "department".

Provide an attachment to demonstrate the "flop" and explain what the desired result would be and how that differs from what you've achieved thus far.

I've put another sheet in the document, "Flop" which demonstrates the "flop" results. "No flop" would be the original sheet, which doesn't include the second parameter. The desired result would be to apply numbering as described in the next quoted section.

=IF(AND($M2="A";$L2="A");COUNTIFS(D$2:D2;D2))

Your IF function is bogus because it does not have an otherwise clause. The function should specify the result when AND($M2="A";$L2="A") is false.
Indeed, currently all of these functions either return a count or "FALSE" as I've only input one type of

=IF(AND($M4="A";$L4="A");COUNTIFS(D$2:D4;D4;L$2:L4;L4);IF(AND($M4="A";$L4="B");COUNTIFS(D$2:D4;D4;L$2:L4;L4)))
… somehow they are crossing paths …

The two expressions in pink are identical so it doesn't matter if L4 is A or B. Perhaps this is not what you want. Your goal is not clear to me without an attachment.

I wish to apply the count function separately from the first instance due to the different combination of "kind" and "department", as keme inferred below. The issue OpenOffice is treating these functions exactly as you describe, the same, and thus not beginning a separate numbering progression.

Based on your formulas I guess that you are numbering, for each date, each occurrence of a combination of "data entry kind" and "department". That would mean that the first repetition of a department/kind set for a given date will be number 2. So you may have 18 different number series, restarting on 1 for each date change. Is that correct?

This is precisely what I am trying to do.

[MrProgrammer]

Apologies for my slow reply... been fighting a cold.

As have I.

keme wrote:
Based on your formulas I guess that you are numbering, for each date, each occurrence of a combination of "data entry kind" and "department". That would mean that the first repetition of a department/kind set for a given date will be number 2. So you may have 18 different number series, restarting on 1 for each date change. Is that correct?

This is precisely what I am trying to do.

I will guess that you want =SUMPRODUCT(D$2:D2=D2;L$2:L2=L2;M$2:M2=M2) in K2, filled down to K20.

In the future, if this works out, I may well add additional department options to this worksheet which will increase this number further.

You have complex IF functions in columns L and M which could be simplified with [Tutorial] VLOOKUP questions and answers. The number of different combinations of L and M values will not change the formula in column K.

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

[nasch]

I will guess that you want =SUMPRODUCT(D$2:D2=D2;L$2:L2=L2;M$2:M2=M2) in K2, filled down to K20.

In the future, if this works out, I may well add additional department options to this worksheet which will increase this number further.

You have complex IF functions in columns L and M which could be simplified with [Tutorial] VLOOKUP questions and answers. The number of different combinations of L and M values will not change the formula in column K.

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

This did work very well.. However I now realize I blundered again in describing the data. (Initially in Excel I was identifying the data using the Department # in the "uniqueID".) Whereas "LineItemGroup"'s B thru E can be grouped together within the respective parent department, Group A cannot,.. as each numbered department has it's main invoice entries separate from each other... only the credits and similar items are listed regardless of sub-department. Is there a method to achieve this separate grouping procedure? I have attached a revised outline..

Scratch that,. I realized adding an additional parameter to SUMPRODUCT would do what I wanted sufficiently. Thank you for all the assistance.