new dilemma on the same song that remains... umm the same

[trickyq]

I need to figure out how to compare each value in an array with each value in another array. This is for the same script I am writing for an excel price sheet. I thought it was done, but it seems that I need to compare each cell from column A with all of Column D looking for a match. so if there are 10 items in Array 0 from column A, I need it to start at Column A row 2 and see if that value is anywhere in Column D, then move on to Col A Row 3 and check to see if that value is matched in Col D and so on. until All 10 items have been looked for in column D. They dont need to be matched in the opposite direction.

this is the code I have now, it perfectly compares the columns line by line. not what I need it turns out.

#!/usr/bin/perl
use strict;
use Spreadsheet::XLSX;

my $excel = Spreadsheet::XLSX -> new ('build.xlsx');
my $sheet = $excel->Worksheet('Sheet1');
my ($row_min,$row_max) = $sheet->row_range();
my @col=();
for my $row ($row_min..$row_max){
  $col[0] = $sheet->{Cells}[$row][0]->{Val};
  $col[3] = $sheet->{Cells}[$row][3]->{Val};
  #print "$row $col[0] $col[3] \n"
  #print "$col[0] $col[3] \n"
    foreach ($col[0])  {
    if ($col[0] eq $col[3]) {
        
        open FILE, ">feckyou.txt" or die $!;
        print FILE "price has changed for $row \n";
        close FILE;    
                }
                
        
  }
  }

I was trying to see if that foreach would go through $col[0] and compare each value with all of $col[3]. Apparently not.

help

[chromatic]

I don't understand what you mean by "comparing each value of $col[0]"; it has one value. You can get rid of the loop altogether, as it does nothing.

Here's another problem:

open FILE, ">feckyou.txt" or die $!;

This will overwrite your file every time through the loop. You'll only ever get one entry. If you want an entry for each row, move this open outside of the outermost loop.

I don't entirely understand your problem (showing some sample data would help), but if you need to see if any of the values from column A are in column D anywhere, you need to loop through each row once, put the values from column A in a hash, then loop through each row again and check if the value for column D exists anywhere in the hash.

Does that make more sense?

[trickyq]

ok let me explain. my company has an excel spreadsheet that is essentially a price list. For industrial construction parts. Once a week we get a price sheet from our distributor of price changes and new items. The spreadsheet that we keep has a column (Column A) of Product IDs. The IDs range from 7-10 numbers or letters and dont follow a particular pattern. The girl at the office copies and pastes the list from the distributor to Column D of the Spreadsheet. She then manually scrolls through all the stuff in Column D (which is in a random order) and finds matches with the product ids in Col A. A match means a price change. Then the girl copies the new price into our price sheet.

I am trying to automate this a little. There are 3700 lines this person has to go through once a week. Where my problem lies is that once the new product Ids are copied into column D they are in any order. So the ID in A17 may match an ID in D347 and if so I need to know about it. The reverse doesnt need to work. I just need to know if the product id in A exists anywhere in D and then use a conditional to copy the corresponding new price to another column (easy part)

so if ($stufffromA eq $stufffromanywhereinD) { copy new price to column R }

hope that clears it up and doesnt make it muddier

[poj]

As chromatic said you need two separate loops , one for each column

#!/usr/bin/perl
use strict;
use Spreadsheet::XLSX;

my $excel = Spreadsheet::XLSX -> new ('build.xlsx');
my $sheet = $excel->Worksheet('Sheet1');
my ($row_min,$row_max) = $sheet->row_range();

# scan col D and store values
my %colD=();
for my $row ($row_min..$row_max){
  my $valD = $sheet->{Cells}[$row][3]->{Val}; 
  $colD{$valD} = $row+1; # excel row number
}

# scan col A starting at row 2
open FILE,'>','feckyou.txt' or die $!;
for my $row (1..$row_max){
  my $valA = $sheet->{Cells}[$row][0]->{Val};
  # does this value exist in Col D
  if (exists $colD{$valA}) {
    my $xlrow = $row+1;
    print FILE "price change [A$xlrow]=[D$colD{$valA}] Value=$valA\n";
  }
}

poj

[trickyq]

once again with minimal tweaking, its perfect

thanks