Dear all,

I've columnA(code), columnB(type) and columnC(empty) in sheet1. I also have columnD(code) and columnE(name) in sheet2. If columnB value is REGION and the 3 character on the right of columnA is equal to value in columnD, then copy value in columnE into columnC. How to create macro to do this?..Below i illustrate the scenario. Thanks in advance for any help.

Sheet1
ColumnA ColumnB ColumnC
ABC2345 BANK
VGD3B23 REGION
HYT8T65 REGION

Sheet2
ColumnD ColumnE
345 VNA
T65 KLE

Expected result in Sheet1
ColumnA ColumnB ColumnC
ABC2345 BANK
VGD3B23 REGION VNA
HYT8T65 REGION KLE

Good Evening,

This is more of a manual process, but you could automate it with a macro. If you have a blank column next to your data, enter this formula to Rank the values using conditions (assumes data is in the range A1:B10 with a header row):

=1+SUMPRODUCT(($B$2:$B$10=B2)*($C$2:$C$10C2))

Now that you have a rank for each type of value in column B, you can either filter or use Advanced Filter to find the values you want to copy/paste.

Ben