Find which column has values and match the case

Help
, , , ,
1

I am having a sheet where data is in the form shown below

image
image1458×291 16.8 KB

Here, Column A B C D and H I J K have same name excet difference of _1 and _2.
I want the result like

image
image1370×235 18.1 KB

where WR SD OP and XX are the last words in the column name and if column contains any value other than 0 then it should be inserted in Remark column accordingly in the format shown and string matching is done after that.

Can anyone help in achieving the remark situation in this…

2

Hi @Swara_Soni

Are the column names constant or dynamic?

If they’re constant, you can simply hardcode the 4 values representing each column.
If they’re dynamic, however, then you may have to use either regular expressions or something like:
columnName.substring(lastIndexOf(CChar("-") + 1), 2)
The lastIndexOf method will find the second hyphen in the column name and the substring method will then extract the next two letters.

Is there any specific part that you need help with or are you looking for someone to give you the whole solution?

1 Like
3

@RPAForEveryone Thanks for the help …and i wanted to find the solution for the whole senerio, can you help me in building that.

4

Hi @Swara_Soni,

Here is the activity called “Find” to find the value in the sheet. It will return the cell info and rowindex.

Sample :

Video Explanation :

Regards
Balamurugan.S

5

@Swara_Soni

Let’s define a dictionary with the column index and the Code like following for the first and second set:
grafik

Within a for each row we can collect the values by using the dictionaries Keys and Column Index and the dictionary values as code if value is not null and other as 0

e.g. Set1
grafik

with the help of a String Join we can bring into the requested format
grafik

For the Match column we can make benefit from the SequenceEquals Method:
grafik

Kindly note:
for some transparency reasons / quick prototyping reasons the dicts are configured within a datatable:
grafik

using this statement for its creation
grafik

for the start we did not setup a fully dynamic self configuring approach and assume that it is also not needed.

find starter help here:
Main_RemarkMatcher.xaml (17.4 KB)

Let us know your feedback

2 Likes
6

@ppr Thanks a lot for the help …the solution helped me a lot in learning new ways for doing things.

Thanks again…

1 Like
7

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