View Single Post
  #1   Report Post  
ixara ixara is offline
Junior Member
 
Posts: 10
Exclamation How to replace cell value based on other cell's value using macro

Dear all,

I have 2 excel files. The first file is the main file that have transactions. The other file contain static data which is a list of full code, description & short code. How do i create a macro to replace the full code(ColumnC) in main file with description(ColumnB) found in the static data file? But change the full code(ColumnC) in the main file only if the value in ColumnB is SOLID and the first 2 character in full code(ColumnC) is equal to short code(ColumnC) in the static data file.The following is the scenario for better understanding. Thanks in advance for any help given.

Main file:
ColumnA ColumnB ColumnC
XY12083 SOLID HHIY
LK02491 TEMP HYTO
BGY1232 SOLID BSVV
UYTB981 SAMPEL JIEI

Static data file:
ColumnA ColumnB ColumnC
HHIY Household HH
HHXS Household HH
BSVV Bookstore BS
BSKY Bookstore BS
SPTB Sports SP
SPMI Sport SP

Expected output in the main file:
ColumnA ColumnB ColumnC
XY12083 SOLID Household
LK02491 TEMP HYTO
BGY1232 SOLID Bookstore
UYTB981 SAMPEL JIEI