Excel: If a column has NULL values compare ID numbers contained at another column and replace values from a table

Hello all, i need some help with an excel file. The columns and content of the file is as follows:

SRnumber Status Substatus SR_Type BranchNAme BranchNumber
123 Closed NotReached Lead JohnSmith 110
234 InProgress NotInterested Lead NULL NULL
345 Open Lead MarkEdwards 155

I need to depict the performance of the sales offices to some charts i have incorporated in the excel file. The problem is that some registries come blank at the sales offices fields. I have at a different excel sheet a table with all SR numbers and respective branch names/numbers. Thus, if the branch number is NULL i can refer to this extra sheet and retrieve the branch name and number.

In short, i need a flowchart that will check if there are NULL values at column BranchName. If yes, i need to vlookup the values of column SRnumber with the respective SRnumbers stored at the additional sheet and get the branch name and paste it at the NULL value.

Could anyone help? I am attaching part of the excel file for your convenience.
Any help would be very much appreciated.

SampleExcel.xlsx (85.0 KB)

hi @tzisman
I’ve attached My workflow here,
I hope this will help you.
Main.xaml (30.3 KB)

Sheet1
sheet1
Sheet2
sheet2
O/p:
Sheet3
sheet3

1 Like

Hello @tzisman
Great Solution By @samir
Similiarly there is another solution Please go through the below workflow
Main.xaml (34.2 KB)
Data.xls (6.5 KB)

1 Like

@vickydas @samir Hello both, thank you very much for the time you spent trying to resolve my problem. Truth is that the first solution for my level of expertise/understanding is a bit complex and difficult to follow. From a first walk through the 2nd solution seems easier to understand. I will try to implement one of the solutions or both of them and let you know if they work or if i come across any issues and i will let you know. Thank you again.

@tzisman I would suggest you to implement and Learn both of the workflows as someday you’ll require both our methods for different use cases :slight_smile:

Thanks

yeah, exactly @vickydas :slight_smile:
and @tzisman I’m there, if you ask me or want me to explain parts which you didn’t get.

Ok guys you are right i will go through both solutions. I first ran @vickydas flow. There were two issues i encountered; 1. it took 45 minutes to finalize. I entered excel application scope at the begining and also checked visible field (to be able to see what is going on) but didn’t check autosave, maybe because of excel application scope it took so long to run, 2. it didn’t copy and paste the values from the 2nd sheet to the 1st even though it didn’t throw any errors, it was as if everything worked fine but eventually i didn’t have the desired result. At the end i added “save workbook” activity. FYI i used the flow named “Failed Attempt” not the one named “Method 1 Prone to error”. I hope it is the right one.
@samir i will now try to go through your flow. It seems all Greek to me, even though i am Greek… :slight_smile: If i manage to implement most of it i will pose any questions i may have.

@tzisman are you running my workflow correctly? because its working flawlessly in my machine without any errors !!!

If you have some time please have a look. I attach both the excel and the xaml file.
What i am actually trying to do is in case @All_LeadsData sheet, @column AB “branch_Name” there is the value “NULL” go to the sheet named “NotRegisteredSRs” and in case column A with SR number of the 1st sheet, matches the SR number of the 2nd sheet copy and paste column B content of the 2nd sheet to the 1st. You have already understood that i know, just top make clear the exact columns. Taking your flow i have adjusted “” to “NULL” and changed the index to match the column number. I also added as you will see excel application scope at the beginning. Maybe i have omitted something important. Main.xaml (84.8 KB)
Leads Management Report_20181214b.xlsx (1.4 MB)