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.
How can I achieve this in libreoffice?
Thanks
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.
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.)
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)
This is what @Lupp calls “implicitly generated compound keys” at How do you match multiple columns in Calc?. 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.
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).
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: 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.