excel yet again

[orionbelt1951]

Hi,

I have 2 excel workbooks that are created each month:

workbook 1 has a column in it with reference numbers in each row (varying number of rows)

workbook 2 has a column in it with reference numbers in each row but each reference number has other characters in the cell

what I need to do is to go through each cell in workbook 1 and see if a cell in workbook 2 has that reference number contained in it, if it has I need the cell in workbook 2 to be replaced by the contents of the workbook 1 reference number.

Is this possible in MS?

Thx,

John

[hagchr]

Hi, should be possible to do. Can you give an example of what the data looks like, eg?

Column 1
1
3

Column 2
3 xxx
4 yyy

Then, Column 2 should become
3
4 yyy

[orionbelt1951]

Hi,

wkb1

column:
D00005628EM
T035564/25/EM
T043030/09+/EM

wkb2

column:
D-012\0833603-0002\D00005628EM
F-012\001730373\TGP/AH/T035564/25/EM\NAOMI & NICOLE
D-012\0833595-0002\D00005627EM
F-001\008846693\HMB/IJI/T043030/09+/EM\CELEBRITY TEACHER
F-026\001537056\T043038/36/CH\008909921\+\0

result wkb2

Column:

D00005628EM
T035564/25/EM
D-012\0833595-0002\D00005627EM
T043030/09+/EM
F-026\001537056\T043038/36/CH\008909921\+\0

Thx,

John

[hagchr]

Hi, it is possible to do and a solution in MS could look something like this. You would need to change the file names in the beginning (as well as name of the used sheet in WB1 and WB2 if not Sheet1). I put the result in a separate file to avoid overwriting the original one in case there are any problems. (Note, This would not work in the MS Lite version)

Code: Select all

//Define files and delete any old output files
Let>file1=C:\Users\Christer\Desktop\TestWB1.xlsx
Let>file2=C:\Users\Christer\Desktop\TestWB2.xlsx
Let>file3=C:\Users\Christer\Desktop\TestWB2out.xlsx
Let>SheetWB1=Sheet1
Let>SheetWB2=Sheet1
IfFileExists>file3
    DeleteFile>file3
endif

//Open the excel files
XLOpen>file1,1,xlBook1
XLOpen>file2,1,xlBook2

//Get number of rows in each sheet - ct1_max and ct2_max
XLGetSheetDims>xlBook1,SheetWB1,ct1_max,nColCount
XLGetSheetDims>xlBook2,SheetWB2,ct2_max,nColCount

//Loop through each element in WB1 and adjust any lines in WB2 that match
Let>ct1=0
While>ct1<ct1_max
    Add>ct1,1
    XLGetCell>xlBook1,SheetWB1,ct1,1,tmp1
    Let>pattern=\Q%tmp1%\E
    Let>ct2=0
    While>ct2<ct2_max
        Add>ct2,1
        XLGetCell>xlBook2,SheetWB2,ct2,1,tmp2
        RegEx>pattern,tmp2,0,M,NM,0
        If>NM>0
            XLSetCell>xlBook2,SheetWB2,ct2,1,tmp1,scResult
        Endif
    EndWhile
EndWhile

//Save the updated WB2 as WB2out
XLSave>xlBook2,file3
XLQuit>xlBook1
XLQuit>xlBook2

[orionbelt1951]

Hi,

That looks great, can you show mw what I'd need to do if the reference numbers are in column 'C' in the spreadsheet?

Thx,

John

[hagchr]

Hi,

If the reference in WB1 is in column C, then you just have to change in one place:

Code: Select all

XLGetCell>xlBook1,SheetWB1,ct1,1,tmp1

change to

Code: Select all

XLGetCell>xlBook1,SheetWB1,ct1,3,tmp1

ct1 is the row number which is looped through iand the next parameter is the column number, which you just change from 1 to 3.

(If the reference in WB2 is also in column C then you also have to change from 1 to 3 in the other two places for XLGetCell/XLSetCell for xlBook2)