smart vlookup

[tommy1234]

Hello
i have 2 sheets that i want to copy values between them.
the problems are :
1.that sheet1 has 100 items and sheet2 has 60 items (all of them included in sheet1).
2.the number of columns for each item is different and i want to copy to sheet1 only the max values column (need to be calculated)

sheet1 has 40 items which are different, so i dont want to delete them when copy the values from sheet2 to sheet1

thank you

item	price1	price2	price3	max
a	2.5			2.5
b	6			6
c	10.1	10.9		10.9
d	5	5.3	5.4	5.4
e	4	2	1	4
f	4.11	5.88		5.88

[ValerieT]

1) to build the max: depending on your real file, use if, while or for each or anything else... to loop on each price cells of a row and keep in a variable only if bigger than previous. Then put the variable into your max column

Can you change the original sheets by adding a column of your own? if yes, quickest way is to create a "unique" key column, combining item+ max, (both sheets). It will be your key for the vlookup

to get the biggest number of row to process you can make an easy stuff on your bigger sheet like;

For i = 1 To 999999                 
    If Cells(i, 1) = 0 Then
            Endlign = i- 1
            Exit For
    Endif
next i

then anything you do, you do it from 1 to Endlign. Don't forget to add some ""On error resume next" no to bug on inexisting key in shortest sheet

Not sure it is clear, but hard to be more precise as I don't know exactly how your sheets look like