CALC: Two Columns -- which names were deleted, which were added from first column to make second column?

[MikeColley]

Hi All:
Surely this has been asked many times, I’m looking for a pointer to the answer and I’m not sure how to google this question.

SETUP: Yesterday’s top stocks in CALC column 1. Today’s top stocks in columm 2. QUESTION: How can libre office CALC tell me which ticker symbols in column 1 were deleted and which new ticker symbols were added to make column 2? (??? maybe use VLOCATE???)

Yes, I know I’m a bad boy for not upgrading, I’m still at 6.2.7.1 in linux mint 17. Install was a LO install, not from mint repositories.

Thanks all for your help. - Mike

----------- June 3 @ 15:41

Hi JohnSUN:

Thanks for the very nice welome, I’m glad to be here. You wanted an example file. I would like CALC to automatically fill in the columns Deleted and Added. The order doesn’t matter. Here is the file:

abc.ods

[JohnSUN]

Hello Mike and welcome! The version doesn’t matter - all the functions that will make this work have been in Calc from the start. It can be MATCH() or VLOOKUP() and even COUNTIF(). It’s a pity that I cannot, according to your description, present your table. If you go back to editing your question, you can insert a sample file with Ctrl+F

[MikeColley]

Hi JohnSUN: Thanks! Please see attached file abc.ods. - Mike (The file is good enough but there are errors in my typing).

[JohnSUN]

One easy solution:
in cell C3 the formula

=IF(COUNTIF($B$3:$B$50;A3);"X";"")

in cell D3 the formula

=IF(COUNTIF($A$3:$A$50;B3);"+";"")

and stretch them down to the end of the table. It will turn out something like this

How it works? The COUNTIF() function tries to find the value of the second parameter in the range specified by the first parameter and simply counts how many were found. The IF() function compares the quantity with zero - if not zero (the value was found in the range at least once), then it outputs some sign, otherwise - an empty string.
As I said, you can use MATCH() instead of COUNTIF(), but the formula in this case will be a little more complicated:

=IF(ISERROR(MATCH(A3;$B$3:$B$50;0));"";"X")

It was a very simple solution. It can be improved.
Select cell A3, choose Format - Conditional - Condition from the menu. Select [Formula in] Condition and enter the formula COUNTIF($B$3:$B$50;A3) - there is no need for IF() and other parts of our first formula. Create a new style with a bright background or choose one of the predefined

Now paint over the rest of the cells in table with this brush

The result will be something like this

Now the condition is set in such a way that the red color in the first column indicates the values that have passed the next day, and the deleted ones are left white. The same applies to the second column - white indicates new values that were not there yesterday, and green - those that passed on that day without changes.

When you understand the principle and master these techniques, you can color your tables as you please.

[MikeColley]

Hi JohnSUN
Wow!!! I’m impressed!!! SOLVED
(I hope I spell right, my keyboard is being difficult to type.)

[SOLUTION:]

I changed =IF(COUNTIF($B$3:$B$50;A3);"X";"") --to-- =IF(COUNTIF($B$3:$B$50;A3);"";"X")

and

I changed =IF(COUNTIF($A$3:$A$50;B3);"+";"") --to-- =IF(COUNTIF($A$3:$A$50;B3);"";"+") [\SOLUTION:]

I like the colors and I am going to put some effort into figuring out that second solution, I use conditional color a lot but this is a new area for me to figure out.

Great Answers!! Thanks JohnSUN - Mike

[JohnSUN]

Bravo Mike! Congratulations! (Hint: a simple NOT() function will help you in the conditional formatting formula)

[JohnSUN]

By the way, your task can have many, many different solutions. For example, the formula ="Deleted: " & TEXTJOIN(", ";1;IF(COUNTIF($B$3:$B$50;A3:A50);"";A3:A50)) & CHAR(10) & "Added: " & TEXTJOIN(", ";1;IF(COUNTIF($A$3:$A$50;B3:B50);"";B3:B50)) (this is an array formula, you must end the input with Ctrl+Shift+Enter instead of the usual Enter) will give the result

Deleted: WIL, TNA

Added: GUSH, WL, PXI, BNKU

[MikeColley]

Darn English language – I’m trying to figure where to put the Ctrl+Shift+Enter … Did you mean to put the Ctrl+Shift+Enter in the cell with or the next cell after …;B3:B50)) --or-- did you mean to put the Ctrl+Shift+Enter in the cell with or in the next cell after BNKU ? I can see this is a one cell solution only. This is an excellent solution also.

[JohnSUN]

I meant “enter the text of this long formula (just copy here and paste there) into any cell on the data sheet, press Ctrl and hold down this key, press Shift and while holding these two buttons press Enter”.

[MikeColley]

Perfect! Different Question: I didn’t ask this at first because I didn’t think of the application at that time: Do you have a formula that counts the issues in column A that also appear in column B. I am looking for the number 24 that is a NUMBER of common items that are in the intersection of column A and column B?

[JohnSUN]

Not sure if this is what you are asking for, but result 24 will be returned by =SUMPRODUCT(COUNTIF(A3:A50;B3:B50))

[MikeColley]

Yes JohnSUN !!! that intersection count (24) is what I was looking for.

I will have to troubleshoot the Ctrl-Shift-Enter suggestion you made above, my output is only “Added: GUSH, WL, PXI, BNKU” and missing is the “Deleted: WIL, TNA” part. Might be a LO version issue or a Win/linux issue. At first glance I don’t see anything wrong with the formula, interesting curly braces show around the entire formula and they disappear when I try to edit the formula. Maybe a different CHAR(10) number is needed for linux? ----3-Hours-Later---- I opened up the spreadsheet and it worked fine, the formula worked fine but the original problem was that the row that contained Deleted: WIL, TNA Added: GUSH, WL, PXI, BNKU stayed at the same height and didn’t expand to two lines of height, only the second line of text was visible because only one line of text could be seen at the regular row height. The formula worked correctly all along.

Thanks JohnSUN