Need Help re: changing data from one spreadsheet to another

[Zaphod]

I know that given enough time I could probably figure this out by reading old posts and doing a little experimenting. Unfortunately I do not have the luxury of doing so due to time contraints and I think that it is a fairly simple question for many here (at least I hope).

I have two work books that have different data. However, three columns are the same. One column is a description, one is a list number and one is a lot number. I know that my lot number is correct in the two workbooks. However, I want to take the list number and description from workbook 2 and whereever the lot number on workbook 1 matches the lot number on workbook 2, take the list number and description from workbook 2 and replace the same corresponding cells on workbook 1 with this data from workbook 2 without changing any other data. I want to do this with each worksheet on workbook 1.

I think I need vlookup but I am just not sure and I want to do this with VB. So explain it differently, I want to look at a number on workbook 1 in column O and find that same number on workbook 2 (any spreadsheet) in column K. Once found, I want to take the information for the same row on workbook 2 columns from columns H and G and put it into workbook 1 (for each spreadsheet) in columns H and I.

Hope this is a good enough explanation. Thanks in advance for any replies.

 

[Zack Barresse]

Re: Need Help re: changing data from one spreadsheet to anot

could you post w/ colo's html maker (bottom of page). it sounds like an index/match combo may work also.

 

[Zaphod]

Re: Need Help re: changing data from one spreadsheet to anot

I can't seem to get it to work although I will try again. In the meantime, perhaps I can be more clear. I have two workbooks containing a number of worksheets. Spreadsheets in both workbooks have errors. So basically I want to merge the data to produce one good workbook with reliable worksheets.

The majority of data in the worksheets of workbook 1 is valid. However, having learned that two columns in the spreadsheets of workbook 1 are not reliable data but are reliable in workbook 2. The worksheets in both workbooks do contain a reliable column that contains a lot number.

Accordingly I want to fix this one a one time basis. So starting with my first worksheet of workbook 1 I want to start with the first lot number on spreadsheet 1 in say O1. Then find the identical reference number anywhere in column K of any worksheet in workbook 2. Once I find the reference in workbook 2, I want to take the data from H and G of the row where the reference is found and copy the data contained in columns H and G of that row back to H1 and I1 of spreadsheet 1 of workbook 1. That would complete the process for the first cell. The same procedure would then be performed for the rest of column O on spreadsheet 1 of workbook 1. Once that is completed I would move on to the next spreadsheet of workbook one and carry out the same operation until all spreadsheets of the workbook contained new data in columns H and I and in all spreadsheets of workbook 1.

I dont know if this is more clear. I hope so and any help would be most appreciated. Thanks in advance.

 

[Zaphod]

Re: Need Help re: changing data from one spreadsheet to anot

I was able to accomplish what I wanted by creating columns and using vlookup. However, for the sake of interest I was still wondering if there was a better way.

 

[Ragnar78]

Re: Need Help re: changing data from one spreadsheet to anot

If u want to compare the columns i think this will be a simple way

Dim nColSheet1 as new collection
Dim nColsheet2 as new collection

sheet(first sheet).select
I=2 'starting point, change it to the number of row u want to start from
while range("A" & cstr(I)) <>"" 'will loop until an empty cell is found
nColsheet1.add range("A" & cstr(I)) .value ' add value
wend

sheet(next sheet).select
I=2 'starting point, change it to the number of row u want to start from
while range("A" & cstr(I)) <>"" will loop until an empty cell is found
nColsheet2.add range("A" & cstr(I)) .value
wend

for I=0 to UBound(nColsheet1)
if nColsheet1.item(I) <> nColsheet2.item(I) then Beep 'or just use another column with the row number equal to I+1 to put a value like 'wrong'
next