Comparing two excel workbook and copy the values if the cell are match

naga7667 has asked for the wisdom of the Perl Monks concerning the following question:

Dear Monks please help me to solve my problem

I am having two excel workbook Logsheet.xls and speclist.xls Logsheet.xls workbook contains sheet name "abcd". In ABCD sheet there are values from the range "A" to "AL" columns respectivly. Speclist.xls workbook contains sheet name "log" with vales of 2 columns "A" and "B". Two columns are dependent on eachother.

My requirement is I want to compare value of column"A" in Speclist.xls workbook with column "E" of logsheet.xls If both values are equal then corresponding values of "B" columns in speclist.xls to be copied to "AU" column of logsheet.

Input

Logsheet.xls

A B C D E .......AU

1 2 3 4 asd .......

1 2 3 4 sdf .......

1 2 3 4 sde .......

Speclist.xls

A B

asd 25

asr 16

sde 42

Logsheet.xls

A B C D E .......AU

1 2 3 4 asd .......25

1 2 3 4 sdf .......

1 2 3 4 sde .......42

Comment on Comparing two excel workbook and copy the values if the cell are match

Replies are listed 'Best First'.
Re: Comparing two excel workbook and copy the values if the cell are match by choroba (Cardinal) on Sep 26, 2017 at 16:40 UTC
Hi naga7667, welcome to the Monastery! What have you tried and how did it fail? Do you want the program to operate directly on the XLS files, or do you work with exported CSVs? Have you tried searching this site for similar questions? ($q=q:Sq=~/;[c](.)(.)/;chr(-\|\|-\|5+lengthSq)`"S\|oS2"`map{chr \|+ord }map{substrSq`S_+\|`\|}3E\|-\|`7**2-3:)=~y+S\|`+$1,++print+eval$q,q,a, [download]	[reply] [d/l]
Re^2: Comparing two excel workbook and copy the values if the cell are match by naga7667 (Initiate) on Sep 26, 2017 at 16:53 UTC
I tried to copy columns to array but dint work out...also I should use only Win32:OLE Its a two Excelfiles. I am able to store the values in array but cannot compare with another array	[reply]
Re^3: Comparing two excel workbook and copy the values if the cell are match by poj (Abbot) on Sep 26, 2017 at 17:58 UTC
I am able to store the values in array Post the code you have for that part. poj	[reply]
Re: Comparing two excel workbook and copy the values if the cell are match by Laurent_R (Canon) on Sep 26, 2017 at 20:17 UTC
Hi naga7667, You probably want to start by storing the content of the Speclis.xls into a hash, with the string (e.g. asd) as key and number (e.g. 25) as value. Once this is done, you read the Logsheet.xls, retrieve en value of the E column for each row, and lookup into the hash to find the value to be stored in column AU.	[reply]
Re: Comparing two excel workbook and copy the values if the cell are match by Anonymous Monk on Sep 26, 2017 at 23:37 UTC
It would be considerably easier to simply do this in Excel itself. The BASIC scripting could be put in either spreadsheet or in a third one which opens the other two as objects.	[reply]