# Comparison - Not Contained **URL:** \ **Category:** Ask the Community\ **Created:** [February 24, 2023, 2:16am UTC](https://connect.superhuman.com/t/comparison-not-contained/37892 "2023-02-24T02:16:10Z")\ **Posts on this page:** 20\ **Page:** 1
**Author:** ![Doug\_Loud](https://yyz1.discourse-cdn.com/coda1/user_avatar/connect.superhuman.com/doug_loud/32/28122_2.png) [@Doug\_Loud](https://connect.superhuman.com/u/Doug_Loud)\ **Post date:** [February 24, 2023, 2:16am UTC](https://connect.superhuman.com/t/comparison-not-contained/37892/1 "2023-02-24T02:16:10Z")
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. ---
**Author:** ![Jasmine\_B](https://yyz1.discourse-cdn.com/coda1/user_avatar/connect.superhuman.com/jasmine_b/32/26214_2.png) [@Jasmine\_B](https://connect.superhuman.com/u/Jasmine_B)\ **Post date:** [February 28, 2023, 9:15pm UTC](https://connect.superhuman.com/t/comparison-not-contained/37892/2 "2023-02-28T21:15:21Z")
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](https://connect.superhuman.com/t/find-duplicates-across-several-columns-and-show-only-first-type/25517): > > 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 … ---
**Author:** ![Doug\_Loud](https://yyz1.discourse-cdn.com/coda1/user_avatar/connect.superhuman.com/doug_loud/32/28122_2.png) [@Doug\_Loud](https://connect.superhuman.com/u/Doug_Loud)\ **Post date:** [March 1, 2023, 1:25am UTC](https://connect.superhuman.com/t/comparison-not-contained/37892/3 "2023-03-01T01:25:48Z")
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. ---
**Author:** ![Billy\_Jackson](https://yyz1.discourse-cdn.com/coda1/user_avatar/connect.superhuman.com/billy_jackson/32/31366_2.png) [@Billy\_Jackson](https://connect.superhuman.com/u/Billy_Jackson)\ **Post date:** [March 1, 2023, 2:10am UTC](https://connect.superhuman.com/t/comparison-not-contained/37892/4 "2023-03-01T02:10:18Z")
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)))` ---
**Author:** ![Doug\_Loud](https://yyz1.discourse-cdn.com/coda1/user_avatar/connect.superhuman.com/doug_loud/32/28122_2.png) [@Doug\_Loud](https://connect.superhuman.com/u/Doug_Loud)\ **Post date:** [March 1, 2023, 2:55am UTC](https://connect.superhuman.com/t/comparison-not-contained/37892/5 "2023-03-01T02:55:02Z")
Hmmm – will do! ![signature_4161339374](https://global.discourse-cdn.com/coda1/original/3X/8/a/8aa0c9bf86bc6404359bec5bbf778392ccd41d5e.png) Douglass N. Loud, Esq. President Integrated Information Systems, Inc. 305 East 40th Street, Suite 12E New York, NY 10016 203-952-7108 cell 212-599-1791 [dougl7573@gmail.com](mailto:dougl7573@gmail.com) ---
**Author:** ![Doug\_Loud](https://yyz1.discourse-cdn.com/coda1/user_avatar/connect.superhuman.com/doug_loud/32/28122_2.png) [@Doug\_Loud](https://connect.superhuman.com/u/Doug_Loud)\ **Post date:** [March 3, 2023, 1:54am UTC](https://connect.superhuman.com/t/comparison-not-contained/37892/6 "2023-03-03T01:54:44Z")
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. ---
**Author:** ![Billy\_Jackson](https://yyz1.discourse-cdn.com/coda1/user_avatar/connect.superhuman.com/billy_jackson/32/31366_2.png) [@Billy\_Jackson](https://connect.superhuman.com/u/Billy_Jackson)\ **Post date:** [March 3, 2023, 2:30am UTC](https://connect.superhuman.com/t/comparison-not-contained/37892/7 "2023-03-03T02:30:33Z")
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: ```auto 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? ---
**Author:** ![Doug\_Loud](https://yyz1.discourse-cdn.com/coda1/user_avatar/connect.superhuman.com/doug_loud/32/28122_2.png) [@Doug\_Loud](https://connect.superhuman.com/u/Doug_Loud)\ **Post date:** [March 3, 2023, 7:46pm UTC](https://connect.superhuman.com/t/comparison-not-contained/37892/8 "2023-03-03T19:46:13Z")
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. ---
**Author:** ![Billy\_Jackson](https://yyz1.discourse-cdn.com/coda1/user_avatar/connect.superhuman.com/billy_jackson/32/31366_2.png) [@Billy\_Jackson](https://connect.superhuman.com/u/Billy_Jackson)\ **Post date:** [March 3, 2023, 8:19pm UTC](https://connect.superhuman.com/t/comparison-not-contained/37892/9 "2023-03-03T20:19:29Z")
> [@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 ---
**Author:** ![Doug\_Loud](https://yyz1.discourse-cdn.com/coda1/user_avatar/connect.superhuman.com/doug_loud/32/28122_2.png) [@Doug\_Loud](https://connect.superhuman.com/u/Doug_Loud)\ **Post date:** [March 3, 2023, 9:35pm UTC](https://connect.superhuman.com/t/comparison-not-contained/37892/10 "2023-03-03T21:35:14Z")
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 ---
**Author:** ![Doug\_Loud](https://yyz1.discourse-cdn.com/coda1/user_avatar/connect.superhuman.com/doug_loud/32/28122_2.png) [@Doug\_Loud](https://connect.superhuman.com/u/Doug_Loud)\ **Post date:** [March 3, 2023, 10:25pm UTC](https://connect.superhuman.com/t/comparison-not-contained/37892/11 "2023-03-03T22:25:00Z")
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 ---
**Author:** ![Billy\_Jackson](https://yyz1.discourse-cdn.com/coda1/user_avatar/connect.superhuman.com/billy_jackson/32/31366_2.png) [@Billy\_Jackson](https://connect.superhuman.com/u/Billy_Jackson)\ **Post date:** [March 4, 2023, 9:13pm UTC](https://connect.superhuman.com/t/comparison-not-contained/37892/12 "2023-03-04T21:13:12Z")
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! ---
**Author:** ![Doug\_Loud](https://yyz1.discourse-cdn.com/coda1/user_avatar/connect.superhuman.com/doug_loud/32/28122_2.png) [@Doug\_Loud](https://connect.superhuman.com/u/Doug_Loud)\ **Post date:** [March 5, 2023, 3:15pm UTC](https://connect.superhuman.com/t/comparison-not-contained/37892/13 "2023-03-05T15:15:31Z")
Try this link to Billy Test: [https://coda.io/d/\_dx7uHagjT-p/Billy-Test\_suCMf](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 ---
**Author:** ![Billy\_Jackson](https://yyz1.discourse-cdn.com/coda1/user_avatar/connect.superhuman.com/billy_jackson/32/31366_2.png) [@Billy\_Jackson](https://connect.superhuman.com/u/Billy_Jackson)\ **Post date:** [March 5, 2023, 7:21pm UTC](https://connect.superhuman.com/t/comparison-not-contained/37892/14 "2023-03-05T19:21:21Z")
Hi Doug, I took a look and think I understand. Would you let me know if [this model doc here](https://coda.io/@billy-jackson/document-comparison) 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! ---
**Author:** ![Doug\_Loud](https://yyz1.discourse-cdn.com/coda1/user_avatar/connect.superhuman.com/doug_loud/32/28122_2.png) [@Doug\_Loud](https://connect.superhuman.com/u/Doug_Loud)\ **Post date:** [March 6, 2023, 3:54pm UTC](https://connect.superhuman.com/t/comparison-not-contained/37892/15 "2023-03-06T15:54:42Z")
WOW! Absolutely on track! I as just tryng to get the list before I went on to the button adding step. ![signature_166945449](https://global.discourse-cdn.com/coda1/original/3X/8/a/8aa0c9bf86bc6404359bec5bbf778392ccd41d5e.png) Douglass N. Loud, Esq. President Integrated Information Systems, Inc. 305 East 40th Street, Suite 12E New York, NY 10016 203-952-7108 cell 212-599-1791 [dougl7573@gmail.com](mailto:dougl7573@gmail.com) ---
**Author:** ![Doug\_Loud](https://yyz1.discourse-cdn.com/coda1/user_avatar/connect.superhuman.com/doug_loud/32/28122_2.png) [@Doug\_Loud](https://connect.superhuman.com/u/Doug_Loud)\ **Post date:** [March 10, 2023, 8:24pm UTC](https://connect.superhuman.com/t/comparison-not-contained/37892/16 "2023-03-10T20:24:40Z")
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! ---
**Author:** ![Doug\_Loud](https://yyz1.discourse-cdn.com/coda1/user_avatar/connect.superhuman.com/doug_loud/32/28122_2.png) [@Doug\_Loud](https://connect.superhuman.com/u/Doug_Loud)\ **Post date:** [March 14, 2023, 1:28am UTC](https://connect.superhuman.com/t/comparison-not-contained/37892/17 "2023-03-14T01:28:55Z")
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! ---
**Author:** ![Billy\_Jackson](https://yyz1.discourse-cdn.com/coda1/user_avatar/connect.superhuman.com/billy_jackson/32/31366_2.png) [@Billy\_Jackson](https://connect.superhuman.com/u/Billy_Jackson)\ **Post date:** [March 14, 2023, 5:08pm UTC](https://connect.superhuman.com/t/comparison-not-contained/37892/18 "2023-03-14T17:08:46Z")
Glad to help Doug! Hope it all works out swimmingly ---
**Author:** ![Doug\_Loud](https://yyz1.discourse-cdn.com/coda1/user_avatar/connect.superhuman.com/doug_loud/32/28122_2.png) [@Doug\_Loud](https://connect.superhuman.com/u/Doug_Loud)\ **Post date:** [March 14, 2023, 6:07pm UTC](https://connect.superhuman.com/t/comparison-not-contained/37892/19 "2023-03-14T18:07:00Z")
Thanks very much – I hope I can help you with something sometime! ![signature_1835949390](https://global.discourse-cdn.com/coda1/original/3X/8/a/8aa0c9bf86bc6404359bec5bbf778392ccd41d5e.png) Douglass N. Loud, Esq. President Integrated Information Systems, Inc. 305 East 40th Street, Suite 12E New York, NY 10016 203-952-7108 cell 212-599-1791 [dougl7573@gmail.com](mailto:dougl7573@gmail.com) ---
**Author:** ![system](https://yyz1.discourse-cdn.com/coda1/user_avatar/connect.superhuman.com/system/32/24484_2.png) [@system](https://connect.superhuman.com/u/system)\ **Post date:** [March 17, 2023, 6:07pm UTC](https://connect.superhuman.com/t/comparison-not-contained/37892/20 "2023-03-17T18:07:12Z")
This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.