Comparison - Not Contained

[Doug_Loud]

Comparison - Not Contained

I have a table with two columns of data. Column A could contain thousands of records, and Column B contains only a few possibly new records. I wish to identify which values in Column B do not already exist in Column A, and display these in Column C. A: Apple, Orange, Pear, Grapes B: Apple, Orange, Watermelon The formulas talk about True and False but don’t seem to easily lists what is or isn’t missing.

[Jasmine_B]

Hey @Doug_Loud! If you have a formula working that can show whether a list is true or false, then try adding .[Name of column that contains the fruits] to the end of your formula next.
Similar questions have come up in the community a handful of times, and there are some good discussions out there! Here is one that comes to mind that is a similar use-case:

Find duplicates across several columns and show only first type

Hi all, Someone could probably save me half a day with the right answer to this. My goal is to make it as easy as possible for people to review a list of changes by only showing the pertinent rows. I want to find all the rows that are total duplicates (in respect to two of the columns, but not the rest of the columns) and hide all but the first “level” of the duplicates. So if there is a row “Apples” with the description “Do this” at “Level 1” and “verson 1” and another row “Apples” with the …

[Doug_Loud]

Thanks! This turns out to be a very interesting question: compare two lists and find the missing items. So far, not so easy to do. One suggestion is to use the OpenAI pack, but I haven’t had time to check that out yet. Sounds like it should be easy, but it’s not. Apples, Oranges, Lemons, Watermelons vs. Apples, Oranges, Lemons, Grapes, Persimmons. Finding that watermelons is missing from the second list is not so easy so far.

[Billy_Jackson]

Hey Doug, I thiiiiink the below should work. You might need to split/foreach the column B depending on its format. But give that a try and let me know

thisrow.columnB.filter(Not(currentvalue.in(columnA)))

[Doug_Loud]

Hmmm – will do!

Douglass N. Loud, Esq.

President

Integrated Information Systems, Inc.

305 East 40^th Street, Suite 12E

New York, NY 10016

203-952-7108 cell

212-599-1791

dougl7573@gmail.com

[Doug_Loud]

Thanks Billy_Jackson– that formula works great as long as the items are in the same rows. But if they are not because one is missing in the middle, it appears missing only because it’s not in a matching row. I’m not sure it’s possible to cycle through the A list to see if a B list member is not there at all:

ColumnA ColumnB ColumnC

Apple Apple

Orange Orange

Lemon Watermelon Watermelon

Watermelon

It thinks Watermelon in missing because it doesn’t match Lemon and doesn’t show Lemon missing. Do you see what I mean?

Thanks for your help.

[Billy_Jackson]

Hmm. I think I misunderstood how you were structuring your data.
The original formula was made for each column to contain a multi-select list, or similar.
For instance:
Row 1, Column A: Apple, Orange, Lemon, Watermelon
Row 1, Column B: Apple, Orange, Lemon

It would then compare these two and show you what’s missing.

If you were looking to compare the whole length of the column, the formula would just be:

columnB.filter(Not(currentvalue.in(columnA)))

May I ask what kind of underlying data this is (in an abstract sense) or are you actually comparing long lists of fruit?

[Doug_Loud]

Thanks – I’ll give it a try…

To answer your question: I am comparing lists of documents or names. I have to maintain a list of the documents in inventory. When they send a new list, I have to check to see if there are any new documents being sent or confirm that I already have them in inventory. They could be sending me 20 new documents to add to an inventory of 250. The list of new documents is created by another formula (yours with more editing) that reads the latest text and looks for the citations and makes a list of them. I have been doing this manually. I am trying to set up a more automated version, so the next time I won’t go crazy keeping track of the documents.

I did it with macros in Excel and Word, but Coda would allow me to link the documents in the text to their descriptions in the inventory. And the links would still work on iPhones and iPads when I send the main link to Coda out to the users. Linking worked great on Macs and Windows, but crashed when the document was transferred, because, of course, the documents weren’t located at their links anymore.

Here’s a real example:

This one seems better, but it still marks as missing an item that’s in a different position (BATES34567):

thisRow.NewA.filter(Not(currentvalue.in(thisRow.OldB)))

ABTable

NewA

OldB

Billy2

BATES12345

BATES12345

BATES23456

BATES34567

BATES23456

BATES34567

BATES45678

BATES34567

BATES67890

BATES67890

Orange

Orange

You have been huge help. Thanks very much.

[Billy_Jackson]

@Doug_Loud

