Hi wrinkles, here a is an attempt of my. You need to adopt the output format to meet you requirements.

I'm going through an input file and add all transactions and items of the transaction to a hash %input, which gives you a structure like this:

$VAR1 = {
          '010' => {
                     'Gross' => '15',
                     'name' => 'Moe',
                     'widget-2' => {
                                     'Gross' => '15',
                                     'Qty' => '1'
                                   },
                     'qty' => '1'
                   },
          '020' => {
                     'Gross' => '35',
                     'name' => 'Larry',
                     'widget-1' => {
                                     'Gross' => '15',
                                     'Qty' => '1'
                                   },
                     'widget-2' => {
                                     'Gross' => '20',
                                     'Qty' => '2'
                                   },
                     'qty' => '3'
                   }
        };

[download]
Be aware that input file should contain only unique transaction ids and no repeating items per transaction as there is no error handling or accumulation if multiple item lines with the same item id are included per transaction. If you need this it's easy to implement. After you have all data in a hash it's easy to iterate over it and create a desired output. Here is the complete script 
# perl
# http://www.perlmonks.org/?node_id=859450

use strict;
use warnings;
use diagnostics;
use 5.010;

use Data::Dumper;


my %input;

my $line = <DATA>; # skip 1st line

while ( my $line = <DATA> ) {
    chomp $line;
    my  ($tx, $name, $type, $product, $qty, $gross) = split /\s*,\s*/,
+ $line;
    #say "$tx, $name, $type, $product, $qty, $gross";
    if ( $type eq 'Cart') {
        #say 'process Cart line';
        #say "data is $tx, $name, $qty, $gross";
             $input{$tx} = {
                                'name' => $name,
                                'qty'  => $qty,
                                'Gross'=> $gross,
                            }
    }
    elsif ( $type eq 'Item' ) {
        #say 'process Item line';
        #say "data is $tx, $name, $qty, $gross";
            $input{$tx} =   {
                                %{$input{$tx}},
                                $product => {
                                'Qty'        => $qty,
                                'Gross'      => $gross,
                                },
            }
                
    }
    else {
        warn 'line not recognized';
    }
    
}

#say Dumper(\%input);

my @items = qw( widget-1 widget-2); # all available items, need to be 
+defined in advance to have a stable order of output items

say 'Txn ID,  Name ,  Qty, Gross, widget-1 Qty, widget-1 Gross, widget
+-2 Qty, widget-2 Gross';
foreach my $tx (keys %input) {
    #say "prcess transaction $tx";
    my $lineout = "$tx, $input{$tx}{'name'}, $input{$tx}{'qty'},$input
+{$tx}{'Gross'}, ";
    foreach my $item ( @items ) {
        #say "found key $key";
        #say "found data $input{$tx}{$key}";
            #say "process item $input{$tx}{$item}";
            $lineout .= ", ";
            $lineout .= $input{$tx}{$item}{'Qty'} // '';
            $lineout .= ", ";
            $lineout .= $input{$tx}{$item}{'Gross'} // '';
    }
    say $lineout;
}

__DATA__
Txn ID,  Name ,  Type , Product ID, Qty, Gross
010   ,  Moe  ,  Cart ,           ,  1 ,   15
010   ,  Moe  ,  Item ,  widget-2 ,  1 ,   15
020   ,  Larry,  Cart ,           ,  3 ,   35
020   ,  Larry,  Item ,  widget-1 ,  1 ,   15
020   ,  Larry,  Item ,  widget-2 ,  2 ,   20

[download]
which creates this output: 
Txn ID,  Name ,  Qty, Gross, widget-1 Qty, widget-1 Gross, widget-2 Qt
+y, widget-2 Gross
010, Moe, 1,15, , , , 1, 15
020, Larry, 3,35, , 1, 15, 2, 20

[download]
I made it easier to me using the build-in DATA handle so you'll need to adopt the code for reading the files and writing output to a file.

P.S. I assume you have an error in the first line of the example output as it shows a total of 20 items included in the first transaction but only 15 are shown in the item line.

In reply to Re: How to combine rows of a spreadsheet by wwe
in thread How to combine rows of a spreadsheet by wrinkles

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.