Compare columns and update the row

Closed
phoenix - Oct 26, 2011 at 09:58 PM
 phoenix - Oct 27, 2011 at 07:50 PM
Hello,

I need some help with a VBA code. I have tried differents codes that i found in the forum but they didn t match with what i m looking for.

I have a workbook with 2 sheets (Master , Master1). Both sheets have 5 columns A to E. (A:E).
All i need is a module macro that will compare the data in columnA of "Master" to columnA of "Master1".
Then copy the corresponding data of columnB to E of "Master1" to columnB to E of "Master" if the data
in columnA of "Master1" is same with the data in columnA of "Master", if not
just leave the data in columnA of "Master" and leave blank the corresponding column.

Regards
Related:

1 response

venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Oct 27, 2011 at 07:04 AM
why do you need a macro . simpler way is a formula and then to be copied

suppose master sheet is like this
heading1 heading2 heading3 heading4 heading5
a
d
g
w
r

master1 is like this
heading1 heading2 heading3 heading4 heading5
a 1 74 6 91
s 95 55 23 21
d 42 71 63 53
f 92 93 23 60
g 19 12 27 32
h 49 64 45 23
j 54 55 42 73
k 36 96 51 37

now in master sheet in B2 type(or copy) this formula
=IF(ISNA(VLOOKUP($A2,master1!$A$1:$E$100,COLUMN(B1),0)),"",VLOOKUP($A2,master1!$A$1:$E$100,COLUMN(B1),0))

copy B2 both across and down .
0
thank you but i used that formula before. i need is a macro that will copy the master sheet to a new sheet and rename it as master rev.0 then if i have a latest rev. for master sheet, the data from latest rev columnA will compare to the previous ( master rev.0) then if there is a same data from latest rev and previou rev. in columnA,the data from column B to E of master rev.0. will automatically copy to the columnA of Master ( latest ).

Let say:
Master ( latest Rev. )
Col A Col B Col C Col D Col E
b
c
f
g
h
z
t


Master Rev.0
Col A Col B Col C Col D Col E
b 35 3 6 9
c 5 4
f
g 6 2
h 3 2 45 3


Result:
Master ( latest Rev. )
Col A Col B Col C Col D Col E
b 35 3 6 9
c 5 4
f
g 6 2
h 3 2 45 3
z
t


Then again if i have another latest data ,the master (latest Rev) will be copy to another sheet then it will rename as MAster Rev.1, same procedure the latest rev will compare again to the previous rev. now it is Master rev1.
0