Delete/Extract Excel colums that don't contain a specific Status

Help
, , ,
1

Hi all,

I have the followning table as an example for what I want to do:

image
image789×85 1.88 KB

There will be multiple sheets under the same format and I want to go through each sheet and remove the columns that have a different status than Ready. So, at the end I want to have only columns with ready status left.
Final result should look like this (only Ready columns):
image

Any thoughts on how this can be done is?
Not to much experience working with this kind of helping stuff so any help would appreciated.

Many thanks

2
  1. Do you know how many columns from start or it can change ?
3

@Mario9016
Find some starter Help here:

grafik
grafik504×674 33.6 KB

  • filter to the status row
  • calculate position of relevant column in the itemArray of the status row
  • filter on columns and keep column 0, calculated Index

Mario9016.xaml (10.4 KB)

Demo is using some LINQ statement for quick prototyping. We can also:

  • decompose LINQ into essentail UiPath Activities
  • doing more with LINQ and reducing some used Activities

Let us know your feedback

4

@JuvRPA no of columns is not fix, it can change

5

@ppr Thanks for you detailed response.
Unfortunately not very familiar with LINQ.
I’ve tried to replace that Build Data Table with a Read Range from the table i need to work with but I get this error:
image
Not sure what should be done in this case.

Thanks

6

@Mario9016
This exception was implemented for the case that no or more then one columns are detected having the ready status. Before Bot runs with wrong information it will be killed for quality reasons.

So its not abut LINQ its about the data and it should be handled as well also in other implementation approaches.

Just share your excel with us in case of you cannot sort it out by your own.

7

Hi @ppr

Couldn’t find a way to get through there.Demo_Status.xlsx (16.6 KB)
I have attached a short version of the table, with 2 sheets and 2-3 columns. There can be columns with the same header, but as mentioned above, I’m interested to keep just the ones with Ready status.

Once again, thanks for your support

Cheers

8

@Mario9016
I had a look on your Excel. In one sheet more than 1 columns had th ready status. Thats why the implementation was breaking. Sure implementation can be adopted, but for this we do need your requirements.What is your requirement?

  • 1 Table with multiple ready status columns
  • multiple Tables with 1 ready status columns

Let us know your feedback

9

@ppr
I have 1 table with multiple sheets (10-12 aprox.) and on each sheet there can be Ready status columns. Or there can be sheets just with Done or On Hold. I need to go through each sheet and keep there only the columns with Ready status.
Cheers

10

@Mario9016
Ok Perfect, just give me some time i will think about and work out an updated demo

11

@Mario9016
Find updated version, now handling multiple ready columns.
Kindly note:
Your demo table had a duplicated column name. Thats why I turned off add Header option. If this will not happen in your data then feel free to tick it on again

Mario9016_2.xaml (10.8 KB)

12

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