Re: Comparing 2 Columns

[Leon Mitchell <leonmitchell shaw ca>]

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: gnumeric-list-request gnome org
To: gnumeric-list gnome org
Sent: Monday, August 17, 2020 6:00:03 AM
Subject: gnumeric-list Digest, Vol 190, Issue 2

Send gnumeric-list mailing list submissions to
        gnumeric-list gnome org

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
        gnumeric-list-request gnome org

You can reach the person managing the list at
        gnumeric-list-owner gnome org

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 <billd post com>
To: gnumeric-list gnome org
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 <mortenw gnome org>
To: bill d <billd post com>
Cc: Gnumeric Mailing List <gnumeric-list gnome org>
Subject: Re: Comparing 2 Columns
Message-ID:
        <CANv4PNmdQ5jXx1nzgNUJ0AyOYNUc20EUGqNcejLS77T2eOZ5-A 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 <gnumeric tim thechases com>
To: bill d <billd post com>
Cc: gnumeric-list gnome org
Subject: Re: Comparing 2 Columns
Message-ID: <20200816185614 35775353 bigbox attlocal net>
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
gnumeric-list gnome org
https://mail.gnome.org/mailman/listinfo/gnumeric-list


------------------------------

End of gnumeric-list Digest, Vol 190, Issue 2
*********************************************

Attachment: Book2.gnumeric
Description: application/gnumeric