PDA

View Full Version : smart vlookup



tommy1234
03-07-2015, 01:03 AM
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
03-11-2015, 07:57 AM
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