It is possible to conditionally format the columns so that items not in the other column are highlighted. You can also use the match or vlookup functions in a 3rd and 4th column to list items only in the first column and only in the second column.
For me conditional formatting was kind of difficult to understand at first but here is what you do: To conditionally format the first column do the following: - Select the data in column A (a2 to a14 in the example) - right click on the selection and select Conditional Formatting... - under "Condition:" select "Expression x evaluates to TRUE." - in the box labeled "x:" enter and(A2<>"",isna(match(A2,B$2:B$14,0))) - the A2<>"" makes sure blank cells (perhaps at the end of the list) are not highlighted - now select the "Background" check box - click "Edit Style" - set the "Background Color" to what every you want - Click "OK" - Click the "Add" button. - Click "Close" Now all the items in column A that are not in column B are highlighted. Repeat with the second column except change the conditional expression to: and(B2<>"",isna(match(B2,A$2:A$14,0))) We can also use another column to display names in column A that aren't in column B on the same row as the original name. - Select cell C2 and enter: =if(and(A2<>"",isna(match(A2,B$2:B$14,0))),A2,"") - copy cell C2 - select cells c3 to c14 and paste the copied cell into these cells Repeat with cell D2 and the range D3 to D14 but use the expression: =if(and(B2<>"",isna(match(B2,A$2:A$14,0))),B2,"") I've attached an example with the conditional formatting and the extra columns. Regards, Leon Mitchell From: [email protected] To: [email protected] Sent: Monday, August 17, 2020 6:00:03 AM Subject: gnumeric-list Digest, Vol 190, Issue 2 Send gnumeric-list mailing list submissions to [email protected] To subscribe or unsubscribe via the World Wide Web, visit https://mail.gnome.org/mailman/listinfo/gnumeric-list or, via email, send a message with subject or body 'help' to [email protected] You can reach the person managing the list at [email protected] When replying, please edit your Subject line so it is more specific than "Re: Contents of gnumeric-list digest..." Today's Topics: 1. Comparing 2 Columns (bill d) 2. Re: Comparing 2 Columns (Morten Welinder) 3. Re: Comparing 2 Columns (Tim Chase) ---------------------------------------------------------------------- Message: 1 Date: Sun, 16 Aug 2020 22:59:44 +0200 From: bill d <[email protected]> To: [email protected] Subject: Comparing 2 Columns Message-ID: <trinity-8319fc96-bf42-4535-ad5d-28c975aff689-1597611584409@3c-app-mailcom-lxa08> Content-Type: text/plain; charset="us-ascii" An HTML attachment was scrubbed... URL: <https://mail.gnome.org/archives/gnumeric-list/attachments/20200816/46eb7214/attachment.html> ------------------------------ Message: 2 Date: Sun, 16 Aug 2020 19:08:22 -0400 From: Morten Welinder <[email protected]> To: bill d <[email protected]> Cc: Gnumeric Mailing List <[email protected]> Subject: Re: Comparing 2 Columns Message-ID: <canv4pnmdq5jxx1nzgnuj0ayoynuc20eugqncejls77t2eoz...@mail.gmail.com> Content-Type: text/plain; charset="UTF-8" I don't think there is an easy spreadsheet solution to this, but if you have the data in sorted text files, the command line "comm -23 filea fileb" will do the job. M. ------------------------------ Message: 3 Date: Sun, 16 Aug 2020 18:56:14 -0500 From: Tim Chase <[email protected]> To: bill d <[email protected]> Cc: [email protected] Subject: Re: Comparing 2 Columns Message-ID: <[email protected]> Content-Type: text/plain; charset=US-ASCII On 2020-08-16 22:59, bill d wrote: > I have 2 columns that mostly contain data common to both. I would > like to remove this leaving only the data unique to 1 column. Any > thoughts on how best to accomplish this? Many thanks! peace It might help to have a better picture of your data. Are they two columns of data in common with a 3rd column such as Month 2019 2020 Jan 4 4 Feb 4 5 Mar 8 8 and you just want the "Feb" row? Or do you have two columns of values 1 2 3 3 4 5 2 1 and you want 5 rows of their unique values: 1 2 3 4 5 Do you need to maintain the source order? Are they some sort of text files that you could use other *nix tools rather than trying to do it in Gnumeric? With a better idea of what your data looks like and what you want the results to look like, it would help craft a solution. -tim ------------------------------ Subject: Digest Footer _______________________________________________ gnumeric-list mailing list [email protected] https://mail.gnome.org/mailman/listinfo/gnumeric-list ------------------------------ End of gnumeric-list Digest, Vol 190, Issue 2 *********************************************
Book2.gnumeric
Description: application/gnumeric
_______________________________________________ gnumeric-list mailing list [email protected] https://mail.gnome.org/mailman/listinfo/gnumeric-list
