Re: Comparing 2 Columns

[John Denker <jsd av8n com>]

On 8/16/20 1:59 PM, 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?

I doubt there is any reasonable way to do it within spreadsheet-land.

It can be done easily in an imperative programming language; see below.

Suppose the input is:

red     
green   white
black   gray
blue    black
white   cyan
        magenta
        yellow

then the output is

                black
blue            
        cyan    
        gray    
green           
        magenta 
red             
                white
        yellow  

where
   the first  output column is unique to the first  input column,
   the second output column is unique to the second input column, and
   the third  output column is common to both.

NOTE: You may find it convenient to save the output to a temporary .csv
file, then add that onto the end of your working file (using ssconvert
--merge-to ...), then copy-and-paste from the added sheet to wherever
the result actually belongs.  Then delete the added sheet.  I find this
to be less laborious and less error-prone than other ways of inserting
data.

===============================================
I call this 'csv-comm.pl'.
It is conceptually similar to the unix 'comm' command ... although it
takes inputs from a /single/ file, and does /not/ require them to be
sorted.

#! /usr/bin/perl -CS

use warnings;
use strict;
use Text::CSV 'csv';
use File::Temp;

main: {
  my $ifile = 'csv-comm.gnumeric';
  my $sheet = 'Sheet1';
  my $dir = File::Temp->newdir(TEMPLATE => "temp-XXXXXXXX");
  system "ssconvert -S $ifile $dir/csv-comm-%s.csv";
  my $aoa = csv(in => "$dir/csv-comm-$sheet.csv");
  my %rslt;
  for my $col (0, 1) {
    my $flag = 1<<$col;
    checker: for my $row ($aoa->@*) {
      my $val = $row->[$col];
      next checker if $val eq '';
      $rslt{$val} += $flag;
    }
  }
  for my $val (sort keys %rslt) {
    my $which = $rslt{$val};
    my @out = ('') x 4;
    $out[$which-1] = $val;
    print join(',', @out), "\n";
  }
}