Join Location of Value Found with a Symbol

Hello,

I have a for each loop which loops through “if condition”. If a value “2021” is found, I would like to get the sheetname.

However, currently my problem is when the value exists in “Sheet1”, the log message shows it correctly. I have a write range activity to record the sheetname. As the process loops through, the value also exists in Sheet2. “Sheet2” then replaces “Sheet1”, I can’t see “Sheet1” anymore.

I want to maintain all sheet names with the value found by joining them with a “|” symbol instead of having one replaced by another. Can I consult you how can I do this?

@WS_Chai
sheet_names_list =

for each sheet in all_sheets:
if “2021” in sheet:
sheet_names_list.append(sheet)

sheet_names_combined = “|”.join(sheet_names_list)

or

Assign activity:
Left side: sheetNamesList
Right side: New List(Of String)

After if condition:If activity:
Add To Collection activity:
Collection: sheetNamesList
Item: currentSheetName

Assign activity:
Left side: concatenatedSheetNames
Right side: String.Join(“|”, sheetNamesList.ToArray())

@rlgandu,

Thanks for your guidance.

Do you mind to show me how to do the collection activity?

@WS_Chai

I guess outdatedFY is of type string…first initialize it with string.Empty outside the loop

Now in the assign use as below

OutdatedFY = outDatedFY + If(outdatedFY.Equals(String.Empty),"","|") + currentText

Cheers

@WS_Chai

@rlgandu,

i tried, what does this mean?

image

@WS_Chai

Hi @Anil_G,

Thanks for the guidance, it works!
Do you mind to explain the logic behind so that I can understand better?

One question- i wish to find value “2021” and “FY21” under my if condition. If EITHER ONE or BOTH exist, then compile the sheetnames. May I know what should I add in my if condition?

image

1 Like

@WS_Chai

Breaking it down

What I am doing is appending the value to the previous value

OutdatedFY - is the variable which c ontains the valeu say initially it contains nothing that is empty

If(outdatedfy.equals(string.empty),"","|")
So with if condition i am checking outdatedFY is empty then dont append pipe and else append pipe

Currenttext

Then adding the wew value

Now when oudated has first sheetname …our if condition will see it is not wmpty and add pipe after name and then name after the pipe

And for second contion you can add like this
Oldfy.Contains("2021") Or oldfy.Contains("FY21")

Cheers

Thanks @Anil_G, that’s very helpful!

1 Like

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