Re: Comparing 2 Columns

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";
  }
}