To answer your question: I am comparing lists of documents or names. I have to maintain a list of the documents in inventory. When they send a new list, I have to check to see if there are any new documents being sent or confirm that I already have them in inventory. They could be sending me 20 new documents to add to an inventory of 250. The list of new documents is created by another formula (yours with more editing) that reads the latest text and looks for the citations and makes a list of them. I have been doing this manually. I am trying to set up a more automated version, so the next time I won’t go crazy keeping track of the documents.

Hmm. Are you able to share the document with me privately? I am reaching the edge of my ability to visualize in the abstract. Maybe you could create a copy, delete the underlying documents and just leave the names?

I thiiiiink I can suggest a much simpler workflow for you, but I’m having trouble being sure.

happy to help! This sounds like a mindnumbing task and I have a personal grudge against mindnumbing tasks everywhere

[Doug_Loud]

Thanks for your patience…here’s a more typical example, where we have identified a list of docs coming in (NewA), some of which are already listed: The GREEN entries in Billy2 are correct and not already in the list, but the RED entries are just out of sync in the list but do already exist in OldB

ABTable

NewA

OldB

Billy2

BATES07235

BATES07235

BATES07237

BATES07237

BATES08127

BATES11175

BATES08127

BATES11175

BATES11282

BATES11175

BATES11282

BATES11285

BATES11282

BATES11285

BATES11473

BATES11285

BATES11286

BATES11508

BATES11286

BATES11287

BATES12015

BATES11287

BATES11473

BATES12016

BATES11473

BATES11508

BATES11508

BATES11755

BATES11755

BATES12015

BATES12015

BATES12016

BATES12016

BATES12017

BATES12017

BATES12345

BATES12345

BATES34567

BATES34567

BATES45678

BATES45678

[Doug_Loud]

Hmmm – think I’ve been thinking about this the wrong way. I stopped having the choices be between 2 columns in one table, and made it a choice between one column in one table and one column in another table. I left NewA in ABTable and put OldB in BCTable with this formula in Column2 of BCTable:

ABTable.NewA.filter(Not(currentvalue.in(BCTable.OldB)))

Interestingly, this results in a new row in BCTable for each OldB entry, and then lists all the left off entries from NewA in Column2 for each row. Huge table, but at least I now know what the missing entries are between NewA and OldB.

Thanks! - Doug

[Billy_Jackson]

Hi Doug,
Is there any way to share the actual Coda doc with me? The formatting isn’t getting carried through when posting on the forum.

Unless of course you’ve got it sorted!

[Doug_Loud]

Try this link to Billy Test:

https://coda.io/d/_dx7uHagjT-p/Billy-Test_suCMf

The second table BCTable, where the missing entries appear on each row for each entry, looked a regular table before all the missing files got put in for each row entry.

Thanks again!

• Doug

[Billy_Jackson]

Hi Doug, I took a look and think I understand.
Would you let me know if this model doc here successfully displays the behavior you’re looking for?

It has two tables:
One, Document Master List is meant to be your “Inventory” that has all the documents already in your system.
The Other, Document Intake, is meant to be where you paste in (or somehow generate rows into) all the new documents you need to cross-check against the master list.

if the docs in Intake ARE in the master list (in this case, if there’s something else with the same name, although you could compare them another way), the checkbox is checked. If they’re not, the checkbox isn’t checked and a button is enabled that allows you to copy that row into the master list.

Let me know if i’m on track or not!

[Doug_Loud]

WOW! Absolutely on track! I as just tryng to get the list before I went on to the button adding step.

Douglass N. Loud, Esq.

President

Integrated Information Systems, Inc.

305 East 40^th Street, Suite 12E

New York, NY 10016

203-952-7108 cell

212-599-1791

dougl7573@gmail.com

[Doug_Loud]

Sorry for the delay in getting back to you - I’ve been at a conference since Sunday. But I will check out your tables very soon. Thank you for your help!

[Doug_Loud]

Fabulous! That is exactly what I needed. Thank you so much for guiding me through the fog! Your solution has the added benefit of not only adding a new row for a new entry but also showing which entries are new, since there is further processing required for them as they enter the main inventory. Huge Help! Thanks!

[Billy_Jackson]

Glad to help Doug!
Hope it all works out swimmingly

[Doug_Loud]

Thanks very much – I hope I can help you with something sometime!

Douglass N. Loud, Esq.

President

Integrated Information Systems, Inc.

305 East 40^th Street, Suite 12E

New York, NY 10016

203-952-7108 cell

212-599-1791

dougl7573@gmail.com