Update columns based on the values in another sheet

Help
, ,
1

Hello All,
Can anyone please help me on below requirement it would be very helpful it me as i am stuck
My requirement is bit complex explaining you in detail below:

Consider Workbook1 as input1
Workbook2 as input2

  1. Based on Distinct “SR-Name” column and for “Lead” , “Head” and “Account” as null in “Workbook1”
  2. Check the SR-Name in another “workbook2” which has the values for above null columns “Lead” , “Head” and “Account”.
  3. If SR-Name of “Workbook1” is not present in “Workbook2” then need to search for “Dept” and match both “Dept” in both workbooks and update the values from second workbook for “lead” head" account columns .
    LIke Below:

Input1.xlsx (8.4 KB) Input2.xlsx (8.6 KB)

image
image

2

@chandrashekar_padmin Can you show us how the Output should appear :sweat_smile:

3

You can use VlookUp for finding value…

4

Hello,

can you please explain me in detail on which condition can i use vlookup?

Regards,
Padmini

5

Hello @supermanPunch,

Output should be like this:

All #N/A in image 2 should be filled with correspondingly:
image

6

If SR name matches then we need to fill the data in remaining columns ryt?
or you want both condition at a time need to match like for sr name and SR-Terittory?

7

Hello @ImPratham45 ,

First preference is SR name matches , second is if SR name not matches then check for SR-Territory.

8

Hello @supermanPunch,

I tried creating xaml file but no actions are done :frowning:
Can you please check what could be missing

Regards,
PadminiMappingUpdate.xaml (16.0 KB)

9

@chandrashekar_padmin Can you Check with this as well :sweat_smile: MatchingRecords.zip (27.9 KB)

10

@chandrashekar_padmin To Check this Xaml File i would be Needing the Other File as well :sweat_smile:

11

Hello @supermanPunch,

Here you are merging the data , but i wanted to update the same in input sheet1.
Output file is also updating for SR-territory which is “Communication” , ideally it shouldn’t update …
Can we write another condition not to update for “Communication”?
Also , can we update the same in input sheet1?

Thanks in Advance.

Regards,
Padmini

12

Hello @supermanPunch,

Can you please respond on above query?
Can we only update 3 columns in input sheet1 instead of merging entire data?
Appreciate your response :slight_smile:

Can u also please give a brief of last 3 steps in the xaml which you sent me :slight_smile:
Thanks in Advance!

Regards,
Padmini

13

Make Your main Excel Like This
image
Then use Below formulas

image
image1183×388 9.22 KB

Add this vlookups using write cell and then using Auto Fill Range u can apply this to entire data

For Ref

Input1.xlsx (10.7 KB) Input2.xlsx (8.7 KB)

14

@chandrashekar_padmin Sorry for the Late Response as I was a bit busy :sweat_smile: , I was trying to implement the method using VLookUp like @ImPratham45 Has done but i still wasn’t able to come to a solution, Have you tried the VLookup method ?

15

Thank you @ImPratham45, @supermanPunch

I will try the vlookup and response to you both :slight_smile:

16

Hi @ImPratham45,

Thank you for simple solution it is working well and fine :slight_smile:
It has saved lot of coding time :slight_smile:

Thanks much both @ImPratham45 and @supermanPunch for help me

i have one query , in case in future if any extra column needed to be checked how can we modify this vlookup?

Like in future if we want to check for client name and fill account group etc kind?

Regards,
Padmini

17

You can search for excel queries on vlookup :point_down:t5:

or on any other site :blush:

Happy Automation :partying_face: