Dynamically write cell if it meets a certain condition

Help Studio
, , ,
1

Hi all, I have this data in excel where it contains list of address

Now I’m reading this data and it determines what are these places

My only problem is that if the address is not complete, it doesn’t properly work. What I want is to replace that missing data to “Not Applicable” like this:

  1. First, I loop through the address list
  2. Then I loop through the reference list
  3. Then I added a condition where address contains a place in my reference list
  4. Then I use write cell to write the data I gathered

I tried to add another condition where address doesn’t contains a place in my reference list but it’s not working

Not CurrentRowData(“Address”).ToString.Contains(CurrentRowRef(“Province”).ToString) Or Not CurrentRowData(“Address”).ToString.Contains(CurrentRowRef(“Municipality”).ToString) Or Not CurrentRowData(“Address”).ToString.Contains(CurrentRowRef(“Barangay”).ToString)

2

Can you share the xlsx file @Shoji ?

3

@Shoji

Please check this. I modified the If cndition you have liek this and added one for value…and also removed the second loop

image
image517×577 25.7 KB

AutoFilter.zip (3.6 KB)

Hope this helps

cheers

4

here

Special Project.xlsx (847.0 KB)

5

Hi, thanks for commenting. I run it and this is the output

image
image1673×164 6.07 KB

6

@Shoji

Check the if conditions once if i have given proper columns…and after tostring try including .tolower.Trim on both sides of contains…may be the casing is different

Cheers

1 Like
7

It works perfectly but after it reaches the address with incomplete details the first row is replaced by “not applicable” data

8

@Shoji

Please make these changes in the xaml

  1. Create variable index - type is integer…assign it to index in the for loop properties.
  2. Remove auto increment check box
  3. Change the cell value to the formula as shown in picture…do this change to all the cell values instead of number give (index+2).ToString

image
image1351×731 37.1 KB

Hope this is clear

cheers

1 Like
9

thank you very much, its working properly

1 Like
10

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.