Ask Your Question
2

How to create a formula based on first occurrence of two columns values?

asked 2018-05-17 18:21:41 +0200

rkstbd gravatar image

In the following example I want to create a formula for TAX column. TAX will be 9 if Product Code appears first time on that date, TAX will be 0 for subsequent occurrence of the same product code on same date.

image description

How can I achieve this in libreoffice?

Thanks

edit retag flag offensive close merge delete

Comments

1

Good example. Next time to make it even better, instead of an image, enter the data directly in the question indented by 4 spaces so that it's easier to read and reproduce.

Jim K gravatar imageJim K ( 2018-05-17 19:46:40 +0200 )edit
1

But: Setting the horizomtal alignment different from default (no alignment selected), in this case 'Center', hides the information about the content type.
With default alignment we would see texts left, numbers right. Provides disambiguation.
(Many questions here concerning problems with texts lookling like dates, and thus assumed to be formatted numbers as the standard representation of dates in spreasheets is.)

Lupp gravatar imageLupp ( 2018-05-17 21:09:25 +0200 )edit

1 Answer

Sort by » oldest newest most voted
0

answered 2018-05-17 19:30:11 +0200

Jim K gravatar image

updated 2018-05-17 19:41:04 +0200

Enter the following formula in cell E2 and fill down to E5.

=IF(MATCH(A2&B2;A$1:A$5&B$1:B$5;0)=ROW();9;0)

Documentation: MATCH, ROW.

This is what @Lupp calls "implicitly generated compound keys" at https://ask.libreoffice.org/en/questi.... If I understand correctly, he considers them implicit because there is not a column that contains the compound keys. Rather, the keys are generated on the fly.

edit flag offensive delete link more

Comments

Just anotherone of my irritating comments:
Creating compounds on the fly by concatening the contents of two data fields may cause errors in rare cases (not likely in the specific one discussed here, combining a date with text): "AA" & "ABBB" deliver the same result as "AAA" & "BBB". This little problem was the reason for me to take care for a disambiguating delimiter in the eample attached to my above linked answer elsewhere, Nowadays we can even use CHAR(0).

Lupp gravatar imageLupp ( 2018-05-17 21:03:24 +0200 )edit

CHAR(0) may or may not work. i.e. it can not be stored in a final result (display text) in most if not all file formats. It may even not work in every spreadsheet function.

erAck gravatar imageerAck ( 2018-05-18 16:09:09 +0200 )edit

@ erAck: Thanks for the hint.
However: The string results I was talking of were created by peristing formulae for comparison purposes. In this context the fact tnat no char(0) will survive as a character contained in a string constant can be seen as an advantage: The ambiguity I mentioned above cannot occur.

Lupp gravatar imageLupp ( 2018-05-18 17:40:44 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2018-05-17 18:21:41 +0200

Seen: 20 times

Last updated: May 17