[Solved] Autonumbering based off criteria in 3 other columns

Discuss the spreadsheet application

[Solved] Autonumbering based off criteria in 3 other columns

Postby nasch » Fri Mar 15, 2019 8:24 pm

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   Expand viewCollapse view
=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   Expand viewCollapse view
=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   Expand viewCollapse view
=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?
Last edited by nasch on Thu Apr 04, 2019 5:22 pm, edited 3 times in total.
OpenOffice 4.1.5 // Windows 10 Enterprise
or
OpenOffice 3.4.0 // Windows 7 Pro
nasch
 
Posts: 7
Joined: Tue Mar 12, 2019 2:39 pm

Re: Autonumbering based off criteria in 3 other columns

Postby RusselB » Fri Mar 15, 2019 8:45 pm

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.
OpenOffice 4.1.7 and LibreOffice 6.0.6.2 on Windows 7 Pro & Ultimate
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
User avatar
RusselB
Moderator
 
Posts: 5488
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Autonumbering based off criteria in 3 other columns

Postby MrProgrammer » Fri Mar 15, 2019 10:49 pm

nasch wrote: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.

nasch wrote: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.

nasch wrote:In this case I would expect the first IF statement …
Calc has no IF statements; it has IF functions.

nasch wrote:… 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.

nasch wrote:=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.

nasch wrote:=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.
Mr. Programmer
AOO 4.1.5 Build 9789 on MacOS 10.11.6.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Moderator
 
Posts: 3839
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Autonumbering based off criteria in 3 other columns

Postby keme » Sat Mar 16, 2019 12:00 pm

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?
User avatar
keme
Volunteer
 
Posts: 3256
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: Autonumbering based off criteria in 3 other columns

Postby Lupp » Sat Mar 16, 2019 3:24 pm

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.
On Windows 10: LibreOffice 6.2 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2528
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Autonumbering based off criteria in 3 other columns

Postby nasch » Tue Mar 19, 2019 4:37 pm

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

RusselB wrote: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.

MrProgrammer wrote:
nasch wrote: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.

MrProgrammer wrote:
nasch wrote: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.

MrProgrammer wrote:
nasch wrote:… 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.

MrProgrammer wrote:
nasch wrote:=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
nasch wrote:=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.

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.
OpenOffice 4.1.5 // Windows 10 Enterprise
or
OpenOffice 3.4.0 // Windows 7 Pro
nasch
 
Posts: 7
Joined: Tue Mar 12, 2019 2:39 pm

Re: Autonumbering based off criteria in 3 other columns

Postby MrProgrammer » Tue Mar 19, 2019 5:21 pm

nasch wrote:Apologies for my slow reply... been fighting a cold.
As have I.

nasch wrote: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.

nasch wrote: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.
Mr. Programmer
AOO 4.1.5 Build 9789 on MacOS 10.11.6.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Moderator
 
Posts: 3839
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Autonumbering based off criteria in 3 other columns

Postby nasch » Tue Mar 19, 2019 11:04 pm

MrProgrammer wrote: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.

nasch wrote: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.
OpenOffice 4.1.5 // Windows 10 Enterprise
or
OpenOffice 3.4.0 // Windows 7 Pro
nasch
 
Posts: 7
Joined: Tue Mar 12, 2019 2:39 pm


Return to Calc

Who is online

Users browsing this forum: No registered users and 36 guests